Grouping MySQL results from query-CodePudding

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
    )

)

CodePudding user response：

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

CodePudding user response：

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