Posted by: phillipnb | December 26, 2010

Supplied argument is not a valid MySQL result resource


Last week I was asked to troubleshoot the code given below.


define( "DATABASE_SERVER", "sigma12" );
define( "DATABASE_USERNAME", "superuser" );
define( "DATABASE_PASSWORD", "y67![**]0o" );
define( "DATABASE_NAME", "sigmawidgetx" );

$mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD);
mysql_select_db( DATABASE_NAME );

$sqlQuery = "SELECT DISTINCT bcity FROM widget ORDER BY bcity ";
$myResult = mysql_query($sqlQuery);
$myReturn = "";

While ($myWidget = mysql_fetch_object($myResult))
{
$myReturn = $myReturn."";
$myReturn = $myReturn."".$myWidget->bcity."";
$myReturn = $myReturn."";
}
$myReturn = $myReturn."";
mysql_free_result($myResult);

The code kept on giving the error

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:\Web2\Test\fetchAllCities.php on line 15
Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in C:\Web2\Test\fetchAllCities.php on line 22

The new php developer who was asked to troubleshoot this had no clue of what was going wrong. He tried different tricks but without any success. His thinking was that something was wrong in the way result was fetched from the database. The first thing I did was to add a few lines of error checking to the code. The code now looked like this:

define( "DATABASE_SERVER", "sigma12" );
define( "DATABASE_USERNAME", "superuser" );
define( "DATABASE_PASSWORD", "y67![**]0o" );
define( "DATABASE_NAME", "sigmawidgetx" );

$mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD);
if (!$mysql)
{
echo "\n".mysql_error();
die ("\nError while connecting to database");
}
mysql_select_db( DATABASE_NAME );

$sqlQuery = "SELECT DISTINCT bcity FROM widget ORDER BY bcity ";
$myResult = mysql_query($sqlQuery);
if (!$myResult)
{
echo "\n".mysql_error();
die("\nError while executing database query\n ");
}
$myReturn = "";

While ($myWidget = mysql_fetch_object($myResult))
{
$myReturn = $myReturn."";
$myReturn = $myReturn."".$myWidget->bcity."";
$myReturn = $myReturn."";
}
$myReturn = $myReturn."";
mysql_free_result($myResult);

Once this new code was run, the error messages changed. The error now looked like this:
No database selected
Error while executing database query

From this it can be seen that there is an error in the database selected – either there is a typo or the database does not exist. Once the correct database name was given the code stopped throwing any of the previous warnings and executed exactly the way the developer had planned.

So, till next time Happy Php Programming!

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: