How to use COUNT() with MYSQLi and Prepared Statements?

Question

Hi, I need to count the number of persons with the same status from a table for a specific user.. I want to use prepared statements so I did something like this (not working):

$sql = 'SELECT status, COUNT(personID) FROM user_persons GROUP BY status WHERE userID = ?';

if ($stmt->prepare($sql)) {
    $stmt->bind_param('i', $userID);
    $stmt->bind_result($status, $count);
    $stmt->execute();
    while ($stmt->fetch()) {
        $data[$status] = $count;
    }
}

I want to get an array like this:

$data[favorite] = 126
$data[otherstatus] = 345

How to do it?

Answer 1

I believe the GROUP BY should be after the WHERE clause.

$sql = 'SELECT status, COUNT(personID) FROM user_persons WHERE userID = ? GROUP BY status'

Are you getting any other errors? Or was it just not doing what you wanted?