Best way to check if MySQL results are back in PHP?

Question

I am looking for the best way to check and see if any results were returned in the query. It seems to me that I often write this part of the code, and sometimes I get errors, and sometimes not.

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 to do this:

 if (!$result) { PERFORM ACTION } 

If the first method does not work, sometimes it will be:

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

Then I even saw that I could do it as follows:

 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 } 

It gets my vote.

The OP assumes the query does not return any error, so this should be one way

Answer 2

One way to do this is to check what mysql_num_rows returns. A minimal finished example would be:

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

But it is recommended to check the return value of mysql_query and process it correctly if it is false (which may be caused by an error); probably also calling mysql_error and logging the error somewhere.

Answer 3

Whenever we execute queries to get some data, it is returned as an object. Then most of us will convert it to an array to easily iterate over strings. In php "empty ()" the function is used to check if the array is empty, i.e. If there is no data in it. Thus, we can check to see if the returned view of the request array is empty by doing this

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

Answer 4

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

 $result = mysqli_query($dbs, $sql); if(gettype($result)=='boolean'){ // test for boolean if($result){ // returned TRUE, eg 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

Of all the above options, I would use

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

checking for the result as shown below

 if (!$result) { PERFORM ACTION } 

This will be true if mysql_error is so effective, if an error occurs, you can enter a double username ...

Answer 6

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

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

Answer 7

I usually use === ( triple equivalent ) and __LINE__ , __CLASS__ to find 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; } } 

Answer 8

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

this is how i do it, you could also throw something else with the dying ...

Answer 9

If you still want to perform the action if the value of $result invalid:

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

This will take into account 0 and false , which mysql_num_rows() returns on error.

Answer 10

mysqli_fetch_array() returns NULL if there is no row.

In the procedural style:

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

In an object oriented style:

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

Answer 11

 $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 12

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

Thus, you check and act if the result is positive (you have an entry in your database that matches your query), otherwise (== 0) you performed the action when the result was not set.

Answer 13

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