What to discuss:
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?
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_resu lt".
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:
[code=php]<?php
// Open a link to a MySQL database.
$my_link_resour ce = mysql_connect(' host', 'usr', 'pwd');
mysql_select_db ('db', $my_link_resour ce);
// Execute a query on the open database.
$sql = "SELECT stuff FROM my_table";
$my_result_reso urce = mysql_query($sq l, $my_link_resour ce);
// Loop through the result set, printing each row.
while($row = mysql_fetch_row ($my_result_res ource)) {
var_dump($row); // For debug only!
}
?>[/code]
There are 3 lines in that code that may well cause an error, such as the one this article covers:
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.
[code=php]<?php
// Open a link to a MySQL database.
$my_link_resour ce = mysql_connect(' host', 'usr', 'pwd');
if($my_link_res ource === FALSE) {
echo 'Failed to connect to MySQL: ' . mysql_error();
exit;
}
if(!mysql_selec t_db('db', $my_link_resour ce)) {
echo 'Failed to select MySQL database: ' . mysql_error();
exit;
}
// Execute a query on the open database.
$sql = "SELECT stuff FROM my_table";
$my_result_reso urce = mysql_query($sq l, $my_link_resour ce);
// Makes sure the query was successful
if($my_result_r esource !== FALSE) {
// Loop through the result set, printing each row.
while($row = mysql_fetch_row ($my_result_res ource)) {
var_dump($row); // For debug only!
}
}
else {
echo 'MySQL Query failed.<br>';
echo ' - Error: ' . mysql_error() . '<br>';
echo " - Query: {$sql}<br>";
exit;
}
?>[/code]
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:
[code=php]<?php
// Open a link to a MySQL database.
$my_link_resour ce = mysql_connect(' host', 'usr', 'pwd') or die(mysql_error ());
mysql_select_db ('db', $my_link_resour ce) or die(mysql_error ());
// Execute a query on the open database.
$sql = "SELECT stuff FROM my_table";
$my_result_reso urce = mysql_query($sq l, $my_link_resour ce) or die(mysql_error ());
// Loop through the result set, printing each row.
while($row = mysql_fetch_row ($my_result_res ource)) {
var_dump($row); // For debug only!
}
?>[/code]
But regardless, either method will produce a lot more helpful error messages when your MySQL functions fail.
- 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:
- What is a "MySQL resource"?.
- MySQL "link" resource.
- MySQL "result" resource.
- What causes the error?
- 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_resu lt".
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:
[code=php]<?php
// Open a link to a MySQL database.
$my_link_resour ce = mysql_connect(' host', 'usr', 'pwd');
mysql_select_db ('db', $my_link_resour ce);
// Execute a query on the open database.
$sql = "SELECT stuff FROM my_table";
$my_result_reso urce = mysql_query($sq l, $my_link_resour ce);
// Loop through the result set, printing each row.
while($row = mysql_fetch_row ($my_result_res ource)) {
var_dump($row); // For debug only!
}
?>[/code]
There are 3 lines in that code that may well cause an error, such as the one this article covers:
- Line #4. The "mysql_select_d b" 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_conn ect" function fails, the "mysql_select_d b" function will not know what MySQL server to use, and will therefore fail, giving us an error message.
- Line #8. Again, if the "mysql_conn ect" call on line #3 fails, the "mysql_quer y" function will not know how to connect to MySQL, and will therefore fail.
- Line #11. The "mysql_fetch_ro w" function relies on the "mysql_quer y" function to provide it a "mysql_resu lt" resource. If any of the preceding functions: "mysql_connect" , "mysql_select_d b", or "mysql_quer y", 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.
[code=php]<?php
// Open a link to a MySQL database.
$my_link_resour ce = mysql_connect(' host', 'usr', 'pwd');
if($my_link_res ource === FALSE) {
echo 'Failed to connect to MySQL: ' . mysql_error();
exit;
}
if(!mysql_selec t_db('db', $my_link_resour ce)) {
echo 'Failed to select MySQL database: ' . mysql_error();
exit;
}
// Execute a query on the open database.
$sql = "SELECT stuff FROM my_table";
$my_result_reso urce = mysql_query($sq l, $my_link_resour ce);
// Makes sure the query was successful
if($my_result_r esource !== FALSE) {
// Loop through the result set, printing each row.
while($row = mysql_fetch_row ($my_result_res ource)) {
var_dump($row); // For debug only!
}
}
else {
echo 'MySQL Query failed.<br>';
echo ' - Error: ' . mysql_error() . '<br>';
echo " - Query: {$sql}<br>";
exit;
}
?>[/code]
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:
[code=php]<?php
// Open a link to a MySQL database.
$my_link_resour ce = mysql_connect(' host', 'usr', 'pwd') or die(mysql_error ());
mysql_select_db ('db', $my_link_resour ce) or die(mysql_error ());
// Execute a query on the open database.
$sql = "SELECT stuff FROM my_table";
$my_result_reso urce = mysql_query($sq l, $my_link_resour ce) or die(mysql_error ());
// Loop through the result set, printing each row.
while($row = mysql_fetch_row ($my_result_res ource)) {
var_dump($row); // For debug only!
}
?>[/code]
But regardless, either method will produce a lot more helpful error messages when your MySQL functions fail.