Num rows returns wrong number

Question

Im trying to count the rows where there are 0 rows, but it return 1 row anyways, when there are no rows that matches the SQL query. Does anyone know what im doing wrong?

        $del = mysql_real_escape_string(strip_tags($_GET['del']));

    if ($del)
    {
        $sql6 = "SELECT count(*) FROM oneliners WHERE id='$del' AND to_user='".$_SESSION['username']."'";
        $result6 = mysql_query($sql6) or trigger_error(mysql_error()."".$sql6);
        $num_rows6 = mysql_num_rows($result6);

        echo $sql6;
        echo '<br>';
        echo $num_rows6;

        //If no rows return, do this
        if ($num_rows6 <= 0)
        {
            header("location:oneliner.php");
            exit();
        }

You are already using count(*) so it will always return 1 row. — ace, Oct 31 '11 at 16:38
You need to escape that `$_SESSION[]` as well. All superglobals that you inject into an sql-statement need escaping. — Johan, Oct 31 '11 at 17:09

Marcus · Answer 1 · 2011-10-31T16:49:04.927

The query always return one row. The row contains the count of rows. You should rather use mysql_fetch_row than mysql_num_rows, where the latter one count the number of rows returned by the query.

$sql6 = "SELECT count(*) FROM oneliners WHERE id='$del' AND to_user='".$_SESSION['username']."'";
$result6 = mysql_query($sql6) or trigger_error(mysql_error()."".$sql6);
if($num_rows6 = mysql_fetch_row($result6)) {
   echo $num_rows6[0]; // Prints the number of rows
}

Even though the easiest way to fix your problem would be to change your query to be:

$sql6 = "SELECT * FROM oneliners WHERE id='$del' AND to_user='".$_SESSION['username']."'";

I'd highly recommend to use the first alternative because this easier way will return ALL rows and thus be less efficient.

evilone · Answer 2 · 2011-10-31T16:53:17.877

You are counting already in sql query. That's returning always one row. Even if there's zero rows in database table. There's two ways of doing it:

SELECT COUNT(*) FROM ... and that's it. No more need for mysql_num_rows() function. If all you want to do is get a count of rows then this way is quicker.
SELECT * FROM ... and you can use mysql_num_rows() function. If you want to get all the rows anyway for displaying in a table and also count them, then this is a way to go.

Num rows returns wrong number

2 Answers2