pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris <dmagick@gmail.com>
To: Lynna Landstreet <lynna@spidersilk.net>
Cc: PgSQL-PHP Mailing List <pgsql-php@postgresql.org>
Subject: Re: Retrieving result of COUNT(*) with PHP
Date: Thu, 29 Mar 2007 10:25:33 +1000
Message-ID: <460B077D.3090704@gmail.com> (raw)
In-Reply-To: <C2307CCD.4B00C%lynna@spidersilk.net>
References: <C2307CCD.4B00C%lynna@spidersilk.net>


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



view thread (15+ messages)  latest in thread

Message-ID: <460B077D.3090704@gmail.com>
Permalink:  ../460B077D.3090704@gmail.com/
Also on:    postgresql.org/message-id/460B077D.3090704@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: dmagick@gmail.com, lynna@spidersilk.net
  Subject: Re: Retrieving result of COUNT(*) with PHP
  In-Reply-To: <460B077D.3090704@gmail.com>
  References: <C2307CCD.4B00C%lynna@spidersilk.net> <460B077D.3090704@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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