canoebrain canoebrain - 6 months ago 133
SQL Question

Checking for empty result (php, pdo, mysql)

Please, can anyone tell me what I'm doing wrong here? I'm simply retrieving results from a table then adding them to an array. Everything works as expected until I check for an empty result...

This gets the match, adds it to my array and echoes the result as expected:

$today = date('Y-m-d', strtotime('now'));

$sth = $db->prepare("SELECT id_email FROM db WHERE hardcopy = '1' AND hardcopy_date <= :today AND hardcopy_sent = '0' ORDER BY id_email ASC");

$sth->bindParam(':today',$today, PDO::PARAM_STR);

if(!$sth->execute()) {
$db = null ;
exit();
}

while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$this->id_email[] = $row['id_email'] ;
echo $row['id_email'] ;
}

$db = null ;
return true ;


When I try to check for an empty result, my code returns 'empty', but no longer yields the matching result:

$today = date('Y-m-d', strtotime('now'));

$sth = $db->prepare("SELECT id_email FROM db WHERE hardcopy = '1' AND hardcopy_date <= :today AND hardcopy_sent = '0' ORDER BY id_email ASC");

$sth->bindParam(':today',$today, PDO::PARAM_STR);

if(!$sth->execute()) {
$db = null ;
exit();
}

if ($sth->fetchColumn()) {
echo 'not empty';
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$this->id_email[] = $row['id_email'] ;
echo $row['id_email'] ;
}
$db = null ;
return true ;
}
echo 'empty';
$db = null ;
return false ;


As always, any help is appreciated. Thanks!

Answer

You're throwing away a result row when you do $sth->fetchColumn(). That's not how you check if there's any results. you do

if ($sth->rowCount() > 0) {
  ... got results ...
} else {
   echo 'nothing';
}

relevant docs here: http://php.net/manual/en/pdostatement.rowcount.php

Comments