Two WHERE statements

Question

Possible Duplicate:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result

I've done a search and couldn't find anything that could specifically help me.

I was hoping you could help.

I'd like to execute a MySQL query which searches a table for entries which meet two criteria (type = green AND on = yes)

I am presented with: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /link/to/my/file.php on line 36

Here is an extract from the code (line 36):

`$green = "SELECT * FROM homepage_vars WHERE type = 'green' AND on = 'yes'";
 $green = mysql_query($green);
 $green = mysql_fetch_array($green);`

Answer 1

ON is a MySQL reserved keyword. If you use it as a column or table identifier, you need to enclose it in backquotes:

SELECT * FROM homepage_vars WHERE type = 'green' AND `on` = 'yes'

You'll then have another problem once the query syntax is corrected. You have overwritten the variable $green several times. Originally, it held your query SQL, but was then used for the query result resource. That's fine, but then you will overwrite it with the row fetched by mysql_fetch_array() and its contents will be an array or FALSE. Subsequent attempts to fetch rows will fail since $green is no longer a result resource.

Always test for query success or failure before attempting to fetch rows. Call mysql_error() to see the error reported by the MySQL server, which would have pointed to invalid syntax near 'on or something similar.

$green = "SELECT * FROM homepage_vars WHERE type = 'green' AND on = 'yes'"; 
$query = mysql_query($green); 
if ($query) {
  // Don't overwrite your query resource!
  // Use a new variable!
  $row = mysql_fetch_array($query);
}
else {
  // Failure!
  echo mysql_error();
}