December 8, 2022

WeAnswer

The complete guide to the beginners as well as to the experienced Web Developers

Get most recent users from chat table

Most recent users from MySQL (https://www.mysql.com/) query. When implement user chatting module then we must requirement like get recently chatting user list.

Large-scale database

If the large scale of the message then must be used a trigger which updates friendslist timestamp and gets friend list as per latest updated timestamp.

The trigger should be like that before/After insert message, friendslist table timestamp(updated) will update automatically.

DATABASE TABLE DESIGN

Table Name: tbl_friendlist

Here simple message table design, “friend_id” is autoincremented unique id, “from_user_id” is sender user id, “to_user_id” is receiver user id, “is_friend” is for it’s frined or not.

friend_id from_user_id to_user_id is_friend created updated
3 12 2 1 2016-07-19 15:09:09 2016-07-19 15:09:09
2 1 12 1 2016-07-19 15:08:09 2016-07-19 15:08:09
1 12 1 1 2016-07-19 15:08:07 2016-07-19 15:08:07

Table Name : tbl_message

Here simple message table design, “message_id” is autoincremented unique id, “from_user_id” is sender user id, “to_user_id” is receiver user id, “message” key is a content of the message, “message_type” is as per content type message like string, image, video, pdf, contact etc.

message_id from_user_id to_user_id message message_type created updated
3 12 2 hey 1 2016-07-19 15:09:09 2016-07-19 15:09:09
2 1 12 fine, How are you? 1 2016-07-19 15:08:09 2016-07-19 15:08:09
1 12 1 Hey,how are you 1 2016-07-19 15:08:07 2016-07-19 15:08:07

Database trigger should be like where you have to update filed “updated” from “tbl_friendlist” table when any message insert in “tbl_message” table

 

Example of MYSQL query: recent chat for user_id=12

Small-scale database

Below example very useful small scale of a database.

DATABASE TABLE DESIGN

Table Name : tbl_message

message_id from_user_id to_user_id message message_type created updated
3 12 2 hey 1 2016-07-19 15:09:09 2016-07-19 15:09:09
2 1 12 fine, How are you? 1 2016-07-19 15:08:09 2016-07-19 15:08:09
1 12 1 Hey,how are you 1 2016-07-19 15:08:07 2016-07-19 15:08:07

Example of MYSQL query: recent chat for user_id=12