[RESOLVED] Simpler Row Count?

Shawazi

I was wondering if there was a simpler way to find out how many rows are in a table. The current code I use is:

$result = mysql_query($query);
$num = mysql_num_rows($result);

It just seems inefficient.

jignesh1

i think there was sql_calc_founds rows()
it a mysql function
and it take little longer

rkolbe

Well, instead of using PHP's MySQL functions, wouldn't it just be quicker to write up an SQL query to count() your rows with a select statement? http://dev.mysql.com/doc/refman/5.0/en/counting-rows.html

This is given that your database is DB built to take the work (e.g. built for the application).

[edit]

You would want your SQL to be something like:

$query = "SELECT count(*) FROM table";
$result = mysql_query($query);

echo $result;

That should return the count for your db....

[edit again]

I did not include mysql_result, which will be needed to return the actual result.

bpat1434

You could always do:

<?php
$num_rows = mysql_result(mysql_query("COUNT(*) `cnt` FROM `table_name`"), 0, 'cnt');
?>

It's not simpler; however, you don't have run a query to get results, then count the rows, you just count the rows in the table with the query itself and get the result.

laserlight

Generally, you can use something like:

$result = mysql_query("SELECT COUNT(*) tablename");
$row_count = mysql_result($result, 0);

Shawazi

That was a lot of responses fast. I'm gonna use the mysql_result approach over two lines. As far as the server is concerned, it won't take anymore time to do

$result = mysql_query("SELECT COUNT(*) tablename"); 
$row_count = mysql_result($result, 0);

than

$num_rows = mysql_result(mysql_query("COUNT(*) `cnt` FROM `table_name`"), 0, 'cnt');

Using two lines is just neater code 🙂 Oh and using a fieldname instead of * would also save some resource.

laserlight

Oh and using a fieldname instead of * would also save some resource.

If you have an autoincrement key column, use that. Using a column that may have null values could return an incorrect row count. If you meant that a column alias would change anything, I think it probably would not, since mysql_result() takes the first column in the result set by default, and your result set only has one column.

EDIT:
oops, and remember: my example has a typo error, since I left out the "FROM" keyword in SQL.

Weedpacket

The DBMS can optimise for COUNT(*) in ways that it can't for COUNT(<field>) - it doesn't need to look at any fields, for example (and so uses fewer resources). Even if the field in question is marked as non-null and indexed there's a possibility that it might have to do more work to count them (it depends on the DBMS's architecture, and I'm not that sure about MySQL's).