php pdo - retrieve results and row count - portable solution

Question

I'm currently querying the db twice to retrieve the row count of my result set, and my result set before I run loops on the rows returned like this:

$result = $db->query("SELECT COUNT(*) FROM mytable");
$result1 = $db->query("SELECT * FROM mytable");

$count = $result->fetchColumn();

if($count == $value){

while($row = $result1->fetch(PDO::FETCH_ASSOC)){
//execute code
    }
} elseif($count == $value2) { 
//execute other code 
}

I've just converted to PDO and so don't know it as well. The code used to use mysql_num_rows. I'm looking for a way I can do this without querying the db twice, this solution that I've been using works for small purposes but is obviously unnecessary load on the database if it were scaled. Many Thanks

EDIT: My reason for moving to PDO is for it's portability features, and thus rowCount() isn't suitable in every instance.

It always is a good idea to read the documentation of the tool you use. It shows you the list of available methods, for example. Here it shows you this: php.net/manual/en/pdostatement.rowcount.php — arkascha
– arkascha, Commented Dec 31, 2015 at 18:49
@Script47 rowCount does not execute on SELECT statements. — Ohgodwhy
– Ohgodwhy, Commented Dec 31, 2015 at 18:50
@Ohgodwhy it works fine when I use it. If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. Though it does come with the warning of: However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications. However, it does work for me. — Script47
– Script47, Commented Dec 31, 2015 at 18:51
rowCount() does work for me in this instance using mysql, however given the warning in the documentation, and the reason for me converting to PDO was the fact that it's portable with most databases, it's not a long term fix. — Adam Copley
– Adam Copley, Commented Dec 31, 2015 at 19:09

Rajdeep Paul · Accepted Answer · 2015-12-31 19:33:50Z

1

You can use rowCount() method to get number of rows, like this:

$result = $db->query("SELECT * FROM mytable");
$count = $result->rowCount();

echo $count;

while($row = $result->fetch(PDO::FETCH_ASSOC)){

    // your code

}

Here's the reference:

rowCount()

Edited:

In case you don't want to use rowCount() method, this is another way of doing it.

$result = $db->query("SELECT * FROM mytable");
$result_set = $result->fetchAll();
$count = count($result_set);

echo $count;

while($row = array_shift($result_set)){

    // your code

}

Here are the relevant references:

edited Dec 31, 2015 at 19:33

answered Dec 31, 2015 at 19:04

Rajdeep Paul

16.9k3 gold badges21 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Copley Over a year ago

apologies that was just a typing error, not a copy/pase of actual code, and doesn't answer the question I asked. Good observation nonetheless, I have edited now.

Austin Schmidt Over a year ago

It should be noted that rowCount() is NOT guaranteed to work for SELECT queries across all platforms!

Ohgodwhy · Accepted Answer · 2015-12-31 18:54:11Z

0

In your case, there's no reason to perform a COUNT(*) query. You can just count how many rows were returned.

$count = $result->fetchAll();

Now $count holds reference to how many rows were returned.

answered Dec 31, 2015 at 18:54

Ohgodwhy

50.8k11 gold badges86 silver badges114 bronze badges

3 Comments

Adam Copley Over a year ago

This answer doesn't work whereas $result->rowCount(); does.

Ohgodwhy Over a year ago

@AdamCopley What about my solution doesn't work for you?

Adam Copley Over a year ago

Vaguely yes, however you omitted the important count($result) which Rajdeep Paul provided and thus the first working solution to my problem. Thank you for your answer though, it is much appreciated.

Collectives™ on Stack Overflow

php pdo - retrieve results and row count - portable solution

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related