PHP - Common Newbie Pitfalls, 2: Supplied argument is not a valid MySQL result

[Atli]

What to discuss:

• What is a "MySQL resource".
• What causes the error.
• How to fix it.

Common Newbie Pitfalls
This article is the second installment in a series of (hopefully) many, following Markus' first installment: 1: Headers Already Sent.

Written by the PHP experts at bytes.com, these articles hope to cover those common bear traps often encountered — and subsequently asked on the bytes.com forums — by PHP newcomers in a clear and concise manner.

Part 2: Supplied argument is not a valid MySQL result

Index:

1. What is a "MySQL resource"?.
   1. MySQL "link" resource.
   2. MySQL "result" resource.
2. What causes the error?
3. How to fix it?

1. What is a MySQL resource?
A resource is a link to an external service or data source. (See PHP: Resources - Manual)
PHP has two main resource types that deal specifically with MySQL: "mysql_link" and "mysql_result".

1.1. MySQL "link" resources
These tell PHP how to communicate with MySQL.

When you call mysql_connect, PHP opens a connection to the MySQL server. If successful, the connection is opened and the connection details are stored in memory. The "mysql_link" resource is a link to the memory location where those details are stored.

1.2. MySQL "result" resources
These point to memory locations where PHP stores the results of a MySQL query.

When you do a mysql_query, MySQL returns all the rows in the result-set to PHP immediately. However, in order to give you the ability to traverse the result set at your own pace, PHP "buffers" the result in memory, storing the exact location of the data in a "mysql_link" resource, along with the position of your code inside the result set.

2. What causes the error?
This warning is shown when we try to pass variables that are not MySQL resources into functions that require them to be MySQL resources, such as the mysql_fetch_row function.

This is always a result of poor error handling. That is; the programmer forgot to write fail-safes in case the function that generates the resource fails. Common causes for that is when you pass invalid database info to the mysql_connect function or pass an invalid SQL query to the mysql_query function. Neither of those functions will cause the errors themselves, but they will return FALSE rather than the MySQL resource they are expected to return, so in the following code, functions that use their return values will not function correctly, and will display this error.

We see a lot of questions about this posted in the PHP forums. Developers that are new to PHP will often do something like this:

<?php
// Open a link to a MySQL database.
$my_link_resource = mysql_connect('host', 'usr', 'pwd');
mysql_select_db('db', $my_link_resource);
 
// Execute a query on the open database.
$sql = "SELECT stuff FROM my_table";
$my_result_resource = mysql_query($sql, $my_link_resource);
 
// Loop through the result set, printing each row.
while($row = mysql_fetch_row($my_result_resource)) {
    var_dump($row); // For debug only!
}
?>

There are 3 lines in that code that may well cause an error, such as the one this article covers:

1. Line #4. The "mysql_select_db" function is meant to set the active database on an open MySQL connection, using a MySQL link resource to connect to MySQL. If the preceding "mysql_connect" function fails, the "mysql_select_db" function will not know what MySQL server to use, and will therefore fail, giving us an error message.
2. Line #8. Again, if the "mysql_connect" call on line #3 fails, the "mysql_query" function will not know how to connect to MySQL, and will therefore fail.
3. Line #11. The "mysql_fetch_row" function relies on the "mysql_query" function to provide it a "mysql_result" resource. If any of the preceding functions: "mysql_connect", "mysql_select_db", or "mysql_query", fail, this function is also likely to fail.

3. How do I fix it?
There is only one thing you need to do to make sure this error doesn't affect you: validate the return value of the "mysql_*" functions.

How you do that is pretty simple: you use if...else statements to check if the return values of those functions are in fact what they are supposed to be. - Consider this rewrite of the example I showed you before.

<?php
// Open a link to a MySQL database.
$my_link_resource = mysql_connect('host', 'usr', 'pwd');
if($my_link_resource === FALSE) {
    echo 'Failed to connect to MySQL: ' . mysql_error();
    exit;
}
if(!mysql_select_db('db', $my_link_resource)) {
    echo 'Failed to select MySQL database: ' . mysql_error();
    exit;
}
 
// Execute a query on the open database.
$sql = "SELECT stuff FROM my_table";
$my_result_resource = mysql_query($sql, $my_link_resource);
 
// Makes sure the query was successful
if($my_result_resource !== FALSE) {
    // Loop through the result set, printing each row.
    while($row = mysql_fetch_row($my_result_resource)) {
        var_dump($row); // For debug only!
    }
}
else {
    echo 'MySQL Query failed.<br>';
    echo ' - Error: ' . mysql_error() . '<br>';
    echo " - Query: {$sql}<br>";
    exit;
}
?>

This, instead of resulting in the rather useless warning we are discussing, would print out useful error messages you could use to debug and fix the problem.

However, being rather verbose, a lot of developers like to replace the if...else blocks with a simple die call. This makes the code a lot shorter, but in return you lose a bit of the control the if...else blocks give you.
You could rewrite the previous example like so, to incorporate this method:

<?php
// Open a link to a MySQL database.
$my_link_resource = mysql_connect('host', 'usr', 'pwd') or die(mysql_error());
mysql_select_db('db', $my_link_resource) or die(mysql_error());
 
// Execute a query on the open database.
$sql = "SELECT stuff FROM my_table";
$my_result_resource = mysql_query($sql, $my_link_resource) or die(mysql_error());
 
// Loop through the result set, printing each row.
while($row = mysql_fetch_row($my_result_resource)) {
    var_dump($row); // For debug only!
}
?>

But regardless, either method will produce a lot more helpful error messages when your MySQL functions fail.