Re: Select and order by question

off topic, but still relevant to php sql.  i'll share
something that has made my life much easier wrt sql
statements - heredocs.

the format looks like this:

$sSql = <<<_ESQL

SELECT * FROM crm_events
WHERE crm_leads_id
IN (SELECT id FROM crm_leads)
AND follow_up_action!=''
ORDER BY follow_up_date asc limit 1

_ESQL

the benefits are that it is much more readable, you
don't have to worry about escaping the usual suspect
characters and you can easily copy and paste this code
into a sql tool to test the results of the sql.  you
can also copy and paste from the sql tool back into
your code.  this helps dramatically when you have
20-30 lines of sql with quotes throughout. ;-)

you can look up heredoc in the php for more
information.

good luck.

--- "Sherwin M. Harris" <Sherwin_Harris@byu.edu>
wrote:

> Assuming you are using a version of a database that
> can support
> sub-queries (MySQL 4.1 and up, Postgresql (all
> reasonably current
> versions, any other robust RDBMS) you can do the
> query like this:
>
> $sSql = "SELECT * FROM crm_events WHERE crm_leads_id
> IN (SELECT id FROM
> crm_leads) AND follow_up_action!='' ORDER BY
> follow_up_date asc limit 1"
>
> Not sure what your follow_up_action !=" is suppose
> to be doing but if
> the rest works for you that should give you what you
> want.
>
>
>
>
>
> Sherwin Harris
>
> Web Developer
>
> Brigham Young University
>
>
>
> ________________________________
>
> From: pgsql-php-owner@postgresql.org
> [mailto:pgsql-php-owner@postgresql.org] On Behalf Of
> Andy Dunlop
> Sent: Wednesday, May 03, 2006 3:13 AM
> To: pgsql-php@postgresql.org
> Subject: [PHP] Select and order by question
>
>
>
> Hi - I have the following code:
>
> //show table of current events needing action
> $sSql = "SELECT id FROM crm_leads ";
> $sLeads = pg_exec($conn,$sSql);
> for($e=0;$e<pg_numrows($sLeads);$e++){
>         $sSql = "SELECT * FROM crm_events WHERE
> crm_leads_id =
> ".pg_result($sLeads,$e,"id")." AND
> follow_up_action!='' ORDER BY
> follow_up_date asc limit 1";
>         $sRes = pg_exec($conn,$sSql);
>         $sHits = pg_numrows($sRes);
>         if($sHits!=0){
>                 for($i=0;$i<$sHits;$i++){
>               // generate the display here
>         }
> }
>
> crm_leads is a parent with many crm_events as it's
> children.
> This code gives me the correct set of rows.
> My problem is that I want only the last row from
> crm_events (as per
> limit 1) but I want those rows sorted by
> follow_up_date. I guess I need
> to have the whole query in one select statement? But
> how?
>
> Any help appreciated
> Thanks
>
> Andy Dunlop
>
> www.infocus.co.za
> +27 21 532 0335 office
> +27 82 770 8749 mobile
>
>
>
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com