Mysql error when counting rows

Question

I have a problem when trying to count the rows in a table.

The variable $username_me equals to the session username. None of them is in an if or else statement.

The error i get is:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/*/public_html/**/sidebar.php on line 21

Line 21 is the last line in the code bit i pasted.

//Count unread oneliners
$oneliners_sql = "SELECT * FROM oneliners WHERE to_user='$username_me' AND read=0";
$oneliners_query = mysql_query($oneliners_sql);
$oneliners_num = mysql_num_rows($oneliners_query);

Change the second line to $oneliners_query = mysql_query($oneliners_sql) or die( mysql_error() ); — JJJ
– JJJ, Commented Oct 14, 2011 at 14:34
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 'read=0' at line 1 But i have tried to do read='0' too, and does not work eiher — Kaizokupuffball
– Kaizokupuffball, Commented Oct 14, 2011 at 14:36
Next do echo $oneliners_sql;. The problem is probably with $username_me. — JJJ
– JJJ, Commented Oct 14, 2011 at 14:37
@kaizo: probably $username_me contains a quote which is unbalancing/breaking the query. Dump a copy of $oneliners_sql here. — Marc B
– Marc B, Commented Oct 14, 2011 at 14:37

stivlo · Accepted Answer · 2011-10-15 03:00:05Z

3

Your code is vulnerable of SQL Injection. There is also an xkcd strip on the argument :-)

If magic_quotes_gpc are not enabled (and they shouldn't, magic quotes were one of the many design mistakes of PHP and they are now deprecated), a hacker (or most likely a bot!) could freely alter your queries causing all sort of troubles to your site.

The proper way to fix this serious problem, is by using Prepared Statements, for instance with PDO. Alternatively you can use mysql_real_escape_string:

$sql = "SELECT COUNT(*) FROM oneliners WHERE to_user='" . 
    mysql_real_escape_string($username_me) . "' AND `read`=0";

The trouble with mysql_real_escape_string is that you've to remember to always use it, while with prepared statements these issues are taken care for you. Also if you're not using prepared statements you can use intval to clean your integer inputs.

Let me stress it once more: SANITIZING YOUR INPUTS IS VERY IMPORTANT. You should not trust anything that comes from the user.

Your problem is caused by mysql_query returning FALSE when the query fails. If the query fails, mysql_query return FALSE instead of resource. You can call mysql_num_rows on resource, not on FALSE. (From the manual: mysql_query() returns a resource on success, or FALSE on error).
The error is caused by the read field name, since is a reserved word. Try to quote it with backticks (`) or even better, rename it.
To find out whether a row exists or not you can use COUNT(*) or SELECT 1

With COUNT the query will always return a value of 0 or 1 (I suppose that you've a unique index on to_user), except when the query fails of course.

$sql = "SELECT COUNT(*) FROM oneliners WHERE to_user='" . 
    mysql_real_escape_string($username_me) . "' AND `read`=0";

With SELECT 1 the query will return 1 if the row exists, otherwise it won't return any row.

$sql = "SELECT 1 FROM oneliners WHERE to_user='" . 
    mysql_real_escape_string($username_me) . "' AND `read`=0 LIMIT 1";

Which one to use depends if you need a count of the rows or you just need to know whether a row exists or not. If there is an unique index, they should be computationally very similar anyway.

edited Oct 15, 2011 at 3:00

answered Oct 14, 2011 at 14:36

stivlo

85.9k35 gold badges148 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Waqar Alamgir Over a year ago

yes exactly read the question first and then see my answer! problem was with ``

stivlo Over a year ago

@waqaralamgir, well I mentioned it: "The error is caused by the read field name, since is a reserved word. Try to quote it with backticks (`) or even better, rename it.". It's a good idea to avoid rerserved words anyway.

stivlo · Accepted Answer · 2011-10-15 02:20:57Z

1

.1. read is mysql reserved word and must be quoted in backticks:

$sql = "SELECT * FROM oneliners WHERE to_user='$username_me' AND `read`=0";

.2. never use SELECT * to count rows. Use SELECT count(*) instead.

.3. always run all your queries this way to see any error occurred.

//Count unread oneliners
$sql = "SELECT count(*) FROM oneliners WHERE to_user='$username_me' AND `read`=0";
$result = mysql_query($sql) or trigger_error(mysql_error()." ".$sql);
$row = mysql_fetch_row($result);
$oneliners_num = $row[0];

never use die() for this purpose, despite of all these stupid suggestions.

.4. Create a function for such a usual task.

function getOne($sql) {
  $result = mysql_query($sql) or trigger_error(mysql_error()." ".$sql);
  if ($row = mysql_fetch_row($result)) {
    return $row[0];
  }
}

So, you'll be able to get your num with one line

$oneliners_num = getOne("SELECT count(*) FROM oneliners WHERE to_user='$username_me' AND `read`=0");

edited Oct 15, 2011 at 2:20

stivlo

85.9k35 gold badges148 silver badges200 bronze badges

answered Oct 14, 2011 at 14:44

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

1 Comment

stivlo Over a year ago

Hi @Col.Shrapnel, just edited a couple of small typos, hope you don't mind :-)

RiaD · Accepted Answer · 2011-10-14 14:35:41Z

-1

mysql_query returned false instread of mysql result. So, there are some errors on your query or connection. You may read error with mysql_error

answered Oct 14, 2011 at 14:35

RiaD

47.8k12 gold badges85 silver badges128 bronze badges

Collectives™ on Stack Overflow

Mysql error when counting rows

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related