PHP PDO mysql counting rows returned

Question

There are a lot of questions on this and I have done a lot of research. However, I am still wondering if I am doing this right.

Here is my statement (I've simplified it):

    try {
        $stmt = $pdc_db->prepare("SELECT * FROM table WHERE color = :color"); 
        $stmt->bindValue(':color', $selected_color);
        $stmt->execute(); 
        $color_query = $stmt->fetchAll();
    } catch(PDOException $e) { catchMySQLerror($e->getMessage()); }

Now, I am using the following to see if this has returned any results:

if (count($color_query) > 0) {

This works, HOWEVER... the SELECT statement will only return one result. So now to access stuff in the results, I am using $color_query[0][colorname]. I know this is because I am using fetchAll(), but I really want to be using fetch()

But if I just use fetch(), I am losing the ability to do a count(), which is pretty simple to me. I know I can do a separate query and check the results of SELECT COUNT(*), but that seems like more work (setting two separate queries up for each)

There must be a way, using PDO in PHP with mySQL, to check if fetch() has returned a result?

there is no reason to use fetch instead of fetchall

Your Common Sense
– Your Common Sense

2012-01-22 05:49:52 +00:00
Commented Jan 22, 2012 at 5:49 — Your Common Sense
– Your Common Sense, Commented Jan 22, 2012 at 5:49

Mark Baker · Accepted Answer · 2012-01-22 00:44:43Z

4

$stmt->rowCount() after the execute(), but doesn't work with all databases... try it with MySQL and see what you get.

answered Jan 22, 2012 at 0:44

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

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

3 Comments

phpmysqlguy Over a year ago

Wow, this actually worked. In all my searching most places said rowCount() wouldn't work with mySQL, so I never bother trying.

Francis Avila Over a year ago

Whether rowCount() works or not depends on whether the underlying database driver uses a real cursor or just returns all results buffered. MySQL for the most part does the latter.

phpmysqlguy Over a year ago

Thanks for the info. I guess the real lesson here is don't assume rowCount() won't work. It DOES work in my situation for PHP PDO with mysql. Hopefully this can save someone else some time.

xdazz · Accepted Answer · 2012-01-22 05:52:21Z

0

You can do it with fecth, fecth will return false if no results returns.

if ($row = $stmt->fetch()) {
     //get the first row of the result.
     //....
}

answered Jan 22, 2012 at 5:52

xdazz

161k38 gold badges255 silver badges278 bronze badges

3 Comments

Your Common Sense Over a year ago

sometimes you have to know if there are results before starting the loop.

xdazz Over a year ago

@Col.Shrapnel He just need the first row of the result, will not need the loop.

Your Common Sense Over a year ago

well, i re-read the question and it may be so. it is quite unclear however.

Collectives™ on Stack Overflow

PHP PDO mysql counting rows returned

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related