How do I display content with PDO based on num_rows?

Question

Apparently, the num_rows property does not work in PDO as it would with mysqli.

Normally, with mysqli, my code would look like this:

    <?php 
$conn = new mysqli('127.0.0.1','root','mypassword','mydbname');
if($conn->connect_errno){
die("Sorry, could not connect.");
}
$id = 1;

$qry = "SELECT * FROM customers WHERE id = ?";
$getCustomers = $conn->prepare($qry);
$getCustomers->bind_param("i",$id);
$getCustomers->execute();
$result = $getCustomers->get_result();
$count = $result->num_rows;

if($count == 0){
echo "Sorry, there are no results";
}else{
while($row = $result->fetch_object()){
echo $row->id;
echo $row->fname;
echo $row->lname;
echo $row->entry_date;
}
}

?>

How do I create the equivalent with PDO? Here is what I have tried so far:

<?php 
try{
$conn = new PDO('mysql:host=127.0.0.1;dbname=mydbname','root','mypassword');
}catch(PDOException $e){
echo $e;
}
$id = 1;


$qry = $conn->prepare("SELECT * FROM customers WHERE id = :id");
$qry->execute([':id'=>$id]);
$rows = $qry->fetchAll(PDO::FETCH_OBJ);
$count = count($rows);

if($count == 0){
echo "Sorry, there are no results for your criteria";
}else{
for($i = 0; $i < $count; $i++){
echo $rows->fname;
}
}
?>

[Duplicate] https://stackoverflow.com/questions/883365/row-count-with-pdo — Karlo Kokkak, Mar 25 '18 at 02:04

score 2 · Accepted Answer · answered Mar 25 '18 at 01:58

2

Yeah isn't PDO great ;p no need to count rows when you have already got them.

To loop over your result as you have an array.

Change:

for ($i = 0; $i < $count; $i++){
    echo $rows->fname;
}

To:

for ($i = 0; $i < $count; $i++){
    echo $rows[$i]->fname;
}

Or better just use a foreach.

foreach ($rows as $row) {
  echo $row->fname;
}

answered Mar 25 '18 at 01:58

Lawrence Cherone

46,049
7
62
106

In his sample code, he's checking the row count to ensure he's bubbling up the proper UX messaging. So, in this case, there is a need. – Jesse Q Mar 25 '18 at 02:39

score 0 · Answer 2 · answered Mar 25 '18 at 01:59

0

The statement fetchAll(PDO::FETCH_OBJ) returns an array containing all of the result set rows as described here. To get the size of the array use sizeof($count). That should give you the size of the array.

answered Mar 25 '18 at 01:59

Avi_B

76
9

The OP is using `$count = count($rows);`, the issue is the for loop. – Lawrence Cherone Mar 25 '18 at 02:01

Jesse Q · Answer 3 · 2018-03-25T03:29:43.870

0

To answer your question specifically. You can use rowCount() to retrieve the number of rows in a result:

$qry = $conn->prepare("SELECT * FROM customers WHERE id = :id");
$qry->execute([':id'=>$id]);
$count = $qry->rowCount();
$rows = $qry->fetchAll(PDO::FETCH_ASSOC);  //my personal preference

for($i=0; $i < $count; $i++) {
    echo $rows[$i]['fname'];
}

To more closely replicate your mysqli code:

while($row = $qry->fetch(PDO::FETCH_OBJ) {
    echo $row->fname;
}

Of course, you should always check $conn->errorCode() after each database execution to ensure something go sideways on you.

UPDATE: As Lawrence points out, rowCount() does not work with MS SQL Server. An alternative in that case is to use fetchAll() and count().

edited Mar 25 '18 at 03:29

answered Mar 25 '18 at 02:37

Jesse Q

1,451
2
13
18

1

rowCount is not guaranteed for SELECTS – Lawrence Cherone Mar 25 '18 at 02:45
Indeed. SQL Server will not return the proper answer in my experience. Answer updated. – Jesse Q Mar 25 '18 at 03:24

How do I display content with PDO based on num_rows?

3 Answers3