Page 1 of 1

Combine SELECT count(*) with query containing LIMIT?

Posted: Sun Feb 01, 2004 1:28 pm
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.

Posted: Sun Feb 01, 2004 2:53 pm
by d3ad1ysp0rk
then do two seperate querys
one to get total, and one to get the selected rows

Posted: Sun Feb 01, 2004 5:51 pm
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.

Posted: Mon Feb 02, 2004 12:19 am
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;