PHP PDO Row Counting

Question

i want to check the rows if there are any events that are binded to a host with host_id parameter, everything is well if there is not any events binded to a host, its printing out none, but if host is binded to one of the events, its not listing the events, but if i remove the codes that i pointed below with commenting problem starts here and problem ends here, it lists the events. I'm using the fetchAll function above too for another thing, there is not any such that error above there, but with the below part, it's not listing the events, how can i fix that?

Thanks

try
{
    $eq = "SELECT * FROM `events` WHERE `host_id` = :id AND `confirmed` = '1' ";
    $eq_check = $db->prepare($eq);
    $eq_check->bindParam(':id', $id, PDO::PARAM_INT);
    $eq_check->execute();

    //problem starts here
    $count3 = $eq_check->fetchAll();
    $rowCount = count($count3);

    if ($rowCount == 0) 
    {
        echo "None"; 
    }
    //problem ends here

    while($fetch = $eq_check->fetch (PDO::FETCH_ASSOC) )
    { 
        $_loader = true;

        $event_id = $fetch['event_id'];
        $event_name = $fetch['event_name'];
        $link = "https://www.mywebsite.com/e/$event_id";

        echo "<a target=\"_blank\" href=\"$link\"><li>$event_name</li></a>";
    }
} 
catch(PDOException $e)
{
    $log->logError($e." - ".basename(__FILE__));
}

Thank you

MidnightLightning · Accepted Answer · 2012-09-14T05:59:54.947

1

You can't fetch twice without executing twice as well. If you want to not just re-use your $count3 item, you can trigger closeCursor() followed by execute() again to fetch the set again.

To reuse your $count3 variable, change your while loop into: foreach($count3 as $fetch) {

edited Sep 14 '12 at 05:59

answered Sep 14 '12 at 05:50

MidnightLightning

6,715
5
44
68

score -1 · Answer 2 · answered Sep 14 '12 at 05:47

-1

The reason that it is not listing the events when you have your code is that the result set is already fetched using your fetchAll statement (The fetchAll doesn't leave anything to be fetched later with the fetch).

In this case, you might be better off running a select count(*) to get the number of rows, and then actually running your full query to loop through the results:

An example of this in PDO is here:

<?php
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
if ($res = $conn->query($sql)) {

    /* Check the number of rows that match the SELECT statement */
  if ($res->fetchColumn() > 0) {

        /* Issue the real SELECT statement and work with the results */
         $sql = "SELECT name FROM fruit WHERE calories > 100";
       foreach ($conn->query($sql) as $row) {
           print "Name: " .  $row['NAME'] . "\n";
         }
    }
    /* No rows matched -- do something else */
  else {
      print "No rows matched the query.";
    }
}

$res = null;
$conn = null;
?>

Note that you cannot directly use rowCount to get a count of rows selected - it is meant to show the number of rows deleted and the like instead.

answered Sep 14 '12 at 05:47

Fluffeh

33,228
16
67
80

@Truth Are you suggesting it is better to retrieve a few (tens of?) thousand rows, put them *all* into an array just to get the count, then do it all over again to loop through them? That seems hugely expensive on server memory and the bandwidth between the database and apache. – Fluffeh Sep 14 '12 at 06:00
Well, rowCount works in MySQL, (which OP stated he was using), but yes. Since you want to use the resultset anyway, you query for it. If it's there, you get count AND results, if not, you get an empty array. Even if there are 10k rows, you're not fetching them **just** to get the count, you fetch them to process them, **and** get the count. Why not reuse the results? You don't need to query twice, just once. – Madara's Ghost Sep 14 '12 at 06:03
@Truth The vast number of PDO workarounds involving rowCount for MySQL suggests that it doesn't work, at least not consistently like in the old `mysql_*` functions. The edit on ML's answer is much better though (using a foreach on the already fetched array) - but I couldn't agree with fetch a potentially huge array just to get the count and then fetch it all over again. – Fluffeh Sep 14 '12 at 06:12

PHP PDO Row Counting

2 Answers2