How to check db query results using PHP PDO

Question

How to 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 did it like this:

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

But just started using PDOs and prepared statements, and checking for $STH does not work properly - it always matters!

Answer 1

I would try using rowCount() :

 $rows_found = $STH->rowCount(); 

But according to the manual:

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

If this does not work for you, you can use another method mentioned in the manual.

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

Answer 2

Just dump the variable information.

 var_dump($STH) 

EDIT

I only have a question. There is rowCount() . Just use $STH->rowCount(); to count the rows, then compare it.

Answer 3

OK, here is the code that it really works, and finally, after a few hours to look around and mix the answers to some answers, this is the one that will actually return the score to your select statement. SQL is the select statement, obviously $ username1 is the text field message for the username. $ password1 is the same as setting a username. Other than that, you should have everything you need to make it work, instead of your variables instead of mine. Hope this saves some people when I searched, trying to get exactly what I wanted here. This solution is for when you have a select statement and you want to be able to continue or stop returning it.

 SQL = 'SELECT * from Users WHERE Username = :Username AND Password = :Password'; STH = $conn->prepare($SQL); STH->bindParam(':Username', $username1); STH->bindParam(':Password', $password1); STH->execute(); STH->setFetchMode(PDO::FETCH_ASSOC); row_count = $STH->rowCount();