How to check if there are results with Prepared Statements...

Question

Hi there,

In the past I would do something like so:

  $sql = 'SELECT * FROM customers WHERE customer_email="' . mysql_real_escape_string($_POST['customer_email']) . '" ';
  $res = mysql_query($sql);

  // if there are no hits...
  if(mysql_num_rows($res) == FALSE) {

Today I am doing the same thing however with prepared statements:

  $stmt = $dbh->prepare("SELECT * FROM customers where customer_email = ? LIMIT 1");
  if ($stmt->execute(array($_POST['customer_email']))) {

The 2nd line of my prepared statement if($stmt... is that "if this query gets a result" or is it "if this query is executed regardless of results or not ie if it executes without error".

What I'm trying to work out is with prepared statements how do you do the equivalent of mysql_num_rows() == FALSE?

Thanks!!

Answer 1

You can use the rowCount() method of PDOStatement to get the number of rows returned:

$stmt = $dbh->prepare("SELECT * FROM customers where customer_email = ? LIMIT 1");
if ($stmt->execute(array($_POST['customer_email']))) {
    if($stmt->rowCount() > 0) {
       //fetch stuff...
    }
}  

Or, if rowCount() proves to be unreliable, you can do this:

$all = $stmt->fetchAll();
if(count($all)) {
   //loop through the set...
}