mysql_num_rows always returns 1

Question

The result is always 1:

$sql = 'SELECT COUNT(Vote) FROM ' . $table;
$res = mysql_query($sql, $conn);
$vote_total = mysql_num_rows($res);

I ran the $sql query in phpMyAdmin and it returns 3, so the query is not the problem. $vote_total is initialized globally to 0, so that 1 is coming from somewhere. What other information do I need to provide to make helping me easier?

Thanks, Ryan

Answer 1

mysql_num_rows returns the number of selected rows and not the fields of a certain row. Use mysql_fetch_row to fetch the row you have selected with your query:

$sql = 'SELECT COUNT(Vote) FROM ' . $table;
$res = mysql_query($sql, $conn);
$row = mysql_fetch_row($res);
$vote_total = $row[0];

You could also use mysql_result to fetch a row and get a certain field:

$vote_total = mysql_result($res, 0, 0);

This fetches the first row (zero based) and returns the first field (zero based).

Answer 2

There will only ever be one row. And in that row will be the count of votes.

$sql = 'SELECT COUNT(Vote) FROM ' . $table;
$res = mysql_query($sql, $conn);
$vote_array = mysql_fetch_array($res);
$vote_total = $vote_array[0];

If you want to count the number of votes with mysql_num_rows, you have to select ALL of the rows.

Answer 3

The tricky part is that even when the sql query contains a COUNT() statement, it is still a query result like any other. MySQL will return a row containg a single column with the number of rows that would have been returned, should you have issued a reglar query. mysql_num_rows() on the other hand, just counts the number of rows in the result of the query that was actually executed. In this case it is always a single row.

What you want is:

$sql = 'SELECT COUNT(Vote) FROM ' . $table;
$res = mysql_query($sql, $conn);
$data = mysql_fetch_row($res);
$vote_total = $data[0];

Answer 4

That query does return only one row. What you want is to retrieve the value from the query:

$row = mysql_fetch_array($res);
$vote_total = $row[0];