CODEWITHSUNDEEP
phpmysql
Ryan
Ryan

Reputation: 12113

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

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.

Upvotes: 13

Views: 37743

Answers (8)

joebert
joebert

Reputation: 2663

$result = mysql_query(...);

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

Upvotes: 0

Orson
Orson

Reputation: 15451

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

This should work.

Upvotes: 6

james
james

Reputation:

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.

Upvotes: 3

random
random

Reputation: 9955

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.

Upvotes: 6

Gabriel Sosa
Gabriel Sosa

Reputation: 7956

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

Upvotes: 1

Ivan Novak
Ivan Novak

Reputation: 666

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

Upvotes: 2

dirtside
dirtside

Reputation: 8280

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.

Upvotes: 7

thedz
thedz

Reputation: 5572

Use mysql_num_rows:

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

Upvotes: 23

Related Questions