Is it possible to check if pdostatement::fetch() has results without iterating through a row?

Question

I have a page which needs to check for results, and the way I came up with to do it is successful, but iterates through the first row of results. Is there a way I can check without iterating, or to go back to that first row without executing the query again?

I was doing this:

 $q = pdo::prepare($SQL);
 $q->execute(array(':foo'=> foo, 'bar'=>bar);
 if(!q->fetch){
     //no results;
 }else{
      //results;
 };

It does pretty much exactly what I hoped, with the unfortunate side affect of skipping the first row of results.

I've resorted to running $q->execute() a second time. Is there a way to avoid doing this?

Answer 1

May be you'll find SELECT FOUND_ROWS() usefull for this. See example at php.net site http://www.php.net/manual/en/ref.pdo-mysql.php#77984

Answer 2

If you want to be lazy, you could always do something like:

$totalRows = count($resultSet->fetchAll());

However, this is less than efficient for large result sets.

Otherwise, see the manual page about rowCount() (particularly example #2) for what appears to be the standard workaround. There are some interesting user-supplied comments on that page as well.

Answer 3

rowCount() is known to work with mysql, so if portability is not a concern, use that.

otherwise, you can try to change the program logic, e.g.

 $stmt->execute();

 $count = 0;
 foreach($stmt as $record) {
  // output...
  $count++;
 }

 if(!$count)
  echo "no results!";

Answer 4

Just put the result of fetch into a variable:

if($row = $q->fetch()) {
    // $row contains first fetched row
    echo $row['coloumn_name'];
}
else
    // no results