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/
