On 3/28/07 5:21 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:
> The upshot of what a lot of other people are saying is, "what you are trying
> to do should work."
>
> Your results are atypical. Despite the number of answers that suggest you
> try a slightly different approach, the way you're doing it should work, I've
> used it without problem.
>
> I would recommend you provide an actual code sample, to ensure you're not
> making some minor mistake. If it turns out that this doesn't work on your
> setup, I would suggest that it's probably a PHP problem and not a Postgres
> one. More specifically, it's probably a problem with the specific version/
> packaging/installation/OS of your PHP, as I've never seen the type of
> behaviour you're describing.
Hmm, OK.
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.
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]
The $numrows variable is used by the paging script I'm using, which I
adapted from one from the PHP Resource Index, to calculate the number of
pages needed, offset, etc.
I've lost track of how many ways I've tried to get the count value into
$numrows, but I know that I've tried, among other things:
pg_fetch_result($result_count, 0, 'count');
pg_fetch_result($result_count, 0, 'result_count');
(I tried this one after adding the AS clause to the query)
pg_fetch_result($result_count, 0);
pg_fetch_result($result_count);
And none of these worked.
I only tried pg_fetch_all so that I could get some idea of what data was
actually in the resource and how it was arranged. I know I don't need a
multidimensional array to get one value. :-) But it didn't help much except
to show that the strange number I was getting was actually in there, despite
the fact that the search query itself worked fine and brought up the number
of results you would think it would.
The system specs are:
Server: FreBSD 6.2
PHP: 4.4.2 (with Suhosin Patch 0.9.6)
PostgreSQL: 7.4.14
Lynna
>> I'm trying to use a SELECT COUNT(*) to count how many results would be
>> retrieved from a particular query (as part of the process of paginating
>> search results).
>>
>> But I'm having trouble figuring out how to retrieve the result of the count
>> in PHP. The result on its own is a resource rather than a specific value,
>> but when I try to retrieve the result via pg_fetch_result, some kind of
>> weird math error happens and I get a huge number that bears no resemblance
>> to the number of results the query actually gets when it runs (1,714,608 for
>> a query that in actuality produces three results).
>>
>> I tried using an AS clause (SELECT COUNT(*) AS result_count FROM etc. WHERE
>> etc.) to give the result a name, but that didn't help, and when I tried
>> using pg_fetch_all on the result to see exactly what it was retrieving, I
>> got this:
>>
>> Array
>> (
>> [0] => Array
>> (
>> [result_count] => 1714608
>> )
>>
>> )
>>
>> Again with the weird number. And yet, if I run the exact same query in the
>> SQL window of phpPgAdmin, I get the proper result count (3 in this
>> instance).
>>
>> Does anyone know what's going on here? Can I just not use SELECT COUNT(*)
>> with PHP at all?
>>
>> I originally had the script running the actual query and then counting the
>> results, and then running it again with LIMIT and OFFSET to get one page's
>> worth of results, but it seemed wasteful to do it that way, so I was trying
>> to do it more efficiently... :-/
--
Spider Silk Design - http://www.spidersilk.net
509 St Clair W Box 73576, Toronto ON Canada M6C 1C0
Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 416.873.9289
