Count

DaveTomneyUK

Hi,

Could someone tell me how to count records and return them using php. I tried this below but it keeps returning the wrong number. It says "1" instead of "56" which is the number of records for that table.

$result = mysql_num_rows(mysql_query("SELECT count(pid) FROM my_gallery"));

echo "$result";

kburger

That's because it returned 1 row containing the number of rows in your table. Do this instead:

$result = mysql_num_rows(mysql_query("SELECT pid FROM my_gallery"));

rebelo

mysql_num_rows returns the number of resulting rows from the mysql query. In this case, your query only returns a single row. You need to grab the resulting 56 from the row or else remove the count() function from the query and grab all rows and then mysql_num_rows will give you what you want.

Also, for what it's worth, I imagine using the count() function and retreiving the results with php is more efficient than grabbing everything and counting with PHP. I would assume it's much less overhead for memory and system usage. This may not matter with a 56 record table, when if it were hundreds of thousands, it might.

DaveTomneyUK

That worked. Thanks. But I cant work out why count(*) wont work as isn't the count function for counting. 🙂

kburger

Yes. But you asked it to return a single row containing the total number of records. Try it at the command line or using something like phpMyAdmin. Examples:

SELECT count(pid) FROM my_gallery;
returns 1 row:
56

SELECT pid FROM my_gallery;
returns 56 rows: (i have no idea what your pid data looks like, but assuming it's auto-increment integer starting from 1)
1
2
3
4
...
54
55
56

LoganK

COUNT() will work, if you remove the mysql_num_rows and reference the column by name.