How to check real-time database change with php websocket?

Question

I want to update a div only when there is change in a certain field in the database, I’m currently using Polling, but I want to switch to websocket for performance reasons, but I only found examples of chats, and it’s not what I need.
Someone has a tip on how to check changes in the comic book with websocket?

Answer 1

You have several ways.

1. Mysql: Using sys_exec().

   It can be used as a Trigger, whenever the data is added it is triggered and therefore can trigger a file, such as a PHP, this PHP sends the information via websocket.

   Maleficio: PHP tends to be slow and added to the connection time you will have a query much slower, since it will only finish after the websocket is completed, or you will have to do some gambiarra to run silently, either way this will not be mentioned in EXPLAIN, you may not be able to know the reason the database is slow.

I found this publication of Pubnub, a company that offers websockets, using curl directly using the sys_eval(), but with a "warning" that this is not recommended.

2. Mysql: Check the logs.

   Do you know how Mysql replicates are synchronized (a master + X Slaves)? One of the ways is by synchronizing Mysql’s logs master, it contains everything that has been changed and you can use this to see what has changed and send information based on it. This solution is perhaps the most complex (and less clear) in my opinion, you can also use something like inotifywait to trigger when the file is updated.

3. PHP: Send after the commit.

   Imagine that you have a website that has a balance, transferable between users, you want to notify them when each receive and send, for example:

mysqli_begin_transaction($con);
mysqli_autocommit($con, false);

$detalhe = mysqli_query($con, '
   INSERT INTO `pagamento_detalhe`(`QuemPagou`, `QuemRecebeu`, `Valor`) 
     VALUES ("'.$idPagou.'", "'.$idRecebeu.'", "'.$Valor.'")
');
$detalhe = mysqli_affected_rows($con) === 1;

$atualizaSaldo = mysqli_query($con, '
   UPDATE usuario as Pagou, usuario as Recebeu 
    SET Pagou.Saldo = Pagou.Saldo - '.$Valor.', Recebeu.Saldo = Recebeu.Saldo + '.$Valor.' 
     WHERE Pagou.id = "'.$idPagou.'" AND Recebeu.id = "'.$idRecebeu.'"
');
$atualizaSaldo = mysqli_affected_rows($con) === 2;

if($atualizaSaldo && $detalhe){
    mysqli_commit($con);
}else{
    mysqli_rollback($con);
}

It would be easier to add something like this:

//...
if($atualizaSaldo && $detalhe){
    mysqli_commit($con);
    enviar_websocket($idPagou, 'Você enviou '.$Valor);
    enviar_websocket($idRecebeu, 'Você recebeu '.$Valor);
}else{
    mysqli_rollback($con);
}

Creating a function enviar_websocket will send the websocket to the id corresponding with a message you set, FOR EXAMPLE. This will be sent whenever a data update occurs, but still on the PHP and not Mysql side.

Answer 2

You need to identify who does the update, delete or Insert in your database, probably a file. php should do this, considering, some client machine requested this file, correct?

Considering that you already have a websocket server working just send()"javascript" to the socketID of the client machine that wants to receive this change.

For detail on how to build websocket server, I have one totally in php and I can help you, it is not difficult, there are several tutos that teaches to mount one as chat, just adapt to your use. see this link http://gustavobeavis.github.io/blog/2014/09/11/web-socket-php/

I based myself on it to set up an internal chat for company where I work...