fetching count and result in PDO

Question

If I have to query db and do something one the basis of number of rows , in mysql I simply query and I can simply query and use simply

if($result->num_rows >= 1){

but in PDO I have to

make count query , the use fetchCloumn() to get count
query again to get the same result and do whatever I want

Rowcount is not helpful , it doesn't work on select

so I am making query twice, PDO doesn't sounds to be helpful here, is my approach wrong or I am right? Thanks

So why not use `COUNT(...)`? As shown in the link you provided. — Funk Forty Niner, May 08 '14 at 14:40
`rowCount()` only works in conjunction with `::MYSQL_ATTR_FOUND_ROWS` — mario, May 08 '14 at 14:41
@Fred-ii- For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action. — user1765876, May 08 '14 at 14:43
Well, you either stick to the older method or endure PDO. Wish I could help you out more. If I could, I would ;-) — Funk Forty Niner, May 08 '14 at 14:44
It's definitively wrong if you're running twice **the same** query. Running different queries (`SELECT COUNT(*)...` and `SELECT foo, bar`, for instance) is normally just fine. But it all depends on your exact use case and code. In other words, how many rows and why do you need the row count before retrieving rows? — Álvaro González, May 08 '14 at 14:44
@ÁlvaroG.Vicario it just like i want to fetch list of all students, I make a query select* from student, now I want to see if num of rows are greater than 0 than use the data or do whatever — user1765876, May 08 '14 at 14:47
I don't think there's any problem here. You issue the query, if it didn't fail just run ::fetchAll(\PDO::FETCH_ASSOC);. If it's an array and larger than 0 then simply display the list, otherwise don't. — N.B., May 08 '14 at 14:59
When you're using a MySQL database PDO::RowCount does exactly what it should do. The databases that produce unreliable results aren't commonly used in combination with PHP to begin with. And if you really can't make use of PDO::RowCount then you can always use PDO::FetchAll, like @N.B. suggested, and then count the amount of elements. — Irdrah, May 14 '14 at 09:36

score 1 · Answer 1 · answered May 08 '14 at 14:55

IMHO, counting all the rows returned by the query (or issue a second query) just to find out if there are rows or not is kind of overkill.

$got_data = false;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $got_data = true;
    process_row($row);
}
if (!$got_data) {
     no_results();
}

If you do need the rowcount, it's trivial to get it while reading the result set:

$num_rows = 0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $num_rows++;
    process_row($row);
}
if ($num_rows==0) {
     no_results();
}

YMMV.

fetching count and result in PDO

1 Answers1