Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id B0B409FBAA3 for ; Wed, 28 Mar 2007 21:25:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 05616-04 for ; Wed, 28 Mar 2007 21:25:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.226]) by postgresql.org (Postfix) with ESMTP id 89CDC9FBA9D for ; Wed, 28 Mar 2007 21:25:14 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id i28so21690wxd for ; Wed, 28 Mar 2007 17:25:13 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=mo5dCehT406UMriJthjYFVSapvaMT92MNn+jh8pQWclfr8Zttz38cVTkBygRlsHaP7o1td7ACdiwi/gCSaYtC2fcsWUsnFx75YwYfMIx2VqhalN11Fsi3UhWfFJAhwASJphUsjumzNWbpF+hNG37VRBmHctSVTzfkdHeGX3RMuk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=G0y8Hb2XaKmucfy7twj3zlrP2zeIH/JRtLqd96N8l0iqGwgwVt97ypmR+8WR9EOGVzjKJoMq1eMGYzBRPtF+FA09UxCG4U/XDDscqVopxw2dpmHUgX7JLMO+ByjenJqub2F8+15Au6FVOIuDQHRHs06AnLUuozdB066X0UO3WHA= Received: by 10.70.80.14 with SMTP id d14mr156847wxb.1175127913162; Wed, 28 Mar 2007 17:25:13 -0700 (PDT) Received: from ?10.1.1.13? ( [203.217.18.65]) by mx.google.com with ESMTP id 40sm87462nzf.2007.03.28.17.25.11; Wed, 28 Mar 2007 17:25:12 -0700 (PDT) Message-ID: <460B077D.3090704@gmail.com> Date: Thu, 29 Mar 2007 10:25:33 +1000 From: Chris User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: Lynna Landstreet CC: PgSQL-PHP Mailing List Subject: Re: Retrieving result of COUNT(*) with PHP References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/63 X-Sequence-Number: 3775 > The code is a bit lengthy because it has to allow for three different search > modes (any, all and exact phrase). So first I define the first part of the > search query as: > > $searchquery = "SELECT i.image_id, i.title, i.medium, j.artist_id, > a.firstname, a.lastname > FROM images i, art_img_join j, artists a > WHERE (i.image_id = j.image_id > AND j.artist_id = a.artist_id) > AND "; > > And then depending on what type of search they chose it does any of three > different things with their search text. In the case of an "any" search (as > in, any of the words they entered), it does this: > > // break into an array of separate words, count them > > $searchwords = explode (" ", $searchtext); > $wordcount = count ($searchwords); > > // loop through array adding each word to select > > foreach ($searchwords as $key => $word) { > > $where_clause .= "i.title ILIKE '%$word%' > OR i.series ILIKE '%$word%' > OR i.medium ILIKE '%$word%'"; > > if (($key + 1) < $wordcount) { > $where_clause .= " OR "; > } > } > > And then, for any of the three types, it adds this: > > $searchquery .= $where_clause . " ORDER BY lower(i.title)"; > > That's for the actual search query. Now, the reason I didn't just run the > query and use pg_num_rows is because the images table has quite a lot of > records. If someone searches for a fairly common word or phrase, they could > get 20 pages of results... So I didn't want to put any more stress on the > database, or slow things down any more, than I had to. Thus, I thought the > count(*) approach might be more efficient. Don't do pg_num_rows - it's the wrong approach for this problem. > So what I did with that was this: > > $count_query = "SELECT COUNT(*) AS result_count > FROM images i, art_img_join j, artists a > WHERE " . $where_clause; > > (I didn't originally have the "AS result_count" in there - I added that when > I was having trouble extracting the value, hoping that giving it a more > specific name might help. It didn't.) > > $result_count = pg_query($count_query); > > $numrows = [any number of things I've tried] $count_row = pg_fetch_assoc($result_count) or die ('problem: ' . pg_last_error()); $numrows = $count_row['result_count']; :D You can't do it all in one step with pg_fetch_assoc (afaik), but this is easy enough. No idea why it's not working with pg_fetch_result but this way does work. -- Postgresql & php tutorials http://www.designmagick.com/