Re: Retrieving result of COUNT(*) with PHP
От | Andy Shellam |
---|---|
Тема | Re: Retrieving result of COUNT(*) with PHP |
Дата | |
Msg-id | 460ADBDD.5020205@mailnetwork.co.uk обсуждение исходный текст |
Ответ на | Re: Retrieving result of COUNT(*) with PHP (Mihail Mihailov <Mihail.Mihailov@uta.fi>) |
Ответы |
Re: Retrieving result of COUNT(*) with PHP
(Mihail Mihailov <Mihail.Mihailov@uta.fi>)
|
Список | pgsql-php |
See note below... Mihail Mihailov wrote: > Hi, > > actually, I don't think you need pg_fetch_all. > I use pg_fetch_row for the purpose. What you get with the SELECT > clause is one row. > > E.g. like this: > $res = pg_fetch_row(pg_query("SELECT COUNT(*) AS result_count FROM > etc. WHERE etc.")); //Not a very good style :-), one should check if > the query runs > $count = $res[0]; > > Another way to calculate number of rows in the result is to use > pg_num_rows function. > $res = pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc."); > $count = pg_num_rows($res); ^^ Will this not just return 1? As there is only 1 row in the returned dataset. Can't say I've ever tried but that's what I'd think would happen. Personally I'd do "SELECT <primary key column> FROM etc" then call pg_num_rows. Although I've never had problems with COUNT(*) working. Perhaps try doing SELECT COUNT(<primary key column>) as num_of_rows FROM etc? Andy. > > Enjoy! > > Mihail > > > > Quoting Lynna Landstreet <lynna@spidersilk.net>: > >> Hi there, >> >> 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... :-/ >> >> Thanks, >> >> Lynna >> >> -- >> 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 >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: Don't 'kill -9' the postmaster >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > !DSPAM:37,460ad90b103001377313056! > >
В списке pgsql-php по дате отправления: