Mysql_fetch_array is showing a warning for values of 0 or 1

Question

My SQL query counts the number of rows that meet a criteria. Sometimes this value is 0 or 1. This will create an error about returning a boolean.

How can this be avoided? Should I not use mysql_fetch_array?

// Query the db to count the # of comments.
$price_change_dbo = 
    mysql_query("
        SELECT COUNT(1) AS comment_count 
        FROM comments 
        WHERE user_id = '{$user_id}';"
    );

// Use the result. 
$price_change_row = mysql_fetch_array($price_change_dbo);

Here is the warning:

Warning: mysql_fetch_array() expects parameter 1 to be resource, 
         boolean given in /Applications/MAMP/... on line 1.

You should not use any `mysql_` functions at all. Have a look at `PDO`. — clentfort, Nov 05 '12 at 18:16
I agree, but working on some older website and they wouldn't appreciate me being the only one to use PDO for now. Thanks though :) — Don P, Nov 05 '12 at 18:18
`$result = mysql_query(...) or die(mysql_error())`. never EVER assume a query has succeeded. — Marc B, Nov 05 '12 at 18:36

score 2 · Answer 1 · answered Nov 05 '12 at 18:19

You should check whether the result ist false. That happens when an error occurs while querying the sql-command. If the result ist false, you should print the error with

echo mysql_error();

(However, you should consider using PDO or mysqli, because the mysql_ methods are deprecated).

At the end, your SQL is wrong. Delete the {} and it should work.

score 1 · Accepted Answer · answered Nov 05 '12 at 18:23

As the comments say, using the mysql_ family of functions is no longer recommended.

However, for sake of explanation of what actually happened:

mysql_query will not directly return the result of your count, it will create a resource and return a numeric identifier which other mysql_ functions will use to grab that resource.

The error message then tells you that instead of a resource, you got a boolean. Usually, this means that your mysql_query failed and returned false instead of a resource identifier.

So, you should first check to see if the result is false and behave accordingly. For example, use mysql_error to check what was wrong with the query.

score 0 · Answer 3 · answered Nov 05 '12 at 18:19

I think your sql query is bad:

 "  SELECT COUNT(1) AS comment_count 
    FROM comments 
    WHERE user_id = '".$user_id."'"

Those curly braces are causing an error in your query. This is causing mysql_query to not be a valid resource which is why mysql_fetch_array is complaining

Mysql_fetch_array is showing a warning for values of 0 or 1

3 Answers3