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

Question

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

Navigation

#1 by (5 votes)
#2 by (0 votes)

9

I want to update a div only when there is a change in a certain field in the database, I am currently using polling, but I want to switch to websocket for performance issues, but I only find examples of chats, and it's not what I need.
Does anyone have a clue how to check changes in bd with websocket?

php mysql javascript websocket pooling

asked by anonymous 29.03.2017 / 17:55

2 answers

How to pass a number in scientific notation in Java? Web security api: SSL?

score 5 · Answer 1

You have many 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.

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

I found this PubNub , a company that offers websockets , which uses curl directly using sys_eval() , but with a "warning" that this is not recommended.

MySQL: Check the logs.

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

PHP: Send after commit .

Imagine that you have a website that has a balance, transferable between users, you want to notify them when they 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 corresponding id with a message you define, EG. This will be sent whenever a data update occurs, but still on the PHP side rather than MySQL.

score 0 · Answer 2

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

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

For detail on how to mount websocket server, I have a totally in php and I can help you, it is not difficult, there are several tutos that teaches to mount a like chat, just adapt to its use. see this link link

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