Invoking a PHP script from a MySQL trigger


Question

Is there any way how to invoke a PHP page / function when a record is inserted to a MySQL database table? We don't have control over the record insertion procedure. Is there a trigger mechanism which can call a PHP script back?

1
58
5/19/2016 3:26:44 PM

Accepted Answer

The trigger is executed on the MySQL server, not on the PHP one (even if those are both on the same machine).

So, I would say this is not quite possible -- at least not simply.


Still, considering this entry from the MySQL FAQ on Triggers :

23.5.11: Can triggers call an external application through a UDF?

Yes. For example, a trigger could invoke the sys_exec() UDF available here: https://github.com/mysqludf/lib_mysqludf_sys#readme

So, there might be a way via an UDF function that would launch the php executable/script. Not that easy, but seems possible. ;-)

31
8/16/2016 2:55:54 PM

A friend and I have figured out how to call Bernardo Damele's sys_eval UDF, but the solution isn't as elegant as I'd like. Here's what we did:

  1. Since we're using Windows, we had to compile the UDF library for Windows using Roland Bouman's instructions and install them on our MySQL server.
  2. We created a stored procedure that calls sys_eval.
  3. We created a trigger that calls the stored procedure.

Stored Procedure code:

DELIMITER $$
CREATE PROCEDURE udfwrapper_sp
(p1   DOUBLE,
 p2   DOUBLE,
 p3 BIGINT)
BEGIN
 DECLARE cmd CHAR(255);
 DECLARE result CHAR(255);
 SET cmd = CONCAT('C:/xampp/php/php.exe -f "C:/xampp/htdocs/phpFile.php" ', p1, ' ', p2, ' ', p3);
 SET result = sys_eval(cmd);
END$$;

Trigger code:

CREATE TRIGGER udfwrapper_trigger AFTER INSERT ON sometable
FOR EACH ROW
CALL udfwrapper_sp(NEW.Column1, NEW.Column2, NEW.Column3);

I'm not thrilled about having the stored procedure, and I don't know if it creates extra overhead, but it does work. Each time a row is added to sometable, the trigger fires.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon