PHP: SQL ticks that don't make any sense

Question

Possible Duplicate:
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

My code was working fine last I checked, but now it seems to have broken. Here is the error:

    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/nerblog/public_html/nerblog/arch.php on line 4

... and here is the code on line 4:

    $q = mysql_query("SELECT * FROM main WHERE month='$month' AND year='$year' LIMIT 12 ORDER BY id DESC");

    while ($sql = mysql_fetch_array($q ))

It's a boolean because your query is invalid – Mitch Dempsey May 09 '12 at 23:02 — Mitch Dempsey, May 09 '12 at 23:02

score 3 · Answer 1 · answered May 09 '12 at 23:01

3

Your SQL query is failing. Try moving "LIMIT" command after "ORDER" to the end of the query:

SELECT * FROM main WHERE month='$month' AND year='$year' ORDER BY id DESC LIMIT 12

answered May 09 '12 at 23:01

Varol

1,668
1
21
30

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

According to the documentation:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

The mysql_fetch_array() function expects a result resource returned by mysql_query. If that call failed, it returns false and that is what you are passing to your mysql_fetch_array() function, hence why it fails.

The problem is actually in your SQL. You need to have the LIMIT clause last in your queries as such:

 SELECT * FROM main WHERE month='$month' AND year='$year' ORDER BY id DESC LIMIT 12

One way to ensure errors in your query don't bleed down into other code is to check the value first:

$q = mysql_query("SELECT * FROM main WHERE month='$month' AND year='$year' ORDER BY id DESC LIMIT 12");

if($q != FALSE)
{
   // Query didn't fail, so get results
   while ($result = mysql_fetch_array($q))
   {
      // Do something with $result
   }

}

score 1 · Answer 3 · answered May 09 '12 at 23:03

mysql_error() exists for a reason.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY id DESC' at line 1

Meaning it didn't expect to see ORDER BY where you put it. The reason for that is that LIMIT should come after ORDER BY, not before.

PHP: SQL ticks that don't make any sense

3 Answers3