Combine SELECT count(*) with query containing LIMIT?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
webcan
Forum Commoner
Posts: 66
Joined: Tue Oct 28, 2003 2:25 pm
Location: Toronto, Canada

Combine SELECT count(*) with query containing LIMIT?

Post by webcan »

Is there a way to combine the count(*) feature while using LIMIT in a SQL statement and still get the TOTAL number of records/rows that would have been retrieved if LIMIT wasn't there?

For example, if I had done SELECT * FROM table, I would have gotten 100 rows... but if I do SELECT * FROM table LIMIT 0, 20 I only get 20 rows, but I still want to know there are 100.

Essentially, like if you do a search on this site it says "400 matches found" but shows you 20 or whatever at a time. I don't want to do a SEPERATE query to get the count.

THANKS!
Peter.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

then do two seperate querys
one to get total, and one to get the selected rows
webcan
Forum Commoner
Posts: 66
Joined: Tue Oct 28, 2003 2:25 pm
Location: Toronto, Canada

Post by webcan »

But is there some way to combine them into one query?

And, when I do just SELECT count(*) FROM table, how do I actually get the number out???? Does anyone have an example?

Thanks,
Peter.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

webcan wrote:But is there some way to combine them into one query?

And, when I do just SELECT count(*) FROM table, how do I actually get the number out???? Does anyone have an example?

Thanks,
Peter.
No, I believe they'd have to be in two separate queries, as said above.

To use count(), format your query like this:

$sql = "select count(*) as myCountVariable from table";
$result = mysql_query($sql, $conn);
$row = mysql_fetch_array($result);

echo "The count is ".$row["myCountVariable"];

Or you could go:

$sql = "select count(*) as myCountVariable from table";
$result = mysql_query($sql, $conn);
$myCount = mysql_result($result, 0);

echo "The count is ".$myCount;
Post Reply