Best way to check if MySQL results returned in PHP?

Question

I'm looking for the best way to check and see if any results were returned in a query. I feel like I write this part of code a lot and sometimes I get errors, and sometimes I don't.

For example, I run this query to check if a username exists before inserting a new one into the database.

$result = mysql_query("SELECT * FROM ...");

Then I want to check and see if any results were returned. Here is one way I do it:

if (!$result) { PERFORM ACTION }

If the first way doesn't work, then sometimes this will:

if (mysql_num_rows($result)==0) { PERFORM ACTION }

Then I even saw that I could do it this way the other day:

list($total) = mysql_fetch_row($result);
if ($total==0) { PERFORM ACTION }

What is the best way to do this?

Answer 1

if (mysql_num_rows($result)==0) { PERFORM ACTION }

For PHP 5 and 7 and above use mysqli:

if (mysqli_num_rows($result)==0) { PERFORM ACTION }

This gets my vote.

OP assuming query is not returning any error, so this should be one of the way

Answer 2

One way to do it is to check what mysql_num_rows returns. A minimal complete example would be the following:

if ($result = mysql_query($sql) && mysql_num_rows($result) > 0) {
    // there are results in $result
} else {
    // no results
}

But it's recommended that you check the return value of mysql_query and handle it properly in the case it's false (which would be caused by an error); probably by also calling mysql_error and logging the error somewhere.

Answer 3

Whenever we do queries to get some data, it is returned as an object. Then most of us convert it to array for looping through the rows easily. In php "empty()" function is used to check if an array is empty i.e. if it has no data in it. So we can check if returned array representation of query isn't empty by doing like this

if(!empty($result)){
           //DO STUFF
}

Answer 4

What is more logical then testing the TYPE of the result variable before processing? It is either of type 'boolean' or 'resource'. When you use a boolean for parameter with mysqli_num_rows, a warning will be generated because the function expects a resource.

$result = mysqli_query($dbs, $sql);

if(gettype($result)=='boolean'){ // test for boolean
    if($result){  // returned TRUE, e.g. in case of a DELETE sql  
        echo "SQL succeeded"; 
    } else { // returned FALSE
        echo "Error: " . mysqli_error($dbs);
    } 
} else { // must be a resource
    if(mysqli_num_rows($result)){

       // process the data    

    }
    mysqli_free_result($result);  
 }

Answer 5

$connect = new mysqli('localhost', 'user', 'password', 'db');
$result = $connect->query("select * from ...");
$count=$result->num_rows;
if(empty($count)){
echo"Query returned nothing";
}
else{
echo"query returned results";
} 

Answer 6

mysqli_fetch_array() returns NULL if there is no row.

In procedural style:

if ( ! $row = mysqli_fetch_array( $result ) ) {
    ... no result ...
}
else {
    ... get the first result in $row ...
}

In Object oriented style:

if ( ! $row = $result->fetch_array() ) {
    ...
}
else {
    ... get the first result in $row ...
}

Answer 7

Of all the options above I would use

if (mysql_num_rows($result)==0) { PERFORM ACTION }

checking against the result like below

if (!$result) { PERFORM ACTION }

This will be true if a mysql_error occurs so effectively if an error occurred you could then enter a duplicate user-name...

Answer 8

Use the one with mysql_fetch_row because "For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error. "

Answer 9

$result = $mysqli->query($query);
if($result){
    perform action
}

this is how i do it, you could also throw an else there with a die...

Answer 10

If you would still like to perform the action if the $result is invalid:

if(!mysql_num_rows($result))
    // Do stuff

This will account for a 0 and the false that is returned by mysql_num_rows() on failure.

Answer 11

Usually I use the === (triple equals) and __LINE__ , __CLASS__ to locate the error in my code:

$query=mysql_query('SELECT champ FROM table')
or die("SQL Error line  ".__LINE__ ." class ".__CLASS__." : ".mysql_error());

mysql_close();

if(mysql_num_rows($query)===0)
{
    PERFORM ACTION;
}
else
{
    while($r=mysql_fetch_row($query))
    {
          PERFORM ACTION;
    }
}