Help with PDO in polling app

[andrewheiss]

The lack of any seek or rewind function in PDO is really throwing me off... I'm trying to make a simple polling application. There are only 3 fields - id, response, and comments - and data is inserted through a simple form with some radio buttons. I can get the data in the database just fine through PDO (using sqlite behind it). Printing the data is tricky, though.

What I want is to be able to get a count of each of the options for statistical purposes. That was relatively easy, as shown from the excerpt below:

...
$sql = "SELECT response, response_comment FROM responses"; 
$stmt = $dbHandle->prepare($sql);
$stmt->execute();
...
$stats = $stmt->fetchAll(PDO::FETCH_COLUMN, 0); 
print_r(array_count_values($stats)); //only print_r for debugging purposes
...

I had to use PDO::FETCH_COLUMN because if I use FETCH_ASSOC, it gives me a multidimensional array that array_count_values can't handle.

Later on the page I want to also loop through the comments for each option. Here's that excerpt:

...
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<p>$row[response]</p>";
echo "<p>$row[response_comment]</p>";
}
...

However, when that code comes after the stats fetch, nothing shows up. If I move the comments fetch before the stats, the stats don't show up. I assume this is because PDO has already iterated through the statement object.

In MySQL I normally just use a data seek to restart the result array, but I've found that it's impossible with PDO (5th question here: [wiki.pooteeweet.org...] )

Anyone with experience with PDO know the best way to do this? Is there a way to restart the $stmt object? I guess I could have two queries, but that seems over the top...

[eelixduppy]

You can try to use rowCount [us2.php.net] with this application but it is not guaranteed that it will work with a SELECT statement with your database, but it's worth a shot because i don't think it will advance the internal iterator. Otherwise, I think you're only other option is to run a separate statement entirely:

SELECT COUNT(*) FROM ....

[andrewheiss]

Yeah, I ended up just doing two SQL queries. It was easier than trying to wrangle the array.