MySQL PHP count(*) returning something weird

Question

I'm running the following query, expecting it to return an INTEGER, but it returns "Resource id #3"
What am I doing wrong?

$queryPlans = mysql_query("SELECT count(*) FROM infostash.rooms");
echo $queryPlans;

There are actually 15 rows in this table, and I would like to return the number 15. Any suggestions?

Answer 1

You need:

$queryPlans = mysql_query("SELECT count(*) FROM infostash.rooms");
$row = mysql_fetch_array($queryPlans);
echo $row[0];

mysql_query() isn't returning the result. It's returning a resource you can loop across and interrogate for rows (as above).

Answer 2

This is actually expected behavior according to the documentation:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

It's a regular select that returns one row with one column and should be treated as such. You can call mysql_fetch_array on the result:

$row = mysql_fetch_array($resource);
$count = $row[0];

Answer 3

$queryPlans = mysql_query("SELECT count(*) FROM infostash.rooms"); mysql_num_rows($queryPlans);

http://us.php.net/manual/en/function.mysql-num-rows.php

Answer 4

mysql_query() returns a result resource. You need another function the get "valuable information" from that resource. In this case mysql_fetch_array()/mysql_fetch_row()/mysql_fetch_object as cletus pointed out. Or (since it's only a single value) mysql_result().
Any sql query may fail for various reasons. You should always check the return value of mysql_query(). If it's FALSE something went wrong and mysql_error() can tell you more about it.

$mysql = mysql_connect(...) or die(mysql_error());
mysql_selecT_db(.., $mysql) or die(mysql_error($mysql));
$query = "SELECT count(*) FROM infostash.rooms";
$queryPlans = mysql_query($query, $mysql) or die(mysql_error($mysql));
$cRows = mysql_result($queryPlans, 0);
echo $cRows;

Answer 5

mysql_query will return a php resource(see: http://www.php.net/manual/en/language.types.resource.php).

The returned resource should then be passed to mysql_fetch_assoc or similar.

Since you are only getting the count, you can use the following:

$queryPlans = mysql_query("SELECT count(*) FROM infostash.rooms");
$count = mysql_result($queryPlans,0,0);
echo $count;

Answer 6

If you are planning on using the full query later (e.g. select , rather than count()), you can save yourself a database hit by using mysql_num_rows() on the full query. Example:

$queryPlans = mysql_query("SELECT * FROM infostash.rooms");
$results = mysql_fetch_array($queryPlans);
echo "There were " . mysql_num_rows($queryPlans) . " results";
while($row = mysql_fetch_assoc($queryPlans)){
    // Do stuff here
}