Grouping MySQL results from query

Question

I am trying to create a basic notification system. The function below works well, however I'd like to be able to group notifications for the same notification item into the same array item. So, any query result that has the same [item] and [item_id] would be grouped together and just update the count for that item group. So I guess adding a new array item count for that group? I have no idea how to approach this. Any help would be much appreciated.

Query Function:

function get_notifications($connect, $user_id) {

    $query = "SELECT * FROM notifications WHERE for_id = {$user_id} AND seen = 0 ";
    $result = mysqli_query($connect, $query) or die(mysql_error($connect));
    while($row = mysqli_fetch_assoc($result)){
        $notifs[] = $row;
    }
    return $notifs;
}

$notifs = get_notifications($connect, $_SESSION['user_id']);

Current Result:

Array
(
[0] => Array
    (
        [note_id] => 3
        [for_id] => 20
        [from_id] => 20
        [item] => like_pp
        [item_id] => 104
        [seen] => 0
        [date] => 2022-01-19 12:55:20
    )

[1] => Array
    (
        [note_id] => 4
        [for_id] => 20
        [from_id] => 20
        [item] => like_comment
        [item_id] => 332
        [seen] => 0
        [date] => 0000-00-00 00:00:00
    )

[2] => Array
    (
        [note_id] => 5
        [for_id] => 20
        [from_id] => 23
        [item] => like_pp
        [item_id] => 104
        [seen] => 0
        [date] => 0000-00-00 00:00:00
    )

[3] => Array
    (
        [note_id] => 6
        [for_id] => 20
        [from_id] => 20
        [item] => pp_like
        [item_id] => 102
        [seen] => 0
        [date] => 2022-01-19 15:03:23
    )

)

Desired Result: ([item] => like_pp and [item_id] => 104 were the same so combined and updated notification_count)

Array
(
[0] => Array
    (
        [note_id] => 3
        [for_id] => 20
        [from_id] => 20
        [item] => like_pp
        [item_id] => 104
        [seen] => 0
        [notification_count] => 2
        [date] => 2022-01-19 12:55:20
    )

[1] => Array
    (
        [note_id] => 4
        [for_id] => 20
        [from_id] => 20
        [item] => like_comment
        [item_id] => 332
        [seen] => 0
        [notification_count] => 1
        [date] => 0000-00-00 00:00:00
    )

[2] => Array
    (
        [note_id] => 6
        [for_id] => 20
        [from_id] => 20
        [item] => pp_like
        [item_id] => 102
        [seen] => 0
        [notification_count] => 1
        [date] => 2022-01-19 15:03:23
    )

)

You can use `GROUP BY item, item_id` to combine all the rows for the same item and item id into the same result row. But how do you want to get the remaining columns from that? — Barmar, Jan 19 '22 at 22:32
Side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Jan 19 '22 at 22:51

Eduard Uta · Accepted Answer · 2022-01-19T22:40:03.737

0

I think what you need is a the query that groups by user_id (for who) and item_id (for what type of notif) and delivers the count of this group. Something like:

$query = "SELECT count(*) as notification_count, for_id, item_id FROM notifications WHERE for_id = {$user_id} AND seen = 0 group by for_id, item_id ";

Optionally, if you need in the output the name of the item, for displaying or other purposes, it can be also added:

$query = "SELECT count(*) as notification_count, for_id, item_id, item FROM notifications WHERE for_id = {$user_id} AND seen = 0 group by for_id, item_id, item ";

edited Jan 19 '22 at 22:40

answered Jan 19 '22 at 22:35

Eduard Uta

2,477
5
26
36

1

Eduard Uta wow thats it! Thank you! – Chris Jan 19 '22 at 22:39
Quick question - how would I also get a total count for all items ungrouped? – Chris Jan 19 '22 at 22:58
I think you either create a new BE function or do a queryzilla as you can find in the accepted answer of: https://stackoverflow.com/questions/45801993/counting-total-rows-and-grouping-by-a-column-in-mysql – Eduard Uta Jan 19 '22 at 23:06

score 0 · Answer 2 · answered Jan 19 '22 at 22:41

Use GROUP BY and aggregation functions.

SELECT MIN(note_id) AS note_id, MIN(for_id) AS for_id, MIN(from_id) AS from_id,
        item, item_id, MIN(seen) AS seen, COUNT(*) AS notification_count, MIN(date) as date
FROM notifications
WHERE for_id = {$user_id} AND seen = 0
GROUP BY item, item_id

Grouping MySQL results from query

2 Answers2