CODEWITHSUNDEEP
phparrays
AJFMEDIA
AJFMEDIA

Reputation: 2123

Select Count Group and Output into Array with Key and Value

I'm looking to select all rows from my database where number_id = 1 and group the experience id's together and output the count of each in to an array with the experience id as the key and the count as the value. 

+------------+------------+
| number_id  | experience |
+------------+------------+
|  1         |  4         |
|  1         |  4         |
|  1         |  1         |
|  1         |  2         |
|  1         |  3         |
|  1         |  1         |
|  1         |  5         |
+------------+------------+

So the results should be:

4 => 2
1 => 2
2 => 1
3 => 1
5 => 1

This is what I have so far:

$query = "SELECT COUNT(*) AS SUM FROM comments WHERE number_id = '1' GROUP BY experience";

$result = mysqli_query($conn, $query);
$experience = array();
if ($result->num_rows>0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $experience[] = $row['SUM'];
    }
}

print_r($experience);

which outputs this

Array ( [0] => 2 [1] => 2 [2] => 1 [3] => 1 [5] => 1 )

but I need to be able to change they array key to be the experience id

How can I do this?

Upvotes: 0

Views: 36

Answers (1)

Nghi Ho
Nghi Ho

Reputation: 463

Just select experience and use it as key.

$query = "SELECT experience, COUNT(*) AS SUM FROM comments WHERE number_id = '1' GROUP BY experience";

$result = mysqli_query($conn, $query);
$experience = array();
if ($result->num_rows>0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $experience[$row['experience']] = $row['SUM'];
    }
}

print_r($experience);

Upvotes: 2

Related Questions