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 6AE029F931D for ; Wed, 10 May 2006 04:59:06 -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 54374-08 for ; Wed, 10 May 2006 04:58:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 14FA59F96B6 for ; Wed, 10 May 2006 04:58:55 -0300 (ADT) Received: from ctb-mesg8.saix.net (ctb-mesg8.saix.net [196.25.240.78]) by svr4.postgresql.org (Postfix) with ESMTP id 1F7065B69A2 for ; Wed, 10 May 2006 07:58:53 +0000 (GMT) Received: from [111.112.113.9] (dsl-165-200-142.telkomadsl.co.za [165.165.200.142]) by ctb-mesg8.saix.net (Postfix) with ESMTP id DA1E34268; Wed, 10 May 2006 09:58:45 +0200 (SAST) Subject: Re: Select and order by question From: Andy Dunlop To: "Sherwin M. Harris" Cc: pgsql-php@postgresql.org In-Reply-To: References: Content-Type: multipart/related; type="multipart/alternative"; boundary="=-h4COFrQejVYZef99DnpJ" Organization: Infocus Date: Wed, 10 May 2006 09:58:14 +0200 Message-Id: <1147247895.6686.88.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/9 X-Sequence-Number: 3509 --=-h4COFrQejVYZef99DnpJ Content-Type: multipart/alternative; boundary="=-Kprzzbv1kpE5F3Fsq9dx" --=-Kprzzbv1kpE5F3Fsq9dx Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit If anyone is interested, here is the code we eventually have used: ".$compName1."".$empl1."".pg_result($sRes, $i,"date")."".pg_result($sRes, $i,"description")."".pg_result($sRes, $i,"type")."".pg_result($sRes, $i,"follow_up_action")."".pg_result($sRes, $i,"follow_up_date")."EditAction"; } } } ?> CRM - Events

