Select Count Group and Output into Array with Key and Value

Question

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?

Nghi Ho · Accepted Answer · 2018-08-26 13:07:13Z

2

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);

answered Aug 26, 2018 at 13:07

Nghi Ho

4631 gold badge6 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

splash58 Over a year ago

array_column(mysqli_fetch_all($result, MYSQLI_ASSOC), 'sum', 'experience');

Collectives™ on Stack Overflow

Select Count Group and Output into Array with Key and Value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related