mysql_num_rows does not work

Question

this is my php code

$sql="SELECT * FROM table_name WHERE FROM_UNIXTIME(date, '%Y') = $q order by id desc";
$q = mysql_query($sql) or die(mysql_error().$sql);
$sql1="SELECT * FROM table_name WHERE FROM_UNIXTIME(date, '%Y') = $q";
$query=mysql_query($sql1);

this gathers the results correctly (everything is correct)

but when i use this to calculate the total results it gives me nothing, however i had for example 3 results:

$total = mysql_num_rows($query);

Answer 1

As I can see $q is resource not a string value, for that reason $sql1 query would fail with error

Answer 2

Because your input isn't a query handle... Since you set $q to the query handle, you should be using that in mysql_num_rows():

$total = mysql_num_rows($q);

Answer 3

If you ask for the number of rows before you've actually retrieved the rows, the database may return zero or some intermediate number. This is true for Oracle and MySQL (don't know about MSSQL but I suspect it's the same). From the PHP docs:

Note: If you use mysql_unbuffered_query(), mysql_num_rows() 
will not return the correct value until all the rows in the 
result set have been retrieved.

Even for a buffered query, PHP would have to fetch all the rows in order to count them.