Events Requiring Action

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 $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 --=-Kprzzbv1kpE5F3Fsq9dx Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit 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
--=-Kprzzbv1kpE5F3Fsq9dx-- --=-h4COFrQejVYZef99DnpJ Content-ID: <1147246953.6686.74.camel@localhost.localdomain> Content-Disposition: attachment; filename=infocus_cropped.jpg Content-Type: image/jpeg; name=infocus_cropped.jpg Content-Transfer-Encoding: base64 /9j/4AAQSkZJRgABAQEAlQCVAAD/4QAWRXhpZgAATU0AKgAAAAgAAAAAAAD//gAXQ3JlYXRlZCB3 aXRoIFRoZSBHSU1Q/9sAQwAFAwQEBAMFBAQEBQUFBgcMCAcHBwcPCwsJDBEPEhIRDxERExYcFxMU GhURERghGBodHR8fHxMXIiQiHiQcHh8e/9sAQwEFBQUHBgcOCAgOHhQRFB4eHh4eHh4eHh4eHh4e Hh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4e/8AAEQgAJABzAwEiAAIRAQMRAf/E AB0AAAIBBQEBAAAAAAAAAAAAAAAIBwEDBAUGAgn/xAA4EAABAwQBAwEGAgcJAAAAAAABAgMEAAUG EQcSITFBCBMiUWFxFGIyRFOBkaHBFRYkJTlSZYLw/8QAGgEBAAMBAQEAAAAAAAAAAAAAAAECBAYD B//EACARAAMAAQUBAAMAAAAAAAAAAAABAgMEBREhMVESMkL/2gAMAwEAAhEDEQA/AGZumfR4HJVv whVlubjs1oOJmoaJjt9lnSleAfg158qFdeV638J+nY1AGZck5dbfbCxfjuHcUIx64QQ9JjmO2VLU W3z2X09Q7to8H0+tYntbcoZzx/mGDW7DZTCBeHHUyI7kdCxIKVspSjqUCUg9ZGx370AxYWCe3f7V j3KdGt8CROmOJZjxmlPOrV4ShIKlH9wBNQJl2Jc6qsEvJ2uTWodwjx1SU25iIkMICQVe78Hfy2dm uRh5ryRyv7ON8ukK/W6zybZDmtXttUJK/wAc0lhfUlPb4CRvuPFCV6d0faiwV6WtEK3XqXDQ97lU 1uMr3QVvQ76+lWLz7QcF6SpqxxA+2lXT7xXrSbWa5ZOvjWJakXkJsKJDr34NDQSpKuxKlKA2QT6b rLwhjIL6ytUOf+AhtnXUEgk/M1ky5qX6s7LYNrwcqtXidO1zKXz6O3iPL6Z7iW7hFDYPlST2FSxb p8edGRIjrC0KGwRXz3evWVcfTIUq7y03azSHAhThR8bf2NTjmnL8rjnC467NGbn3G6LDcBogqSPz Eev0FWxZK/o8d80Oin8q08uKn2WNAVjW+1AWCkEDtS+xMB5+uNsF1uXKyIN3U31iHHiIDLatbCDo d/lWy4/y/ldHHeUKz6ymBeLMw6Ytx9wEtyukHSgnWvT5VpOSJvS51dwN/Y/+FV69b7eKhz2RM5yX kLiz+3cqmtzJ4mONBxLCGvhB7dkgCtLy3yNlmPe09gGE2q4IZsV5YaXNYMZC1LJddSdLKSodkjwR QE+hffRBqvV89D50o03k7Ms55jyHDm86g8f2y0vFmOhbaPey1BSk7Ss+fG+3oRXe4ZH5wx7kGFCf v8TNMTfSFPS1BtDjG9jyN79P40BPmz/tNFedKV3CtD6GigFR5F/1DMIP/GpGu37KVR7a3fmfhrtv /M+/r+sRqZqRjOPSMgZyF+x2x28MJ6WZ64iFPtjuNBzXUBpR8H1ql6xfHb3LiTLzYrZcZUJXVEel xEPLjnYO0FQJSdgHtQBl3fFLvs/qL50d9/gVSqezGlQ9l/lLsSPw9wA+R/w7lN+4y240tlxCFtLB SpBTsKBHcEeu61NvxTGbZbJVttuP2mFClhSZMdiG2228FDSutIGlbBO90QPnhx/Yn5vF7LnSoJkK kNNkg6347fyqnG90t1nhyccyVZgrStYC17SlQV6g/up6sm4zx2Vj6bXZbRBtLbKittqFHQy2CfPw oAGz89VE9z4wAdDd2skeakHQLzCXBr7EarBkhy2n4z6Lt26Ys2mwuL4y405afjRAHItxiZhHtuFY cg3F9T6et1s9TaQPHf8ArXf854hemcdxq+WWG7cf7tutLkNJSVK6U9woJHkef4VMmJYQ3AQGrRZY 8EK7H3LIbH8BUrYvYm7XF0vSnXP0yfkfT7VOJunxK6Rz+8apW6qqVVXw4Cye0PxVNx5FzfyiLBe9 0FLhSF9L6Fa7p6T3J9K53CeR8j5fx3NGYmNiHj7cZ1i2TVJWFyuxA7E9zrRqV5nHWBTJv42VhWNv yurr985a2VL38+op3XRR4rEZltiO0hlpsAIQ2npSkAa0B6Dx2FbjmhSfY15Mw/B+P52JZjd2LBdI k9xZROV7sLSddwT9dj91afO80t+ce2Tx1crQy+u3sFtiK+tHSmQA47tafyhRI3+XdNldsFwu7zvx 91xKwz5f7eRbmXHD/wBindZEjE8ZkXOFc5GP2l6dAQEQpLkNtTsYDZ02ojaRsnxryaAWfLsm4ezP kW+WDl3C28Ynw3Pds3B5ZbU+ATpSnW9b2AkjZPpUfX9mwYXm2PxeAs7u94kyZmpFvjSVPx0oJGyr XbxvzTuX7GMdyBKU36xWu7JQOlImw23gkfIdYNW7DiWMY+oqsOPWm1KIIJhw22d9/wAoFAbKEHVQ 2VSh0yC2kuhPgK13/nRWQQfQgD7UUBWiiigCgjYooqGC2P60KQhf6SQr7iiirUuist9HhISlXSlI A+gq8AN+KKKrfSXB6MrRRRUlQooooAooooAooooD/9k= --=-h4COFrQejVYZef99DnpJ Content-Type: image/jpeg; name="image002.jpg" Content-ID: Content-Description: image002.jpg Content-Location: image002.jpg Content-Transfer-Encoding: base64 /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAoHBwgHBgoICAgLCgoLDhgQDg0NDh0VFhEYIx8lJCIf IiEmKzcvJik0KSEiMEExNDk7Pj4+JS5ESUM8SDc9Pjv/2wBDAQoLCw4NDhwQEBw7KCIoOzs7Ozs7 Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozv/wAARCAAZAFADASIA AhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQA AAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3 ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWm p6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEA AwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSEx BhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElK U1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3 uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD1Gz1G 4n1y/s3ksmht1QosUu6Zcjnev8I9KjuPFvh60iWWfWrJUZioYTA8jr09K5nwv/yVrxh/1yt//Qar fCTSNOm8K3dxNZwyyzX0yu0iBiQDwOaAN7xV4603w94f/tOCe2vJJcfZ4lmX97k4yPYd64+f4t3q vJbPFYLKpH763lLx4x0yeprnbi2g/wCFS6r+5Qm31kxwkqMxr5g4B7CsOaxhttftftKKkLg9Rhd3 vUTdtj0cDRjNucrNK2j8z1Dw/wDEKW9nEck0Nx6qhww/Cu0uvEWk6fax3N9qFvapIPl81wpP0HU1 4hfwxpf6QtkYjqJulwIOvl55z7YrrvDQ0/8A4WVqg1wQmYQJ/Z4ucbNn8WzPGaUG+pePhTUvdSVu 2x6LZa7pWo2kl3Z6jbTwRcySJICE/wB70/Gp4r22nsxeRXEUluV3iZWBQr656Yrh9T/4Rf7f4gtt HhDavLpjm4NsMxbcHAOPl3frTPDOs6Zb/BuEy30C7LCSNl3jcHww2465rQ8s7G48RaNa2Md/Pqlo lrL/AKuYyja/+6e/4U/TNc0vWVZtN1C3uwn3vKcEr9R1FeRaPZWd1oXhB4tZt7DV4raX7NHewb4J VLtnOeAf1rpPDuoyWPj5NP1LSdLOo3tuT9t0tzt2r/fTtQB3kOlWNtf3F/BaRR3V0AJ5lXDSY6ZN Lp+lWOk25t9PtY7aEuXKRjA3HqauUUAZLeGdENhJYHS7Y2s0vnSQ7PlZ853Eeuawtb8C219M8sUU bo/LROMj8K7I9KSpaTRtRrzotuD3OL0fwRBp77oLGC1Y8M6r81dDd+HNI1G0S21DT7e7jj+750Yb BrS/ip9KMUgq1ZVNyhpuiaZo8Bg02wt7SNvvLFGF3fX1qong/wAOR3b3aaJZLPICGkEIyc9a2qKs xMqTwxocumRaZLpVq9nDny4GiBVM+npT9K8PaPom7+y9MtrPf94xRgE/jWlRQB//2Q== --=-h4COFrQejVYZef99DnpJ--