CODEWITHSUNDEEP
phpmysqlcountmysqli
jonagoldman
jonagoldman

Reputation: 8754

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

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?

Upvotes: 0

Views: 4503

Answers (2)

jeee
jeee

Reputation: 1

Put an alias to it, e.g:

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

...then pick it up using:

fetch_object:

$row->count_p

fetch_assoc:

$row['count_p']

Upvotes: 0

xil3
xil3

Reputation: 16439

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?

Upvotes: 2

Related Questions