CODEWITHSUNDEEP
phpmysqlstored-proceduresmysql-event
Channel Dunia
Channel Dunia

Reputation: 1

Stored procedure execution in background?

how to run mysql stored procedure behind the scenes without creating a loading website to wait for the results of the procedure to finish?

DELIMITER $$

USE `simbakda_sensus`$$

DROP PROCEDURE IF EXISTS `info`$$

CREATE DEFINER=`potutu`@`%` PROCEDURE `info`(unit VARCHAR(20))
BEGIN   
    DELETE FROM info WHERE kd_unit=unit;
    INSERT INTO info
    SELECT unit,'a' AS kib ,SUM(a) AS a_sen,SUM(b) AS a_tot,(SUM(b)-SUM(a)) AS a_non FROM (
    SELECT unit,COUNT(*) AS a,'0' AS b FROM trkib_a WHERE kd_unit=unit AND STATUS='1'
    UNION ALL
    SELECT unit,'0' AS a, COUNT(*) AS b FROM trkib_a WHERE kd_unit=unit
    )a
    UNION ALL
    SELECT unit,'b' AS kib ,SUM(a) AS a_sen,SUM(b) AS a_tot,(SUM(b)-SUM(a)) AS a_non FROM (
    SELECT unit,COUNT(*) AS a,'0' AS b FROM trkib_b WHERE kd_unit=unit AND STATUS='1'
    UNION ALL
    SELECT unit,'0' AS a, COUNT(*) AS b FROM trkib_b WHERE kd_unit=unit
    )b
    UNION ALL
    SELECT unit,'c' AS kib ,SUM(a) AS a_sen,SUM(b) AS a_tot,(SUM(b)-SUM(a)) AS a_non FROM (
    SELECT unit,COUNT(*) AS a,'0' AS b FROM trkib_c WHERE kd_unit=unit AND STATUS='1'
    UNION ALL
    SELECT unit,'0' AS a, COUNT(*) AS b FROM trkib_c WHERE kd_unit=unit
    )c
    UNION ALL
    SELECT unit,'d' AS kib ,SUM(a) AS a_sen,SUM(b) AS a_tot,(SUM(b)-SUM(a)) AS a_non FROM (
    SELECT unit,COUNT(*) AS a,'0' AS b FROM trkib_d WHERE kd_unit=unit AND STATUS='1'
    UNION ALL
    SELECT unit,'0' AS a, COUNT(*) AS b FROM trkib_d WHERE kd_unit=unit
    )d
    UNION ALL
    SELECT unit,'e' AS kib ,SUM(a) AS a_sen,SUM(b) AS a_tot,(SUM(b)-SUM(a)) AS a_non FROM (
    SELECT unit,COUNT(*) AS a,'0' AS b FROM trkib_e WHERE kd_unit=unit AND STATUS='1'
    UNION ALL
    SELECT unit,'0' AS a, COUNT(*) AS b FROM trkib_e WHERE kd_unit=unit
    )e
    UNION ALL
    SELECT unit,'f' AS kib ,SUM(a) AS a_sen,SUM(b) AS a_tot,(SUM(b)-SUM(a)) AS a_non FROM (
    SELECT unit,COUNT(*) AS a,'0' AS b FROM trkib_f WHERE kd_unit=unit AND STATUS='1'
    UNION ALL
    SELECT unit,'0' AS a, COUNT(*) AS b FROM trkib_f WHERE kd_unit=unit
    )f;
END$$

DELIMITER ;

Upvotes: 0

Views: 2902

Answers (1)

O. Jones
O. Jones

Reputation: 108676

You have a few good choices.

If you need to run this on a regular schedule (for example every hour or each Sunday at 03:00 or whatever) use a MySQL Event. (Notice that some of those US$5 per month shared hosting services don't allow Events on their MySQL servers.)

Enable the event scheduler:

SET GLOBAL event_scheduler = ON;

Then create the event object. It will live in your MySQL database and run on the schedule you gave it.

CREATE EVENT myevent_01
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO CALL simbakda_sensus.info;

Another choice is a one-time event. Same deal but it runs once and vanishes. You must recreate the event each time you want to run your procedure. This is a pure "background" execution of your procedure.

CREATE EVENT run_info_now
ON SCHEDULE AT CURRENT_TIMESTAMP
DO CALL simbakda_sensus.info;

A third way:

Create a small php page that runs your procedure. Then put an iframe in your main web page, and invoke the small page from it. You can make the small page display "Info done!" or some other message when your sp finishes running.

A fourth way.

Some linux / FreeBSD / Unix servers allow users to set up regularly scheduled events called cron jobs. Big topic: read about it here. How to create cron job using PHP?

Pro tip: Create a log table. Make each data-maintenance procedure insert a row into the table when it runs, showing the current time and other information about the procedure. I promise you, if your system gets a lot of use, you will look at this log once in a while to make sure things are running normally.

Upvotes: 2

Related Questions