PHP, SQL-Query with IN clause troubleshooting

I have some problem with my Code, i tried to solve it on my own but i didnt found any matching solves.

So i have an array with User_ids…

var_dump($arr)
array(6) { [0]=> int(5) [1]=> int(6) [2]=> int(2) [3]=> int(3) [4]=> int(4) [5]=> int(7) }

JavaScript
​x
 
var_dump($arr)array(6) { [0]=> int(5) [1]=> int(6) [2]=> int(2) [3]=> int(3) [4]=> int(4) [5]=> int(7) }​

…and i want to get the amount of tasks every user has 🙂

$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT COUNT(`task_id`) AS `amount`, `u_id`, `name` FROM `v_all` WHERE `u_id` IN ($in)";
$stmt = $pdo->prepare($sql);
$stmt->execute($arr);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

JavaScript
 
$in  = str_repeat('?,', count($arr) - 1) . '?';$sql = "SELECT COUNT(`task_id`) AS `amount`, `u_id`, `name` FROM `v_all` WHERE `u_id` IN ($in)";$stmt = $pdo->prepare($sql);$stmt->execute($arr);$data = $stmt->fetchAll(PDO::FETCH_ASSOC);​

OUTPUT:

var_dump($data);
array(1) { [0]=> array(3) { ["amount"]=> string(2) "14" ["u_id"]=> string(1) "2" ["name"]=> string(15) "Max Mustermann" } }

JavaScript
 
var_dump($data);array(1) { [0]=> array(3) { ["amount"]=> string(2) "14" ["u_id"]=> string(1) "2" ["name"]=> string(15) "Max Mustermann" } }​

my problem is, that i was expecting 6 Users, not only 1. Im kinda stuck here, hope u can help me 😀

Thx a lot ^^

Answer

Thanks to “04FS” i could solve my problem! Now my results are displayed correctly 🙂 Thanks alot

This is due to your use of the aggregate function COUNT, without explicitly specifying proper GROUP BY criteria. – 04FS 10 mins ago

Advertisement

Answer