pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Select and order by question
5+ messages / 4 participants
[nested] [flat]

* Select and order by question
@ 2006-05-03 09:13  Andy Dunlop <andy@infocus.co.za>
  0 siblings, 1 reply; 5+ messages in thread

From: Andy Dunlop @ 2006-05-03 09:13 UTC (permalink / raw)
  To: pgsql-php

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

Attachments:

infocus_cropped.jpg (image/jpeg, 1.9K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: Select and order by question
@ 2006-05-03 12:45  chris smith <dmagick@gmail.com>
  parent: Andy Dunlop <andy@infocus.co.za>
  0 siblings, 0 replies; 5+ messages in thread

From: chris smith @ 2006-05-03 12:45 UTC (permalink / raw)
  To: Andy Dunlop <andy@infocus.co.za>; +Cc: pgsql-php

On 5/3/06, Andy Dunlop <andy@infocus.co.za> wrote:
>
>            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?

select * from crm_leads l, crm_events e where l.id=e.crm_leads_id and
follow_up_action != '' 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/



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Select and order by question
@ 2006-05-03 14:56  Sherwin M. Harris <Sherwin_Harris@byu.edu>
  0 siblings, 2 replies; 5+ messages in thread

From: Sherwin M. Harris @ 2006-05-03 14:56 UTC (permalink / raw)
  To: pgsql-php

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 

 

Attachments:

image002.jpg (image/jpeg, 1.3K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: Select and order by question
@ 2006-05-03 16:12  operationsengineer1@yahoo.com
  parent: Sherwin M. Harris <Sherwin_Harris@byu.edu>
  1 sibling, 0 replies; 5+ messages in thread

From: operationsengineer1@yahoo.com @ 2006-05-03 16:12 UTC (permalink / raw)
  To: Sherwin M. Harris <Sherwin_Harris@byu.edu>; pgsql-php

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 
From pgsql-php-owner@postgresql.org  Wed May  3 16:26:00 2006
X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 88B7B9FA36E
	for <pgsql-php-postgresql.org@localhost.postgresql.org>; Wed,  3 May 2006 16:26:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
 by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
 with ESMTP id 58161-02
 for <pgsql-php-postgresql.org@localhost.postgresql.org>;
 Wed,  3 May 2006 16:25:47 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from a2s5.a2hosting.com (host-209-124-50-10.a2webhosting.com [209.124.50.10])
	by postgresql.org (Postfix) with ESMTP id 3F9C39F9C1D
	for <pgsql-php@postgresql.org>; Wed,  3 May 2006 16:25:46 -0300 (ADT)
Received: from adsl-153-96-23.tys.bellsouth.net ([70.153.96.23] helo=[192.168.1.10])
	by a2s5.a2hosting.com with esmtpsa (TLSv1:RC4-SHA:128)
	(Exim 4.52)
	id 1FbMyl-0006l6-Db; Wed, 03 May 2006 15:25:43 -0400
In-Reply-To: <20060503161243.3545.qmail@web33314.mail.mud.yahoo.com>
References: <20060503161243.3545.qmail@web33314.mail.mud.yahoo.com>
Mime-Version: 1.0 (Apple Message framework v749.3)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <1AEFEEC2-CEA4-414F-876A-D805EAB55544@pgedit.com>
Cc: "Sherwin M. Harris" <Sherwin_Harris@byu.edu>,
 pgsql-php@postgresql.org
Content-Transfer-Encoding: 7bit
From: John DeSoi <desoi@pgedit.com>
Subject: Re: Select and order by question
Date: Wed, 3 May 2006 15:25:24 -0400
To:  <operationsengineer1@yahoo.com> <operationsengineer1@yahoo.com>
X-Mailer: Apple Mail (2.749.3)
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - a2s5.a2hosting.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12]
X-AntiAbuse: Sender Address Domain - pgedit.com
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200605/8
X-Sequence-Number: 3508


On May 3, 2006, at 12:12 PM, <operationsengineer1@yahoo.com>  
<operationsengineer1@yahoo.com> wrote:

> 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

A little shameless promotion: pgEdit will even color the heredoc  
string for SQL if you start your heredoc string with <<<sql. Works  
for <<<html also.

I'll have a short article out soon that shows how you can pretty much  
remove all SQL from your PHP code using a simple PHP class and  
PostgreSQL functions.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Select and order by question
@ 2006-05-10 07:58  Andy Dunlop <andy@infocus.co.za>
  parent: Sherwin M. Harris <Sherwin_Harris@byu.edu>
  1 sibling, 0 replies; 5+ messages in thread

From: Andy Dunlop @ 2006-05-10 07:58 UTC (permalink / raw)
  To: Sherwin M. Harris <Sherwin_Harris@byu.edu>; +Cc: pgsql-php

If anyone is interested, here is the code we eventually have used:

<?php
$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 desc limit 1";
     $sRes = pg_exec($conn,$sSql);
     $sHits = pg_numrows($sRes);
       
        if($sHits!=0){
                
                for($i=0;$i<$sHits;$i++){
                        
                        $temp_date = explode("-",pg_result($sRes,
$i,"follow_up_date"));
                        $temp_tstamp = gmmktime(0,0,0,$temp_date[1],
$temp_date[2],
$temp_date[0]);                                                          
                        $array_echo[$temp_tstamp] .=
"<tr><td>".$compName1."</td><td>".$empl1."</td><td>".pg_result($sRes,
$i,"date")."</td><td>".pg_result($sRes,
$i,"description")."</td><td>".pg_result($sRes,
$i,"type")."</td><td>".pg_result($sRes,
$i,"follow_up_action")."</td><td>".pg_result($sRes,
$i,"follow_up_date")."</td><td><a
href='crm_events_edit.php?id=".pg_result($sRes,
$i,"id")."'>Edit</a></td><td><a
href='crm_events_bycomp.php?id=".pg_result($sRes,
$i,"crm_leads_id")."'>Action</a></td></tr>";
                }
    }
}
?>
<html>
<head>
<title>
CRM - Events
</title>
</head>
<body bgcolor="<?echo $sBodyColor?>">
<?php require("menu.php"); ?>
<table width='100%'>
<tr><td><h2><font color='<? echo $sHeadingColor?>'>Events Requiring
Action</font></h2></td>
<td align='right'><a href='manual/crm_events.html' target='_blank'><img
src='images/help_btn.jpg' border=0></a></td>
</table>
<? asort($array_echo); ?>
<? foreach($array_echo as $ae){
        echo $ae;
   }
?>
<? echo $prog_echo_end; ?>
</body>
</html>

The limit 1 gets us the last qualifying event on each lead, and this
result is stuck into an array which is sorted by follow_up_date prior to
display.

I probably didn't explain the problem too well! But thanks for the
input.
Andy


On Wed, 2006-05-03 at 08:56 -0600, Sherwin M. Harris 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 
> 
> 
> 
>  
> 
> 

Andy Dunlop

www.infocus.co.za
+27 21 532 0335 office
+27 82 770 8749 mobile

Attachments:

infocus_cropped.jpg (image/jpeg, 1.9K) download view image
image002.jpg (image/jpeg, 1.3K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread


end of thread, other threads:[~2006-05-10 07:58 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-03 09:13 Select and order by question Andy Dunlop <andy@infocus.co.za>
2006-05-03 12:45 ` chris smith <dmagick@gmail.com>
2006-05-03 14:56 Re: Select and order by question Sherwin M. Harris <Sherwin_Harris@byu.edu>
2006-05-03 16:12 ` operationsengineer1@yahoo.com
2006-05-10 07:58 ` Andy Dunlop <andy@infocus.co.za>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox