Whats the proper way to check if mysql_query() returned any results?

Question

I tried what seemed like the most intuitive approach

$query = "SELECT * FROM members WHERE username = '$_CLEAN[username]'
                                  AND password = '$_CLEAN[password]'";
$result = mysql_query($query);

if ($result)
{ ...

but that didn't work because mysql_query returns a true value even if 0 rows are returned. I basically want to perform the logic in that condition only if a row is returned.

Answer 1

Use mysql_num_rows:

 if (mysql_num_rows($result)) {
    //do stuff
 }

Answer 2

If you're checking for exactly one row:

if ($Row = mysql_fetch_object($result)) {
    // do stuff
}

You can use mysql_fetch_array() instead, or whatever, but the principle is the same. If you're doing expecting 1 or more rows:

while ($Row = mysql_fetch_object($result)) {
    // do stuff
}

This will loop until it runs out of rows, at which point it'll continue on.

Answer 3

What about this way:

$query = "SELECT * FROM members WHERE username = '$_CLEAN[username]'
                                  AND password = '$_CLEAN[password]'";
$result = mysql_query($query);
$result = mysql_fetch_array($result);

//you could then define your variables like:
$username = $result['username'];
$password = $result['password'];

if ($result)
{ ...

I like it because I get to be very specific with the results returned from the mysql_query.

-Ivan Novak

Answer 4

well...

by definiton mysql_query:

mysql_query() returns a resource on success, or FALSE on error.

but what you need to understand is if this function returns a value different than FALSE the query has been ran without problems (correct syntax, connect still alive,etc.) but this doesnt mean you query is returning some row.

for example

<?php

$result = mysql_query("SELECT * FROM a WHERE 1 = 0");

print_r($result); // => true

?>

so if you get FALSE you can use

mysql_errorno() and mysql_error() to know what happened..

following with this:

you can use mysql_fetch_array() to get row by row from a query

Answer 5

mysql_num_rows

Retrieves the number of rows from a result set. This command is only valid for statements like SELECT or SHOW that return an actual result set.

If none match, then zero will be the return value and effectively FALSE.

$result = mysql_query($query);

if(mysql_num_rows($result))
{ //-- non-empty rows found fitting your SQL query

  while($row = mysql_fetch_array($result))
  {//-- loop through the rows, 
   //--   each time resetting an array, $row, with the values

  }
}

Which is all good and fine if you only pull out of the database. If you change or delete rows from the database and want to know how many were affected by it...

To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query, use mysql_affected_rows().

$result = mysql_query($query);

if(mysql_affected_rows())
{ //-- database has been changed 

}

//-- if you want to know how many rows were affected:
echo 'Rows affected by last SQL query: ' .mysql_affected_rows();

mysql_query() will only return FALSE if the query failed. It will return TRUE even if you have no rows, but successfully queried the database.

Answer 6

I used the following:

if ($result != 0 && mysql_num_rows($result)) {

If a query returns nothing it will be a boolean result and it's value will be 0.

So you check if it's a zero or not, and if not, we know there's something in there..

HOWEVER, sometimes it'll return a 1, even when there is nothing in there, so you THEN check if there are any rows and if there is a full row in there, you know for sure that a result has been returned.

Answer 7

$sql = "SELECT columns FROM table";
$results = mysql_query($sql, $conn);
$nResults = mysql_num_rows($results);
if ($nResults > 0) {
   //Hurray
} else {
   //Nah
}

This should work.

Answer 8

$result = mysql_query(...);

if(false !== $result)
{
    //...
}