I created a notification system, and that is pretty good. There are a few things you need to work on to make them perfect - so here I am.
The application I'm working on is a website written in PHP.
My notifications have a seen column that indicates whether a notification was detected or not. This column is updated when the user clicks the notification drop-down button, which is very similar to Facebook.
There are several cases where there are two identical notifications - the same user with the same address (URL). I want the notifications to be selected in a unique way, and the ones that need to be ignored. I don’t even understand how these identical are stored in the first place.
In any case, an approximate array of notifications is presented here:
Array ( [0] => Array ( [notification_id] => 34 [receiver_id] => 9 [notification_issuer] => 11 [notification_message] => Dugipo has liked your image [notification_target] => http:
As you can see, there are two pairs of identical arrays, and this is not what I need. I also try to achieve an effect when two or more people like the same thing, messages become like this: X, Y and n others like your image
I can’t think about how I will do it. In any case, this is a function / method that collects notifications:
public function getNotifications($seen = false) { global $db; $seen = ($seen === true) ? 1 : 0; $limit = ($seen === true) ? 5 : 10; $sql = 'SELECT n.*, u.user_avatar, u.username FROM ' . NOTIFICATIONS_TABLE . ' n LEFT JOIN ' . USERS_TABLE . " u ON n.notification_issuer = u.user_id WHERE receiver_id = ? AND notification_seen = $seen ORDER BY notification_time DESC LIMIT 5"; $query = $db->prepare($sql); $query->execute(array($this->id)); $result = $query->fetchAll(PDO::FETCH_ASSOC); /*for ($i = 0; $i < sizeof($result); $i++) { if ($result[$i]['notification_message'] == $result[++$i]['notification_message']) { $new_message = ''; if ($result[$i]['notification_issuer'] != $result[++$i]['notification_issuer']) { $new_message .= ''; } } }*/ return $result; }
The commented-on for loop was supposed to create the effect when people who loved a certain thing became the only notification, and the names were separated by a comma and "and" for other people who liked the image.
This is the function / method in which notifications are stored:
public function sendNotification($notification, $receiver, $notification_target = null, $notification_type = 'default') { global $db; $sql = 'INSERT INTO ' . NOTIFICATIONS_TABLE . '(receiver_id, notification_issuer, notification_message, notification_target, notification_type, notification_time) VALUES (?, ?, ?, ?, ?, ?)'; $query = $db->prepare($sql); $query->execute(array($receiver, $this->id, $notification, $notification_target, $notification_type, time())); }
An example of using this method:
Finally, here is the notification table schema:
+----------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------------+------+-----+---------+----------------+ | notification_id | int(11) unsigned | NO | PRI | NULL | auto_increment | | receiver_id | int(11) unsigned | NO | | NULL | | | notification_issuer | int(11) unsigned | NO | | NULL | | | notification_message | varchar(255) | NO | | NULL | | | notification_target | varchar(255) | YES | | NULL | | | notification_type | varchar(55) | NO | | default | | | notification_time | int(11) unsigned | NO | | NULL | | | notification_seen | tinyint(1) unsigned | NO | | 0 | | +----------------------+---------------------+------+-----+---------+----------------+
SQL Fiddle: http://www.sqlfiddle.com/#!2/6970b/1
So, I basically after two things:
Be able to select unique lines. Duplicate lines are ignored- Achieve the effect to combine notifications that have the same goal into one notification, which then shows the names of individuals of the same action (for example, like) as follows: X, Y, Z and others liked your image.
So, after SELECT from the database, I would like all users who created a notification for the same target to become a beautifully readable string, as I described above. I try to avoid users who loved / did not like / commented on the same goal in order to have a separate notification to the recipient - they must be concatenated somehow.
So, let's say the action is like an image. The five users of image_like-d are the same notification_target , or now we can determine what the user will be notified about with notification_type (which will be image_like) and reference_id (we will know that this will be the image now); So now we have a complete idea of what image_id is based on reference_id , and we also know this image because notification_type says the image is _like.
So, with this table:
id reference target issuer_id type 1 25 tg1 43 like 2 25 tg1 23 like 3 53 tg2 77 comment 4 53 tg2 23 comment 5 53 tg2 67 comment 6 53 tg2 98 dislike 7 34 tg3 65 like
From this table, these will be the following results:
- User43 and User23 liked reference25 (links to tg1)
- User77, User23 and User67 commented on link 53 (links to tg2)
- User 98 does not like link53 (links to tg2)
- User65 liked reference34 (links to tg3)
Any help would be appreciated.