You have several ways.
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.
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.
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.
Any personal tips?
– Murilo Souza
Too wide your question. First of all, already know how to use websocket? If you do not know, better start there, because the part of the comic comes later.
– Bacco
The reply of @Inkeliz seems to me to present in number 3 the best option. Do you have any specific reason for sending messages from the websocket at the database level and not at the application level? Because it seems to me counterproductive, especially in mysql that is not so concerned with real-time. Maybe using Redis or Mongodb are better options in this case, but it will depend on your needs.
– Daniel