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

User · 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. ;-)

User · Answer

A cronjob could monitor this log and based on events created by your trigger it could invoke a php script  That is if you absolutely have no control over you insertion   If you have transaction logs in you MySQL  you can create a trigger for purpose of a log instance creation

User · Answer

If you have transaction logs in you MySQL  you can create a trigger for purpose of a log instance creation  A cronjob could monitor this log and based on events created by your trigger it could invoke a php script  That is if you absolutely have no control over you insertion

User · Answer

That should be considered a very bad programming practice to call PHP code from a database trigger  If you will explain the task you are trying to solve using such  mad  tricks  we might provide a satisfying solution   ADDED 19 03 2014   I should have added some reasoning earlier  but only found time to do this now  Thanks to  cmc for an important remark  So  PHP triggers add the following complexities to your application    Adds a certain degree of security problems to the application  external PHP script calls  permission setup  probably SELinux setup etc  as  Johan says  Adds additional level of complexity to your application  to understand how database works you now need to know both SQL and PHP  not only SQL  and you will have to debug PHP also  not only SQL  Adds additional point of failure to your application  PHP misconfiguration for example   which needs to be diagnosied also   I think trigger needs to hold some debug code which will log somwewhere all insuccessful PHP interpreter calls and their reasons   Adds additional point of performance analysis  Each PHP call is expensive  since you need to start interpreter  compile script to bytecode  execute it etc  So each query involving this trigger will execute slower  And sometimes it will be difficult to isolate query performance problems since EXPLAIN doesn t tell you anything about query being slower because of trigger routine performance  And I m not sure how trigger time is dumped into slow query log  Adds some problems to application testing  SQL can be tested pretty easily  But to test SQL   PHP triggers  you will have to apply some skill

User · Answer

I was thinking about this exact issue for a case with long polling where I didn t want the php script to have to continually poll the db   Polling would need to be done somewhere  memory would probably be best   So if somehow the trigger could put the info into something like memcache  then php could poll that would would be much less intensive overall   Just need a method for mysql to use memcache   Perhaps into a predefined variable with a specific user id   Once the data is retrieved php could reset the var until the db sets it again   Not sure about timing issues though   Perhaps a second variable to store the previous key selected

User · Answer

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    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  We created a stored procedure that calls sys eval  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

User · Answer

I found this   http   forums mysql com read php 99 170973 257815 msg-257815  DELIMITER    CREATE TRIGGER tg1 AFTER INSERT ON  test  FOR EACH ROW BEGIN    echo  php  foo php   gt  gt   tmp yourlog txt END    DELIMITER

User · Answer

I don t know if it s possible but I always pictured myself being able to do this with the CSV storage engine in MySQL  I don t know the details of this engine   http   dev mysql com doc refman 5 7 en csv-storage-engine html but you can look into it and have a file watcher in your operating system that triggers a PHP call if the file is modified

User · Answer

Run away from store procedures as much as possible  They are pretty hard to maintain and are VERY OLD STUFF

User · Answer

In order to get a notification from the database I wrote a command line script using websocket to check for the latest updated timestamp every second  This ran as an infinite loop on the server  If there is a change all connected clients will can be sent a notification

[php] Invoking a PHP script from a MySQL trigger

Examples related to php

Examples related to mysql

Examples related to triggers