Implementing Real-Time Notifications with PHP and WebSockets or SSE

I'm developing a real-time messaging feature for a CRM and need assistance with pushing notifications to the front end without frequent database queries, which could increase server and database load.

Currently, I have a webhook set up to insert SMS messages into a MySQL database using PHP, as shown below:

if (isset($_POST['From']) && isset($_POST['Body'])) {
    $stmt = $pdo->prepare('INSERT INTO sms_messages (`number`, message) VALUES (:number, :message');
    $stmt->execute([
        'number' => $_POST['From'],
        'message' => $_POST['Body'],
    ]);

    // Function to send notification to client needed here.
}

I want to forward these notifications directly to my client's front end, using JavaScript, without constant polling. I have some experience with Ratchet for handling WebSockets in PHP but am unsure how to integrate the above PHP event with WebSocket to notify the client in real-time.

Here is the basic WebSocket server setup I have:

require 'vendor/autoload.php';
use Ratchet\Server\IoServer;
use Ratchet\Http\HttpServer;
use Ratchet\WebSocket\WsServer;
use Ratchet\MessageComponentInterface;
use Ratchet\ConnectionInterface;

class Chat implements MessageComponentInterface {
    public function onOpen(ConnectionInterface $conn) {
        echo "New connection! ({$conn->resourceId})\n";
    }

    public function onMessage(ConnectionInterface $from, $msg) {
        echo sprintf('New message from %d: %s'."\n", $from->resourceId, $msg);
    }

    public function onClose(ConnectionInterface $conn) {
        echo "Connection {$conn->resourceId} has disconnected\n";
    }

    public function onError(ConnectionInterface $conn, \Exception $e) {
        echo "An error has occurred: {$e->getMessage()}\n";
        $conn->close();
    }
}

$server = IoServer::factory(new HttpServer(new WsServer(new Chat())), 8080);
$server->run();

And the corresponding JavaScript for the client:

var conn = new WebSocket('ws://localhost:8080');

conn.onopen = function(e) {
    console.log("Connection established!");
    conn.send('Hello, World!');
};

conn.onmessage = function(e) {
    console.log(e.data);
};

conn.onerror = function(e) {
    console.error("Connection failed!", e);
};

function sendMessage(message) {
    if (conn.readyState === WebSocket.OPEN) {
        conn.send(message);
    } else {
        console.log("WebSocket is not open. ReadyState: " + conn.readyState);
    }
}

setTimeout(function() { sendMessage('Hello again!'); }, 1000);

I'm also considering server-side events (SSE) as an alternative, but like WebSockets, I'm unsure how to set it up without periodic database checks. Any guidance on how to efficiently push events from PHP to JavaScript in real-time, using either WebSockets or SSE, or any other advisable method would be greatly appreciated.

Please, have a look at the Push to an Existing Site section of the Ratchet documentation. It describes exactly what you need, and I believe there's no need to provide some source code here. It's all described very well in that section, with good source code examples that follow the descriptions.

Basically, after INSERTing a database record for a new text message, which you've already outlined in your question, you'll need to also place the same message data into ZeroMQ, which Ratchet will then receive in its main loop and forward/broadcast to all currently connected push clients, which reside in your client's frontend. One of the benefits of ZeroMQ is that it can run without a dedicated message broker (hence the "zero").

As a result, the connected push clients would not put load onto your MySQL database while receiving push messages, and the data exchange through ZeroMQ would be performed only once per text message. Moreover, as a message queue, ZeroMQ is inteded and optimized for such data exchange. As a result, the overall design and the implementation would be rather simple yet efficient, with pretty much no foreseeable bottlenecks.