Firstly I am fully aware that web-sockets and comet are better methods for this than AJAX, but I am trying to make my code as simple as possible so I am using AJAX for now.
Context
Currently the PHP code to get the messages for a specific conversation (using the 'cid'), is embedded in the page which the user views the messages on. Therefore the messages are only loaded every time the page is refreshed. Consequently I have decided to create an AJAX based system which fetches the messages for me at a set interval ie 5 seconds.
The Problem
The SQL code which is executed as a result of the AJAX request will obviously only need to return messages that are not currently displayed on the screen otherwise the full conversation will just be echoed onto the screen every 5 seconds. Therefore there needs to be a way for the AJAX to determine if the message is already on the screen, I presume that this will need to be accomplished using a database field however I don't know how.
Current database architecture
(I would post an image but my reputation is too low.)
Message_id (primary key), cid (which conversation the message belongs too), message, sent_user, receive_user, sent_time.
I am using AJAX without jQuery.
If message_id is autoincrement, that should work for testing if the message is shown yet. Just keep track of the highest message_id the client has received. (This is assuming you send all messages every x seconds rather than literally one message at a time.)
It could be a session variable, or you could somehow return the highest id through Ajax to the client and keep a js variable that holds the highest message_id received, and send it back to the server in a request parameter in your ajax, and use that in your SQL lookup
... where message_id > highest_mid_received and ...
I actually did this before and I think I did it by keeping track of the highest id on the client. If you're returning json through Ajax that will make it simple to send over both the messages and the ids. I think I was just using a string with delimiters when I did this, and using split in Javascript, and that works too even though its old fashioned now due to json.
Keeping a variable on the client makes more sense if you're worried about the user closing the tab accidentally and want them to get all the messages again automatically if they open a new tab to the page. If that's not a concern or you definitely want them to not receive all the messages again after closing the tab and launching a new one to the same page then keeping track in the session makes more sense.
Add a delivered field to your table. When your page is loaded (refresh), add a piece of code that updates all records and mark them as delivered. This is true because when the user firsts enter the chat, you mentioned that he/she receives all messages.
UPDATE messages SET delivered = 1 WHERE cid = ?
Now, during the ajax calls, you will select every message where delivered = 0
SELECT * FROM messages WHERE delivered = 0 AND cid = ?
UPDATE messages SET delivered = 1 WHERE delivered = 0 AND cid = ?
And then return the selected records for the AJAX call and append them into your chat. If the SELECT returns empty, it means that there is no new message to be appended to the conversation and you don't need to execute the UPDATE statement.