Checking for an empty result (PHP, PDO, and MySQL)

Solution 1:

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

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

Relevant documentation is here: PDOStatement::rowCount

Solution 2:

If you have the option of using fetchAll() then, if there are no rows returned, it will just be an empty array.

count($sql->fetchAll(PDO::FETCH_ASSOC))

will return the number of rows returned.

Solution 3:

You should not use rowCount for SELECT statements as it is not portable. I use the isset function to test if a select statement worked:

$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");

// I would usually put this all in a try/catch block, but I kept it the same for continuity
if(!$sth->execute(array(':today'=>$today)))
{
    $db = null;
    exit();
}

$result = $sth->fetch(PDO::FETCH_OBJ)

if(!isset($result->id_email))
{
    echo "empty";
}
else
{
    echo "not empty, value is $result->id_email";
}

$db = null;

Of course this is only for a single result, as you might have when looping over a dataset.

_{I thought I would weigh in as I had to deal with this lately.}

Solution 4:

$sql = $dbh->prepare("SELECT * from member WHERE member_email = '$username' AND member_password = '$password'");

$sql->execute();

$fetch = $sql->fetch(PDO::FETCH_ASSOC);

// if not empty result
if (is_array($fetch))  {
    $_SESSION["userMember"] = $fetch["username"];
    $_SESSION["password"] = $fetch["password"];
    echo 'yes this member is registered'; 
}else {
    echo 'empty result!';
}