Sol Sol - 3 months ago 12
MySQL Question

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

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);
}

Answer

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