Records Polling from MySQL table

Question

What could be the best way to fetch records from a MySQL table for more than one clients connected, which are retrieving records concurrently and periodically.

So everyone gets the new messages as the new record enters the table but old messages should not retrieve again.

Current Table Structure: MessageId, Message, DatePosted, MessageFromID

Thanks

Answer 1

Your problem can be translated to: How can each client know, which records to read and which records not.

There are two completly different approaches to that, with very different properties.

1. Let the client care for that
2. Let the server care for it

Model #1 would quite simply require, that you

• Use something like an AUTO_INCREMENT on some field, if your MessageID is not guaranteed to be incrementing
• On the server give each client not only the messages, but also the ID
• Have the client keep this ID and use it as a filter for the next poll

Model #2 needs you to

• Have another table with 'ClientID and MessageID'
• Whenever a client gets a message, create a record there
• Use non-existance of such a record as a polling filter