PHP instruction executed directly by Mysql

Question

Currently, as the sending of the email was giving a lag in the application, because it has to connect with Gmail etc, I transferred to the server the mission to send these messages, as follows::

• User writes to tbl_msg the message and the addressee.
• On the server I have an open page that is giving Reload every 10 seconds.
• This page searches the database for the tbl_msg, when it finds it, it executes a PHP code that sends the email.

This way the application for the user does not suffer any lag.

What I want

It is possible for the bank itself to do something that if any data is recorded in the tbl_msg Mysql automatically runs the PHP statement, without having to have an open page?

Answer 1

There is how you perform external functions in Mysql, but it is a tedious and complicated maintenance job.

It’s a lot easier for you for an entrance crontab or in the task scheduler run the desired PHP every 10 minutes.

Linux

In most distros *Nix the crontab line would look like this, to run every 10 minutes:

5,15,25,35,45,55 * * * * /usr/bin/php meuscript.php > /var/log/meuscript.log

Some distros support this syntax:

*/10 * * * * /usr/bin/php meuscript.php > /var/log/meuscript.log

Windows

Just schedule a task on scheduler with these features. If you want, you can redirect to a log and facilitate the detection of errors and debug.

c:/caminhoparaoseu/php.exe c:/caminho/para/arquivo.php

Independent of OS, make sure to use the right paths for things in your system.

Making a loop with PHP

For short intervals of time, you can run a PHP script with a loop infinite at system startup:

<?php
   set_time_limit( 0 ); // para o PHP poder rodar sem limite de tempo

   while( true ) {
       ... aqui vai a sua função do DB ...

       sleep( 10 ); // numero de segundos entre um loop e outro
   }
?>

Do not access this script from the browser! Use the command line not to unnecessarily occupy a web server process. Also, the directive max_execution_time has default value 0 by the command line, allowing the loop rotate indefinitely.

Answer 2

From what I’ve seen, it’s possible, but it’s not simple...

You need to import the udf library in the MYSQL to use a function called sys_exec.

Then you could create a Trigger so that whenever there was a new record, the function sys_exec was called, and on that call execute a command on the system, which in turn execute php, passing as parameter the script responsible for data search and e-mail sending.

I never used it, but I saw it in this question in English. What will validate whether or not it works is your programming skills.