How do you count specific rows with PDO within a function?

Question

I see other tutorials on counting rows, but none on counting specific rows $row[scope] for specific results. How can this be done?

function counting(){
$query = "SELECT    * 
          FROM      table";

$result->fetchAll($sql, $params);   
$num_rows = count($result);

$counter = 0;

    //while($row = mysql_fetch_array($result)){ //old code
    foreach($result as $row){
        if($row[scope] == "all"){
            $counter++;
        }
    }
    $return = ($counter > 0)?TRUE:FALSE;
}

protected function fetchAll($sql, $params)
{
    $stmt = $this->dbh->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchALL(DatabaseAdapter::FETCH_ASSOC);
}

Hey, why don't you use the SQL language to do that for you? It's quite the right tool for the job in this case. (e.g) SELECT * FROM table where scope = 'all'; -- At that point you won't need PHP to filter it — Ryan
– Ryan, Commented Sep 8, 2016 at 20:34
This is just a piece of a long formula of about 5 different rows in the function (even though I made it simple), so I wanted to know the basics. — user6173198
– user6173198, Commented Sep 8, 2016 at 20:35
The basics are to let sql do it 100x quicker. Like select count(*) as theCount from myTable where scope='all' — Drew
– Drew, Commented Sep 8, 2016 at 20:36
Sure, if you want to drag your system to a halt and not learn sql :p — Drew
– Drew, Commented Sep 8, 2016 at 20:38

Your Common Sense · Accepted Answer · 2016-09-09 17:01:14Z

2

You need two functions for that.

Instead of fetchAll() you need just a general purpose function to run a query that returns PDOStatement which is essential.

protected function sql($sql, $params)
{
    $stmt = $this->dbh->prepare($sql);
    $stmt->execute($params);
    return $stmt;
}

in most cases it will work exactly the same way as your fetchAll() function without any changes in the code, but in case you explicitly need to get an array from it, you can always attach a fetchAll() to its call:

$result = $this->sql($sql, $params)->fetchAll();

And the second function you need is for the actual count:

function counting($value)
{
    $query = "SELECT count(*) FROM table WHERE scope = ?";
    return $this->sql($sql, [$value])->fetchColumn();
}

using such a function you can count (or detect the existence) for any scope.

Note that thanks to returned PDOStatement we are able to get another type of result - a scalar value instead of array.

upgrading While loops can be a pain sometimes....

You see, when SQL and PDO are properly used, you scarcely need a while loop at all

edited Sep 9, 2016 at 17:01

answered Sep 9, 2016 at 4:10

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

3 Comments

user6173198 Over a year ago

So you have to hit the database twice. This helped clarify this. Please fix fethColumn for other users when they see this. :)

Your Common Sense Over a year ago

sorry, why twice?

user6173198 Over a year ago

Sorry, I was using this logic for a function of counting within another function that I didn't add to the question itself. You are correct with your logic!

Collectives™ on Stack Overflow

How do you count specific rows with PDO within a function?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related