SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Nov 2011
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    PHP Code:
    $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?

  2. #2
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    PDO

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

    Code php:
    /*
     * $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
    }

  3. #3
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,904
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    @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.
    PHP Code:
    $result mysqli_query($link$query); 

    if ( 
    $result 

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

      
    // results not found 



    //
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    67
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    @ShinVe


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



    PHP Code:
    $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.

  5. #5
    SitePoint Enthusiast NuttySkunk's Avatar
    Join Date
    Jan 2012
    Location
    United Kingdom
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    PDO

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

    Code php:
    /*
     * $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?

  6. #6
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,904
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    @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 :
    PHP Code:

    #===============
    #
    # function qq()
    #     parameter: SQL statement
    #     return    
    #         success:    valid resource 
    #        otherwise:  False
    #  
    #===============
    function qq$sql )
    {
        
    $resource mysql_query($sql);
        
        
    $rows mysql_num_rowsmysql_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; 
    Last edited by John_Betong; Feb 1, 2012 at 05:48. Reason: I did not refresh the thread and missed @NuttySkunk's post
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  7. #7
    SitePoint Enthusiast NuttySkunk's Avatar
    Join Date
    Jan 2012
    Location
    United Kingdom
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    @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

  8. #8
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,904
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    @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


    .
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  9. #9
    SitePoint Enthusiast NuttySkunk's Avatar
    Join Date
    Jan 2012
    Location
    United Kingdom
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Only $50 per month for the entry level DV from Media Temple, there support seems pretty good. Sorry for off topic!

  10. #10
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    67
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    @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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •