How can I store the result of an SQL COUNT statement in a PHP variable.

Question

I want to get the number of rows in my MySQL table and store that number in a php variable. This is the code I'm using:

$size = @mysql_query("SELECT COUNT(*) FROM News");

$size ends up being "Resource ID #7." How do I put the number of rows directly into $size?

Answer 1

You need to call mysql_fetch_row or one of its sister functions.

<?php
// untested
$result = @mysql_query("SELECT COUNT(*) FROM News");
// error handling
$row = mysql_fetch_row($result);
$count = $row[0];
?>

Answer 2

mysql_query returns a query resource id. In order to get values from it you need to use mysql_fetch_assoc on the resource id to fetch a row into an array.

$result = mysql_query("SELECT COUNT(*) FROM News");
$row = mysql_fetch_assoc($result);
$size = $row['COUNT(*)'];

Answer 3

On a related note you can use the mysql_num_rows() function to obtain the number of rows from a given query. This is handy if you need to grab the data but also know the number of rows.

<?php
  $result = @mysql_query("SELECT * FROM news");
  $count = @mysql_num_rows($result);
?>