Search code examples
phpmysqlsql-insertsql-deleteinbox

Mysql and PHP inbox update

I have created an inbox system. Signed in user can message other signed in users. The usermessage Table from database has two fields 1. userid and 2. messageid. The following is an excerpt from this table.

userid | messageid
12     | 1
13     | 1
14     | 2
15     | 2
12     | 3
15     | 3
12     | 4
14     | 4

In above situation when user '12' send message to user '13'. I want the messageid '1' to move to bottom of the table so that when the user '12' or '13' is checking his mailbox, the messageid '1' needs to appear on the top of inbox as a recent conversation. So far I have been able to achieve this by deleting the messageid '1' and inserting as a new query for each userid. This is my code:

DELETE FROM usermessage WHERE userid = '12' and messageid = '1';
INSERT INTO usermessage SET userid = '12', messageid = '1';
DELETE FROM usermessage WHERE userid = '13' and messageid = '1';
INSERT INTO usermessage SET userid = '13', messageid = '1';

Is it possible get this done with one query. So far, I was not able to find any definite answer in stackflow related to my problem.

Solution

  • Querying rows without SORT statement gives no guaranties that they will be sorted in the created time order. Its is just a chance that MySQL gives you such a sorting behavior since now.

    So it is necessary that you found or create an objective sorting rule. It can be the timestamp of the message, or a new timestamp column in you [usermessage] table.

    It is also noticeable that deleting and re-creating rows will affect the MySQL process cost because MySQL will need to update the indexes on [usermessage] table each time you delete and insert.