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?
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
$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";
}
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...
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
}
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.
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;
}
}
$result = $mysqli->query($query);
if($result){
perform action
}
this is how i do it, you could also throw an else there with a die...
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);
}
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 ...
}
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. "
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.
Source: Stackoverflow.com