pdo count where

[Justafriend]

I am still working on learning pdo   slowly trying to migrate into the  newest  most secure operations  and have an issue with the count and no idea where to look for it  i have spent countless hours searching and am no further along so i thought id ask the people here if this is even possible

 

What i have is a count  total number of entries  per person which i have working perfectly  but the issue is i need to only count them when the  total is greater then 5

$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);  
 foreach($dbh->query('SELECT username,COUNT(*), WHERE COUNT(*)<5 
 FROM games  
GROUP BY username') as $row) {  
echo "<tr>";  
echo "<td>" . $row['username'] . "</td>";  
echo "<td>" . $row['COUNT(*)'] . "</td>";  
echo "</tr>";   
}  
?>  

so if a user only has 4 entries it will skip his name

I hope this makes sense and hope someone can help me

[Barand]

A WHERE condition filters inputs to the query. At that time the totals aren't known. To filter output, use HAVING

SELECT username,COUNT(*) as total, 
FROM games
GROUP BY username
HAVING total > 5

Edited January 17, 2017 by Barand

[Justafriend]

when i tried that with all the code it give me an Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\pawn\listtest.php on line 59

but if i i remove the having it  works no problem  here is the updated code

$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);  
 foreach($dbh->query('SELECT username,COUNT(*) as total, 
FROM games
GROUP BY username
HAVING total > 5') as $row) {  
echo "<tr>";  
echo "<td>" . $row['username'] . "</td>";  
echo "<td>" . $row['COUNT(*)'] . "</td>";  
echo "</tr>";   
}  
?>  

[Barand]

Syntax error in the query. Remove the comma after "as total".

 

As COUNT(*) now has the column alias "total" you should refer to it as $row['total'] and not $row['COUNT(*)'];

 

It's easier to give expressions like that a column alias, especially for complex expressions.