MySQL query results are not a resource

Question

I am having trouble with the query in this code. The problem is one I have had before, the $num = mysql_num_rows($result); part gives me a MySQL error saying it expected a resource. Usually when I have this error it is because I misplaced a single quote some where, but after looking I cannot find any problem, though this query is a bit more complex than what I usually have to deal with.

//connect to the database and stuff

$last_year = idate("Y")-1;
$month = date("m");
$day = date("d");                                   

$query = "SELECT bills.b_id, bills.c_id, bills.grand_total, bills.void, bills.date_added,
                 customers.b_name, customers.l_name, customers.f_name, customers.phone 
          FROM bills, customers 
          WHERE bills.c_id = customers.c_id 
                AND bills.void = '0' 
                AND date_added BETWEEN '".$last_year."-".$month."-".$day."' AND CURDATE()";
$result = mysql_query($query);
mysql_close($link);                 
$num = mysql_num_rows($result);

EDIT:

Although I already know the mysql_close() function is not the problem I went ahead and removed it and my code still does not work. This EXACT same code (other than the query) works in nearly a dozen other pages. The problem is in the query, the MySQL error (as stated before) is mysql_num_rows() expects parameter 1 to be resource. I am working on getting the specific error now.

Answer 1

Add some error handling to your code.

$result = mysql_query($query);
if ( !$result ) {
  echo 'the query failed: ', mysql_error();
  die;
}

(in "real" production code you might not want to display the actual query and error message to just any arbitrary user though).

see also: http://docs.php.net/mysql_error

Answer 2

1. Check to see if there were mysql errors. If you don't already have error reporting turned on, turn it on for development (error_reporting(E_ALL);).

2. Try waiting to close your mysql connection until after you're done with the result sets.

Answer 3

Try with the *mysql_close()* function at the end. If you close the mysql connection, *mysql_num_rows()* is not going to work

$num = mysql_num_rows($result);
.
.
//Any others mysql operations
.
.
mysql_close($link);

Answer 4

You close link to mysql before retrieving query results. That's the problem. Just don't use mysql_close() as PHP can automatically handle it.

Answer 5

"not a resource" error means your query failed.
change your mysql_query call in that manner

$result = mysql_query($query) or trigger_error(mysql_error().$query);

and see what's wrong with your query.
always do it this way to keep in touch with every error may occur