Efficient way to check if a mysql query returned no results?

[ShinVe]

I have been using this to check if there are any results from a query:

$result = mysqli_query($link, $query);

if (mysqli_num_rows($result) != 0)
{
//results found
} else {
// results not found
} 

This seems like a long-ish process to check to see if a query returned anything. Is there a more efficient way?

[Michael Morris]

PDO

If you are writing new code it is strongly recommended that you use PDO.

/*
 * $dsn is a database connection string, containing username, password
 * and other connection details as outlined in PDO's manual entry
 */
$db = new PDO( $dsn );
 
/*
 * In PDO we normally prepare statements so that we can reuse them.
 * there are two syntaxes for this - ? syntax and :value syntax.
 */
$statement = $db->prepare("
  SELECT *
  FROM users
  WHERE id = ?
");
 
/*
 * Now we look for the user with id 4. The array contains multiple possible
 * parameters to the query.
 */
$statement->execute(array(4));
 
/*
 * We can now ask the statement for a rowcount, or we immediately work
 * with the results, since if the query has a return an array will be returned,
 * else boolean false will be returned.
 */
 
$user = $statement->fetchAll();
 
if ($user !== false) {
  // work with results.
} else {
  // there where no results
}

[John_Betong]

@ShinVe


http://php.net/manual/en/mysqli.query.php

Return Values

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

$result = mysqli_query($link, $query); 

if ( $result ) 
{ 
  // results found 
  echo mysqli_num_rows($result);
}
else
{ 
  // results not found 
} 

//

[brense]

@ShinVe


http://php.net/manual/en/mysqli.query.php

$result = mysqli_query($link, $query); 

if ( $result ) 
{ 
  // results found 
  echo mysqli_num_rows($result);
}
else
{ 
  // results not found 
} 

//

The result can still be empty even if it was succesful.

If you use pdo, as suggested above. You get an array back (empty array if result is empty) so you can do sizeof($array) or count($array) to check if you have 0 results or not.

[NuttySkunk]

PDO

If you are writing new code it is strongly recommended that you use PDO.

/*
 * $dsn is a database connection string, containing username, password
 * and other connection details as outlined in PDO's manual entry
 */
$db = new PDO( $dsn );
 
/*
 * In PDO we normally prepare statements so that we can reuse them.
 * there are two syntaxes for this - ? syntax and :value syntax.
 */
$statement = $db->prepare("
  SELECT *
  FROM users
  WHERE id = ?
");
 
/*
 * Now we look for the user with id 4. The array contains multiple possible
 * parameters to the query.
 */
$statement->execute(array(4));
 
/*
 * We can now ask the statement for a rowcount, or we immediately work
 * with the results, since if the query has a return an array will be returned,
 * else boolean false will be returned.
 */
 
$user = $statement->fetchAll();
 
if ($user !== false) {
  // work with results.
} else {
  // there where no results
}

Congrats on member of the month.

Do you have any personal recommendations on further resources for PDO?

[John_Betong]

@NuttySkunk
First check if it is available on your SERVER - I made this mistake when recently changing hosts

@Michael Morris
Yes I agree that PDO is a better option if it is available on the SERVER;


If PDO is not available then a public function may simplify the process slightly :

#===============
#
# function qq()
#     parameter: SQL statement
#     return    
#         success:    valid resource 
#        otherwise:  False
#  
#===============
function qq( $sql )
{
    $resource = mysql_query($sql);
    
    $rows = mysql_num_rows( mysql_query($sql) );
    
    return $rows ? $resource : NULL;
}

//=========================================
function index()
{    
    $sql = 'SELECT id FROM jokes WHERE id = 125';
     
       //  Maybe have resource with rows > 0
    if( $qry = qq( $sql ) ) 
    {
             var_dump( $qry );
    }
    else
        {
         echo 'Yes we have no rows';
        }
            
    die; 

[NuttySkunk]

@NuttySkunk
First check if it is available on your SERVER - I made this mistake when recently changing hosts

I have a Dedicated Virtual Server (Media Temple) with root access and permission to install whatever I require

[John_Betong]

@NuttySkunk

I have a Dedicated Virtual Server (Media Temple) with root access and permission to install whatever I require

I am envious and would dearly love to the same control especially since my current host is not living up to expectations


.

[NuttySkunk]

Only $50 per month for the entry level DV from Media Temple, there support seems pretty good. Sorry for off topic!

[brense]

@NuttySkunk



I am envious and would dearly love to the same control especially since my current host is not living up to expectations


.

Download wamp

If you have a router you can create your own webhost :P