Hi guys hope you are well. am developing an android social media application and am a bit stuck in mysqli query.
The application database comprises of feeds that can be commented, liked,reported and shared, this actions trigger a notification to be send to the concerned party and the notification data is stored in the notification_table. Notification information can be retrieved in the notification module in the application. My problem comes in the retrieval of notification information.
The database comprises of five tables: feed table, commented table,likes table,shared table and notification table. Now when a user comments on the feed the comment is saved in the commented table with a reference id to the feed table and a user id referencing to user information who commented, a notification is send to the feed owner and the notification information is saved in the notification table with the feed id reference to the feed table.
Similar chain of event occurs to the like,shared and reported table. now how can i retrieve notification data from notification table with a user id
a schema of the tables is bellow
feed_table
| id | feed | user_id | time_stamp |
-----------------------------------------------
| 0 | good day | 25470159221 | 08/08/2018 |
| 1 | bad day | 254701594222 | 08/09/2018 |
| 2 | normal day | 254701594223 | 08/10/2018 |
Comment_feed_table
| id | feed_id|comment| user id | time_stamp |
-----------------------------------------------
| 0 | 0 |codding| 25470159221 | 08/08/2018 |
| 1 | 1 |bad cod|254701594221 | 08/09/2018 |
| 2 | 2 |mysqli |254701594222 | 08/10/2018 |
user_like_table
| id | feed_id |user_id | time_stamp|
----------------------------------------
| 0 | 1 |25470159221|08/08/2018 |
| 1 | 1 |25470159222|08/08/2018 |
| 2 | 0 |25470159221|09/10/2018 |
user_share_table
| id | feed_id |user_id | time_stamp|
----------------------------------------
| 0 | 1 |25470159221|08/08/2018 |
| 1 | 1 |25470159221|08/08/2018 |
| 2 | 0 |25470159222|09/10/2018 |
user_notification_table
| id | feed_id |notificator_id | time_stamp|
----------------------------------------
| 0 | 1 |25470159222 |08/08/2018 |
| 1 | 1 |25470159229 |08/08/2018 |
| 2 | 0 |25470159224 |09/10/2018 |
now can i retrieve the notification data in notification_table that involved user_id 254701594221 in DESC order of time_stamp as in who commented,liked,shared at timely order
have tried it this way:
first retrieve feed_id of user comments, like and shared that match with notification feed_id
function select_likes($con,$phone,$offset,$no_records){
$respond=array();
$sql="SELECT
user_notification.feed_id As feed_like,
user_feed_likes.user_feed_id
FROM user_feed_likes
LEFT JOIN user_notification ON user_feed_likes.user_feed_id= user_notification.feed_id
WHERE user_feed_likes.phone = '$phone'
GROUP BY user_notification.feed_id
ORDER BY user_feed_likes.time_stamp desc
LIMIT $offset,$no_records
";
$results=mysqli_query($con,$sql);
if (mysqli_num_rows($results)>0) {
foreach ($results as $key => $value) {
//echo "
".$value['feed_id'];
array_push($respond,$value['feed_like']);
}
}
return $respond;
}
repeatedly for all the tables like,share,commented then use the ids to select from notification_table
function get_notification($con,$feed_id){
$respond=array();
$sql="SELECT
user_notification.id,
user_notification.feed_id,
user_notification.phone,
user_notification.message,
user_notification.tag,
user_notification.time_stamp,
user_credentials.username,
profile_pic.imagenamesized,
user_feeds.feed,
user_feeds.phone AS Feed_phone,
(SELECT username FROM user_credentials WHERE phone=user_feeds.phone) AS Feed_username,
(SELECT count(user_phone) FROM user_feed_comments WHERE feed_id=user_notification.feed_id ORDER BY id desc LIMIT 1) AS no_comments,
(SELECT count(phone) FROM user_feed_likes WHERE user_feed_id=user_notification.feed_id ORDER BY id desc LIMIT 1) AS no_likes,
(SELECT count(phone) FROM user_feed_shared WHERE post_id=user_notification.feed_id ORDER BY id desc LIMIT 1) AS no_shares
FROM user_notification
INNER JOIN user_credentials ON user_notification.phone=user_credentials.phone
LEFT JOIN profile_pic ON user_notification.phone=profile_pic.phone
LEFT JOIN user_feeds ON user_notification.feed_id=user_feeds.id
WHERE user_notification.feed_id IN('".implode("','",$feed_id)."')
GROUP BY tag ,user_notification.feed_id desc
ORDER BY user_notification.time_stamp desc;";
$results=mysqli_query($con,$sql);
if (mysqli_num_rows($results)) {
foreach ($results as $key => $value) {
array_push($respond, $value);
}
}
return $respond;
}
The problem with this method is that it doesn't follow the orderly manner of time especially when new offset is incremented (increase in the limit page e.g Limit From 0,10 increases to Limit from 10,20).
Secondly the Group by notification_id does not display the last record e.g group by notification_id does not display the last person who commented or liked.
Lastly, I don't think the reverse psychology here applies well in database query e.g selecting first the comment,like,and share ids in different queries and then combining them and selecting the notification_table using those feed_id feels like redundant and time consuming query PLEASE HELP
Any idea to achieve notification retrieve similar to twitter or facebook will be highly appreciated thank you.
