Select and order by question

--=-sOG5NXamD3uWs9jff1xs
Content-Type: multipart/alternative; boundary="=-Zh7ZMpfLl0g840bQSXCt"


--=-Zh7ZMpfLl0g840bQSXCt
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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 $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

--=-Zh7ZMpfLl0g840bQSXCt
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit








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

Re: Select and order by question

On 5/3/06, Andy Dunlop wrote:
>
> Hi - I have the following code:
>
> //show table of current events needing action
> $sSql =3D "SELECT id FROM crm_leads ";
> $sLeads =3D pg_exec($conn,$sSql);
> for($e=3D0;$e > $sSql =3D "SELECT * FROM crm_events WHERE crm_leads_id =3D ".pg_=
result($sLeads,$e,"id")." AND follow_up_action!=3D'' ORDER BY follow_up_dat=
e asc limit 1";
> $sRes =3D pg_exec($conn,$sSql);
> $sHits =3D pg_numrows($sRes);
> if($sHits!=3D0){
> for($i=3D0;$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 limi=
t 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?

select * from crm_leads l, crm_events e where l.id=3De.crm_leads_id and
follow_up_action !=3D '' ORDER BY follow_up_date ASC LIMIT 1;

Make sure crm_events crm_leads_id has an index on id and cram_leads id..

--
Postgresql & php tutorials
http://www.designmagick.com/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Select and order by question

This is a multi-part message in MIME format.

------_=_NextPart_001_01C66EC1.BEEE2A7F
Content-Type: multipart/alternative;
boundary="----_=_NextPart_002_01C66EC1.BEEE2A7F"


------_=_NextPart_002_01C66EC1.BEEE2A7F
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 =3D "SELECT * FROM crm_events WHERE crm_leads_id IN (SELECT id =
FROM
crm_leads) AND follow_up_action!=3D'' ORDER BY follow_up_date asc limit =
1"

Not sure what your follow_up_action !=3D" is suppose to be doing but if
the rest works for you that should give you what you want.

=20

=20

Sherwin Harris

Web Developer

Brigham Young University

=20

________________________________

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

=20

Hi - I have the following code:

//show table of current events needing action
$sSql =3D "SELECT id FROM crm_leads ";
$sLeads =3D pg_exec($conn,$sSql);
for($e=3D0;$e $sSql =3D "SELECT * FROM crm_events WHERE crm_leads_id =3D
".pg_result($sLeads,$e,"id")." AND follow_up_action!=3D'' ORDER BY
follow_up_date asc limit 1";
$sRes =3D pg_exec($conn,$sSql);
$sHits =3D pg_numrows($sRes);
if($sHits!=3D0){
for($i=3D0;$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?=20

Any help appreciated
Thanks

Andy Dunlop
=20
www.infocus.co.za
+27 21 532 0335 office
+27 82 770 8749 mobile=20

=20


------_=_NextPart_002_01C66EC1.BEEE2A7F
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable




charset=3Dus-ascii">