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 79ADD9F9CC1 for ; Wed, 3 May 2006 13:12:55 -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 29761-06 for ; Wed, 3 May 2006 13:12:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web33314.mail.mud.yahoo.com (web33314.mail.mud.yahoo.com [68.142.206.129]) by postgresql.org (Postfix) with SMTP id 997879F9C10 for ; Wed, 3 May 2006 13:12:44 -0300 (ADT) Received: (qmail 3547 invoked by uid 60001); 3 May 2006 16:12:43 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=ojxUgSuyMEHd1O9tbqTSbRrQ0lTfFH6qH9QXmcAKgtuPreVkU5uOOAC9UwTmlE1HCaiysjPg2zWDmLryUGALedXH7KkDeeVkyf4IM1vjqDsTvtmBrYpec1akDS9hFgwJWErSDx1k9d5JsLKi6PfyWSSBgcux/7bwyqPc03JsCUk= ; Message-ID: <20060503161243.3545.qmail@web33314.mail.mud.yahoo.com> Received: from [12.9.179.126] by web33314.mail.mud.yahoo.com via HTTP; Wed, 03 May 2006 09:12:43 PDT Date: Wed, 3 May 2006 09:12:43 -0700 (PDT) From: Subject: Re: Select and order by question To: "Sherwin M. Harris" , pgsql-php@postgresql.org In-Reply-To: MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit Content-ID: X-Archive-Number: 200605/7 X-Sequence-Number: 3507 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" 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 > > > > __________________________________________________ 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 ; 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 ; 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 ; 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" , pgsql-php@postgresql.org Content-Transfer-Encoding: 7bit From: John DeSoi Subject: Re: Select and order by question Date: Wed, 3 May 2006 15:25:24 -0400 To: 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, 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 <<