1

What is the preferred method for getting the number of rows that are returned for a SELECT state when using PDO with prepared statements?

I am currently using rowCount() but the docs say I shouldn't be using that since "most databases" don't support it (It is actually working just fine for me, so I'm tempted to keep using it. I can't find any sources that list exactly which databases do not support it, but apparently mine is fine).

Instead they recommend I use fetchColumn() but that requires writing a completely separate SQL statement that includes the COUNT(*) in my sql statement.

This is what they propose (http://php.net/manual/en/pdostatement.rowcount.php#example-1038):

//SQL TO GET ROWS TO OUTPUT
$sql = 'SELECT *
  FROM properties
  WHERE lister_id = :lister_id
  AND lister_type = "landlord"';

$result = $dbh->prepare($sql);
$result->bindParam(':lister_id', $_SESSION['loggedin_lister_id'], PDO::PARAM_INT);
$result->execute();

//SQL TO GET NUMBER OF RETURNED ROWS
$row_num_sql = 'SELECT COUNT(*)
  FROM properties
  WHERE lister_id = :lister_id
  AND lister_type = "landlord"';

$row_num_result = $dbh->prepare($row_num_sql);
$row_num_result->bindParam(':lister_id', $_SESSION['loggedin_lister_id'], PDO::PARAM_INT);
$row_num_result->execute();
$num_rows = $row_num_result->fetchColumn();

if($num_rows > 0) {
  while($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo $row['name'];
  }
}

I find this method that requires me to write a separate and nearly identical sql statement to be redundant and a serious pain when using prepared statements. I can understand how this approach might be acceptable when using a short SQL statement with a basic query, but not in the case of a prepared statement.

1. Is there any other way I can use the fetchColumn() approach without having to rewrite what is almost exactly the same code?

2. Where can I find an official list of which databases rowCount() supports when using a SELECT statement? And since it is working on the database I am currently using, can I assume it is safe to use(assuming I am not updating my database anytime soon)?

zeckdude
  • 15,877
  • 43
  • 139
  • 187
  • 2
    Correct me, please, if I got you wrong. Are you running this long and windy code to get $num_rows variable only to use it in the `if($num_rows > 0)` condition? – Your Common Sense Jul 27 '13 at 11:36
  • http://php.net/manual/en/pdostatement.rowcount.php, you can use `$result->rowCount` – DevZer0 Jul 27 '13 at 11:37
  • @DevZer0: rowCount is not the best way! see `For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement` –  Jul 27 '13 at 11:40
  • 1
    you can try this if all your requirement is to test if there is rows to loop `$sql = 'SELECT * FROM properties'; $stmt = $db->prepare($sql); $stmt->execute(); if ($data = $stmt->fetch()) { do { echo $data['id'] . '
    '; } while ($data = $stmt->fetch()); } else { echo 'Empty Result'; }`     – DevZer0 Jul 27 '13 at 11:50
  • @Akam look at his code, what he is doing is to see if the rows > 0 to iterate through the result. – DevZer0 Jul 27 '13 at 11:51
  • @DevZer0: yes but NumRows with PDO is interested subject at the mean time –  Jul 27 '13 at 11:53
  • if its hurting this badly why not modify the source code and add the support for mysql – DevZer0 Jul 27 '13 at 11:54
  • @Your Common Sense, yes you are correct. I just want to make sure there is a row being returned and if so, run the loop to spit them out. But I would also like to know what the best approach would be if I need to know what action to take if there are a specified number of rows. – zeckdude Jul 27 '13 at 12:17
  • 1
    Why would one want to know if there are a specified number of rows returned? – Your Common Sense Jul 27 '13 at 12:19
  • Say for example, I want a special message to appear when a user has 5 listings, then it would be good to know that. – zeckdude Jul 27 '13 at 12:20
  • 2
    Why would you devise artificial examples? Do you have a certain practical problem at hand? – Your Common Sense Jul 27 '13 at 12:28
  • It's not an artificial example by any means. I have that situation on another page and the question applies to what I am asking here, so I thought it would be good to ask. What happens is that when a user creates 5 listings, I need a special badge to appear. But in order to make it appear, I need to check to see how many listings that user has. The badge appears on the same page where all the listings are displayed. So instead of writing nearly duplicate code, I would like to see how many rows were returned. – zeckdude Jul 27 '13 at 12:40
  • Would you rephrase your question to to show this. – david strachan Jul 27 '13 at 12:42
  • 1
    If you have your results already, you don't need no special routine to return the count - you can simply count them. Please, do not bug yourself with imaginary problems. Ther are alot of real ones to care for. – Your Common Sense Jul 27 '13 at 13:46

1 Answers1

0

If you don't want to use rowCount I'm think you should two query, or you can use fetchAll and count(fetchAll) for rowCount

the second way, Use SELECT *,COUNT(*) ...

  • I would love to use RowCount() but the php pdo docs say not to use them since "most databases" don't support it. It actually works on my setup so I'm leaning towards just using it, since it seems like much less trouble. – zeckdude Jul 27 '13 at 12:18
  • if database doesn't support it , you must emaluate it in php, or run another query, I think `count(fetchAll)` is the best. –  Jul 27 '13 at 12:43