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
|
1 2 3 4 5 |
CREATE TRIGGER `MostRecentFriend` AFTER INSERT ON `tbl_message` FOR EACH ROW UPDATE tbl_friendlist SET updated = New.created WHERE (( from_user_id=New.from_user_id&&to_user_id=New.to_user_id ) OR ( from_user_id=New.to_user_id &&to_user_id=New.from_user_id ) ) AND is_friend=‘1' |
Example of MYSQL query: recent chat for user_id=12
|
1 2 3 4 5 6 7 8 9 10 11 |
//example query 1 select * from tbl_friendlist where from_user_id='12' or to_user_id='12' order by updated desc //example query 2 select u1.user_id as user_id1,u2.user_id as user_id2,u1.firstname as firstname1,u2.firstname as firstname2,u1.lastname as lastname1,u2.lastname as lastname2,u1.avatar as avatar1,u2.avatar as avatar2,from tbl_friendlist as m left join tbl_users as u1 on u1.user_id=m.from_user_id left join tbl_users as u2 on u2.user_id=m.to_user_id where m.from_user_id='12' or m.to_user_id='12' order by m.updated desc limit 0,10 |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
//example query 1 select * from tbl_message where from_user_id='12' or to_user_id='12' group by (from_user_id+to_user_id) order by message_id //example query 2 select u1.user_id as user_id1,u2.user_id as user_id2,u1.firstname as firstname1,u2.firstname as firstname2,u1.lastname as lastname1,u2.lastname as lastname2,u1.avatar as avatar1,u2.avatar as avatar2,from tbl_message as m left join tbl_users as u1 on u1.user_id=m.from_user_id left join tbl_users as u2 on u2.user_id=m.to_user_id where m.from_user_id='12' or m.to_user_id='12' group by (m.from_user_id+m.to_user_id) order by m.message_id desc limit 0,10 |
More Stories
How to reset or change the MySQL root password?
Incorrect datetime value: ‘0000-00-00 00:00:00’ for column
MySQL String Functions