php - Best practice for websockets / MySQL realtime data

Question

I have a chat room that allows all guests on the website to view all messages but only authenticated users can post.

Posting is done via ajax to an api built in PHP.

At the moment all data for the chatroom is handled via ajax (with timed requests for new data) and now I want to switch that up to websockets (also in PHP using Ratchet).

I intend to keep the current ajax method for posting, but want the secure websocket to hand the posted data down to the client, both guests and authenticated members alike.

I have the socket set up and working but I'm not entirely sure what the best practice is for querying the chat database (MySql) to get the latest messages.

Currently I use the following to query the db for the latest 40 messages which as I mentioned above is done by ajax requests every 15 seconds... This data is then fitered by javascript on the client side to only append messages that they haven't already seen...

public static function read()
{
  $db = static::getDB();
  $sql = 'SELECT * FROM chatbox ORDER BY date_added DESC LIMIT 40';
  $stmt = $db->prepare($sql);
  $stmt->execute();
  $results = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $results;
}

... but I'm struggling to see how I can get this data via the websocket script without constantly querying the database with some kind of polling method which defeats the whole idea of using websockets to cut down on polling.

Basically I want all connected clients to wait for any messages and when they are posted this data is sent down to them.

Is there something I need to do with MySQL to notify a PHP script everytime a new message is posted or is this the totally wrong approach?

I've searched around a bit and I can't seem to find the answer, so any help to point me in the right direction would be greatly appreciated.

question from:https://stackoverflow.com/questions/65889238/best-practice-for-websockets-mysql-realtime-data

Answer 1

Waitting for answers