Hello all,
I've been surfing forum threads on this, and there are plenty, with no satisfactory results. Here's the deal...
I've got quite a few instances of this very common query code on my site:

//example 1
$result = mysql_query("SELECT * FROM table WHERE ID = ".$_REQUEST['ID']."");
$row = mysql_fetch_array($result); //LINE OF CODE IN ERROR

//example 2
$result = mysql_query("SELECT * FROM table WHERE ID = ".$_REQUEST['ID']."");
while($row = mysql_fetch_array($result)) { //LINE OF CODE IN ERROR
//LOOP DATA
}

The PHP error log will frequently give a warning to this effect:"PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/local/ftp/path/to/file.php on line 15".
The important thing to know is that I ALWAYS get the desired result from the database. I've added "or die(mysql_error())" to the end of the queries with no error being displayed and echoing the $result will always give me the resource id number.

If I'm getting the data, why would there be a warning? Most of the diagnoses of this error in these forum threads I've been reading keep mentioning bad queries or database connections. Apparently, neither of those things are the problem if I'm actually getting results.

Enlightenment?

    Well, there is no "or die()" in the above examples, so if one of the queries should fail, then I would expect to see that exact error message. If that is not the case, then you need to show us the exact code you are using (of course changing passwords and such to generic values).

    Also, you should not be using $_REQUEST values directly in your SQL, you should at the very least be "sanitizing" it with [man]mysql_real_escape_string/man, and possibly may have to first do a stripslashes() if magic_quotes_gpc is enabled on your host (or, better yet, disable magic_quotes_gpc).

      Point well taken on the $REQUEST issue. I've actually been taken some steps to change that part. As far as the actual query code...

      $result = mysql_query("SELECT * FROM jocks WHERE jockID = ".$_REQUEST['jockID']."");
      $row = mysql_fetch_array($result); //LINE OF CODE IN ERROR
      

      The db has a table called "jocks" with a column called "jockID". There's really nothing more to say. The only code preceding this is an include for a site header. The code after is just a list of the variables taken from the database that are renamed and/or formatted in some matter to be echoed on the page.

      I should also mention that I've run this exact query using a working ID# in my sql client, SQLyog, at it works every time with no errors. The important thing to remember is that I consistently get the expected data and adding an "or die(mysql_error())" to the end of the query, as I've done many times already, will NOT yield a mysql error...but I get PHP errors.

      So...what ELSE could be at play other than a bad query?

        Well, add the or die() statement back in there to make sure that it's not the query that's failing, you never know...

          Assuming jockID is an integer...

          $sql = "SELECT * FROM jocks WHERE jockID = " . (int) $_REQUEST['jockID'];
          $result = mysql_query($sql);
          if(!$result)
          {
             error_log('Query failed [$sql]: ' . mysql_error());
             echo "<p class='error'>Sorry, a database error occurred. The error has been logged, blah blah blah...</p>";
             exit;
          }
          $row = mysql_fetch_array($result); //LINE OF CODE IN ERROR
          

            Well, folks...after some further detective work, and the recent visit of the MSNbot to one of our now dead pages that yielded an error, we've discovered the source of the issue. I'll thank you all for your help now and give you the stupid result: I didn't have any error checking on the page to account for an old ID passed from an old, cached page. My code assumed all the ID's would be good because I assumed, wrongly, that everyone would be choosing an item from a core list of current items on the home page. I didn't take into account the OLD stuff.

            May I say...duh.

            Thanks, everyone!

              Write a Reply...