Re: Retrieving result of COUNT(*) with PHP

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend