I'm trying to create a notification system like the one in Facebook. I explain my exact aim of this notification system at the end of this post.
The following code should show all notifications about posts/ratings/etc. except my posts of course. Also it should show all notifications where I'm the creator of a content (n.creator_id). But the result is empty:
SELECT
(SELECT COUNT(*) FROM `notification_read` WHERE `is_read`='0' AND `uid`='".$_SESSION['uid']."') AS count,
n.`id`,n.`site`,n.`site_id`,n.`creator_uid`,n.`type`,
nu.`id` AS nuid,nu.`uid` AS nu_uid,nu.`date`,
u.`gender`
FROM `notification` AS n
LEFT JOIN `notification_user` AS nu ON nu.`nid` = n.`id`
LEFT JOIN `users` AS u ON u.`id` = nu.`uid`
WHERE
(
nu.`uid` != '".$_SESSION['uid']."'
AND
n.`creator_uid` = '".$_SESSION['uid']."'
)
GROUP BY n.`id`
ORDER BY nu.`date` DESC
LIMIT 7
Table structures are:
notification: id, site_id, creator_uid, site, type
notification_user: id, nid, uid, date
notification_read: id, nuid, uid, is_read
Further explanation: I need to LEFT JOIN users because I need the gender to say "X liked his/her comment". The COUNT shows the amount of notifications I want to display on that globe icon.
Also I couldn't think of a better table structure for my needs thinking of that what I want to do. But I'm open for suggestions, if there are better ways or better table structures or anything else I could improve.
My aim is to display notifications for:
- content I've created (img uploaded, comments, ratings, etc)
- content of others I've commented, rated, etc.
- if I gained credits
- if someone used my referal link to sign up on the website
So pretty much the basic social network things. Thank you in advance for suggestions about that structure or/and to answers to my question why my SELECT statement shows an empty result.
