Database query on an empty table not displaying what I expect

PHP
1

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.


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


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:


if ( ($stmt = $PDO->query("SELECT COUNT(show) FROM shows WHERE on_year = $current_year ")) >= 2 ){
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

PDO::query returns a [url=http://php.net/manual/en/class.pdostatement.php]PDOStatement object, not the result of the query.
You should do a fetch on that object first :slight_smile:

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.


$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


$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>";
}
3

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:

$stmt = $PDO->prepare("SELECT COUNT(show) FROM shows WHERE on_year = ?"));
4

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!


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

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 :slight_smile:

In fact, your code probably won’t work on < 5.4 because the compiler doesn’t recognise the syntax.