CODEWITHSUNDEEP
phpmysqlpdocount
user7269149
user7269149

Reputation: 25

Use pdo fetch_group to save sql count as individual variables

I'm trying to do a sql COUNT with php PDO::FETCH_GROUP to avoid using three separate sql queries

$genders = $db->query('SELECT COUNT(memberID), gender FROM members WHERE gender IS NOT NULL GROUP BY gender')->fetchAll(PDO::FETCH_GROUP);

The array for this looks like:

Array ( [3] => Array ( [0] => Array ( [gender] => Female [0] => Female ) [1] => Array ( [gender] => Male [0] => Male ) ) [2] => Array ( [0] => Array ( [gender] => Unknown [0] => Unknown ) ) )

And while I can print that using a foreach within another foreach loop it's not really what I want.

I've tried also to use bindColumn with a while loop:

$stmt = $db->prepare('SELECT m.gender, COUNT(*) as count FROM members m WHERE m.gender IS NOT NULL GROUP BY m.gender');
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_BOUND);
$stmt->bindColumn('count', $count);
$stmt->bindColumn('gender', $gender);
while ($row = $stmt->fetch())
    {
        echo $gender.' - '.$count.' ';
    }

But although they both work I want to know if it's possible to save each of the counts in relevant variables?

e.g. $femaleCount, $maleCount, $unknown etc

Upvotes: 1

Views: 363

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157888

Instead of FETCH_GROUP you need PDO::FETCH_KEY_PAIR. The link is to my article where I listed all the fetch modes explaining their purpose and possible use.

$sql = 'SELECT gender, COUNT(*) FROM members WHERE gender IS NOT NULL GROUP BY gender';
$data = $db->query($sql)->fetchAll(PDO::FETCH_KEY_PAIR);
foreach ($data as $gender => $count)
{
    echo "$gender - $count\n";
}

Upvotes: 2

EhsanT
EhsanT

Reputation: 2085

I'll use your second query and change it a little bit. Overall, your queries are OK, but you have to use an Associative array to store your data in it and then use it in your code.

The query will be like this:

$strSQL = 'SELECT gender, COUNT(*) as member_count FROM members WHERE gender IS NOT NULL GROUP BY gender';

Since you are not passing any variable to your query, then there is no need to user prepare just use a simple query

So the code will be like this:

$arrMembers = array();

$strSQL = 'SELECT gender, COUNT(*) as member_count FROM members WHERE gender IS NOT NULL GROUP BY gender';
$result = $db->query($strSQL);
while($row = $result->fetch(PDO::FETCH_ASSOC))
    $arrMembers[$row['gender']] = $row['member_count'];

Now you have an array with the name of $arrMembers with all the returned records form the query in it. The key of each row of the array is "gender" and the value of the row is "member count"

So after this you can use this code to echo the number of members of any gender in your code:

echo $arrMembers['Female']; // Will print number of female members
echo $arrMembers['Male']; // Will print number of male members
echo $arrMembers['Unknown']; // Will print number of members with unknown gender

Upvotes: 1

Related Questions