How do I check db query returned results using PHP's PDO

Question

How do I check if my result set is empty using PDO in PHP?

$SQL = "SELECT ......... ORDER BY lightboxName ASC;";
$STH = $DBH->prepare($SQL);
$STH->bindParam(':member_id', $member_id);
$STH->execute();
$STH->setFetchMode(PDO::FETCH_ASSOC);

while($row = $STH->fetch()) {
    $lightbox_name = $row['lightboxName'];
    $lightbox_id = $row['lightboxID'];
    echo '<option value="'.$lightbox_id.'">'.$lightbox_name.'</option>';
}

I used to do it like this:

$result = mysql_query("SELECT ...... ORDER BY lightboxName ASC;");
if(!$result) { echo 'No results found!'; }

But have just started using PDO and prepared statements and checking against $STH does not seem to work as expected — it always has value!

Answer 1

I would try to use rowCount():

$rows_found = $STH->rowCount();

But, according to the manual:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

If it does not work for you, you can use the other method mentioned in the manual.

You can of course also set a variable before your while loop, change it in your loop and check the value after the loop...