How to make a PHP script react in real time to a database insertion?

Question

I have multiple PHP scripts running 24/7, each on different machines, that fetch information about users, from the Internet.

These users are in a table in a (postgresql) database. Every time a new user registers in the app, a new record is inserted in the table, which assigns it to one of the currently running PHP scripts (distributing the load evenly).

Is there a way so that the PHP script it has been assigned to can react in real time, in order to begin fetching information about the user immediately?

(The current setup I have is that each PHP script queries the table every 1 min, and checks if new users have been assigned to it... but more frequent queries increase database load, and in that 1 min window I am not fetching information from the newly added user.)

The short answer, "No." The long answer, start looking at Node.js. — jjwdesign, Mar 14 '16 at 16:36
I'm also interested in a possible solution for `MySQL`, in case there is a super-easy way to accomplish this. — dazedviper, Mar 14 '16 at 16:43
LISTEN / NOTIFY in five minutes http://www.pomm-project.org/news/using-postgresql-notify-in-5-minutes.html — greg, Mar 14 '16 at 21:47

score 1 · Accepted Answer · answered Mar 14 '16 at 16:40

1

Check this example of PHP code for postgres LISTEN and NOTIFY: http://php.net/manual/en/function.pg-get-notify.php

Postgres man on LISTEN/NOTIFY: http://www.postgresql.org/docs/current/static/sql-notify.html http://www.postgresql.org/docs/current/static/sql-listen.html

answered Mar 14 '16 at 16:40

Ihor Romanchenko

26,995
8
48
44

But I'd still have to call `pg_get_notify()` every 1 min to see if new notifications have been issued, wouldn't I? I assume this is more lightweight than querying a table and that I can increase the poll frequency? – dazedviper Mar 14 '16 at 16:45
@hallaplay835 No, you don't need to call it every minute. It will block the script execution until it receives a notification. If you don't want to get the script blocked then there are workarounds which require polling a socket. Anyway, just google for the examples. `LISTEN/NOTIFY` is definitely a way to go for PostgreSQL real time events. – pumbo Mar 15 '16 at 04:20

score 0 · Answer 2 · edited May 23 '17 at 10:28

0

Trigger an HTTP request from a sys_exec() launch from an SQL trigger ? But it's seems overkill.

DELIMITER $$

CREATE TRIGGER `test_after_insert` AFTER INSERT ON `test`
FOR EACH ROW BEGIN

SET @exec_var = sys_exec('/usr/bin/php /myApp/after_insert.php')
END;
$$

DELIMITER ;

I know it's not a postgres script but it should work.

--- EDIT ---

And after in after_insert.php :

function httpPost($url, $data)
{
    $curl = curl_init($url);
    curl_setopt($curl, CURLOPT_POST, true);
    curl_setopt($curl, CURLOPT_POSTFIELDS, http_build_query($data));
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($curl);
    curl_close($curl);
    return $response;
}

sources : How to make an HTTP Post

edited May 23 '17 at 10:28

Community

1
1

answered Mar 14 '16 at 16:40

Thomas Leduc

1,092
1
20
44

This would launch `after_insert.php`... which would issue an HTTP request? So the HTTP request is sent to the machine where the originally running PHP script is... and how would it be alerted? – dazedviper Mar 14 '16 at 16:52
Hum, yes why not ? SQL Trigger -> sys_exec -> after_insert.php -> http post to your remote script. I will update my answer – Thomas Leduc Mar 14 '16 at 17:09
My PHP script `A.php` runs on a machine where there is not a web server. In case I installed one, the request would be handled by another PHP script `B.php` that would have to communicate somehow with `A`. How? [Inter-process communication](http://php.net/manual/en/function.stream-socket-pair.php)? Maybe a [tick handler](http://php.net/manual/en/control-structures.declare.php)? Are you sure this is more efficient than querying the table continuously? – dazedviper Mar 14 '16 at 17:41
I would recommend to avoid using such solutions which depend on execution of external programs. It is not safe, not efficient, not portable and not cool! Make use of `LISTEN/NOTIFY` feature instead. – pumbo Mar 15 '16 at 04:24
I think it's the most efficient @pumbo, maybe not cool, but efficient, portable and safe. Safer than an open endpoint pointing on a select query. Why don't install a web server ? You just add an entry in your `iptables` on the `A` server receiving the query from the ip of the `A` one and an `nginx` exposing your `B.php` script. Maybe I don't really understand the problem ... There is no IPC or tick handler to use ... – Thomas Leduc Mar 15 '16 at 09:09

How to make a PHP script react in real time to a database insertion?

2 Answers2