SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,367
    Mentioned
    80 Post(s)
    Tagged
    3 Thread(s)

    Database query on an empty table not displaying what I expect

    Something strange is happening as I have an empty table but I am not getting what I expect displayed on the page.

    As Conformation I have run this and I get number = 0 which is what I expect.
    PHP Code:
    $sql "SELECT COUNT(show) FROM shows WHERE on_year = $current_year"
    $result $PDO->prepare($sql); 
    $result->execute(); 
    $number_of_rows $result->fetchColumn();
    echo 
    "<br/>number = $number_of_rows<br/>"
    When I run this code I expect "I'm sorry, there seems to be a problem." but I get "2013"
    PHP Code:
    if ( $stmt $PDO->query("SELECT COUNT(show) FROM shows WHERE on_year = $current_year ") ){
    echo 
    "$current_year";
    }
    else { echo 
    "<p>Toylander will be attending some shows in $current_year.<br>Dates to be confirmed</p>"; }

    The only way I can get what I want is by using:
    PHP Code:
    if ( ($stmt $PDO->query("SELECT COUNT(show) FROM shows WHERE on_year = $current_year ")) >= ){
    echo 
    "$current_year";
    }
    else { echo 
    "<p>Toylander will be attending some shows in $current_year.<br>Dates to be confirmed</p>"; }

    Obviously I am doing something wrong; can somebody let me know what it is!

    Out of interest the same thing was happening when I was using Mysql statements.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    PDO::query returns a PDOStatement object, not the result of the query.
    You should do a fetch on that object first

    You should get an error when you compare that object against an int as your're doing (E_NOTICE: Object of class PDOStatement could not be converted to int).
    Probably you have those disabled? Might want to enable them in php.ini as it saves a lot of head aches to see notices, at least in development environments (never on production!)

    Since you're not getting an error I'm assuming PHP is comparing the 2 to the resource number of the PDOStatement object or something like that, which is rather dirty and can't be relied upon.

    PHP Code:
    $res $PDO->query("SELECT COUNT(show) FROM shows WHERE on_year = $current_year"));
    if (
    $res->fetchColumn() > 0) {
        echo 
    "$current_year";
    } else {
        echo 
    "<p>Toylander will be attending some shows in $current_year.<br>Dates to be confirmed</p>";

    or, even better

    PHP Code:
    $stmt $PDO->prepare("SELECT COUNT(show) FROM shows WHERE on_year = ?"));
    $stmt->execute(array($current_year)); // or $stmt->execute([$current_year']); if you're on PHP>=5.4
    if ($stmt->fetchColumn() > 0) {
        echo 
    "$current_year";
    } else {
        echo 
    "<p>Toylander will be attending some shows in $current_year.<br>Dates to be confirmed</p>";

    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,367
    Mentioned
    80 Post(s)
    Tagged
    3 Thread(s)
    Thanks for the info @ScallioXTX and I have tried both your examples without success and am getting an internal server error.

    I am getting a bit tired now and will have a look at it again tomorrow.

    I based my second code snippet above on a user submitted example from the php site and I modified it slightly; but only in the if loop. I can not find the page again now.

    // or $stmt->execute([$current_year']); if you're on PHP>=5.4
    This is annoying as my code will break when the hosts update to that php version. I suppose everybody will have to rewrite their code then!

    Update:
    Server error due to an extra ) on this line:
    PHP Code:
    $stmt $PDO->prepare("SELECT COUNT(show) FROM shows WHERE on_year = ?")); 
    Last edited by Rubble; Oct 15, 2013 at 15:06. Reason: Added an Update

  4. #4
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,367
    Mentioned
    80 Post(s)
    Tagged
    3 Thread(s)
    Would this be worth doing where the code will need changing when the php version is updated as the chances of me remembering to do it are slim!

    PHP Code:
    if (version_compare(phpversion(), '5.4.0''<')) {
        
    $stmt->execute(array($current_year));
    } else (
    $stmt->execute(['$current_year']); ) 

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    You don't have to change array(something) to [something], as array(something) will still work; [] is just a different notation for array() since PHP 5.4, so I just added that if you're on PHP 5.4 you might want to use that

    In fact, your code probably won't work on < 5.4 because the compiler doesn't recognise the syntax.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •