MySQL Events in PHP

Introduction

This article describes how to create MySQL events using PHP. So if we talk about "events", the general term or words that to minds are "anything that takes place or happens". So the MySQL event, or scheduled event, is a named block of code that executes, or fires, according to the event scheduler. By default the event scheduler is off. In simple words, an event is similar to a trigger. However, rather than running in response to a data change, events can be scheduled to run any number of times during a specific period.

Syntax of CREATE EVENT statement

The first CREATE EVENT statement is creates a one time event

CREATE EVENT event_name ON SCHEDULE {at timestamp | every interval [starts timestamp] [end timestamp] } DO sql_block

The following procedure describes how to create an event.

Step 1

First create a table, on which an event will be fire (if required, otherwise you can use an existing table).

The following code creates a table with the name of "ForEvent" in a MySQL database.

Code

<?php
$con=mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
$sql = "CREATE TABLE ForEvent (EventTime DATETIME );"; // Query for creating a table
mysql_query($sql);
echo "Table has been created.";
mysql_close($con);
?>

Output




Step 2

After Step 1 create an event

In the following query:

CREATE EVENT 'testevent' ON SCHEDULE EVERY 1 MINUTE STARTS '2013-03-06 16:26:22' DO INSERT INTO 'ForEvent' VALUES (NOW())

the first CREATE EVENT statement creates an event with the named 'testevent', and the DO keyword is used to identify the statements and which event
should execute. That query is used in the  following code that creates an event with the name of "testevent".

Code
 

<?php

$con=mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("mysql", $con);

$query = "CREATE EVENT testevent

    ON SCHEDULE EVERY 1 MINUTE

    STARTS '2013-03-06 16:26:22'

     DO

     INSERT INTO `ForEvent` VALUES (NOW());";

mysql_query($query);

echo "Event has been created.";

mysql_close($con);

?>

Output

 

Step 3

After the completion of Step 2, using the following query I try to fetch data from the table "ForEvent", but the "ForEvent" table is empty , because the user has not inserted any

value in it, but an event with the name of "testevent" is created to insert value in it at a scheduled time. But it is not working because the event shedular is not "ON".

Code

<?php

$con=mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("mysql", $con);

$qry=mysql_query("select * from ForEvent");

while($row = mysql_fetch_array($qry))

{

echo $row['EventTime'];

echo "<br/>";

}

echo "The <b>ForEvent</b> table is empty </br>beacause the user do not insert any value in it, </br>but a event with the name of <b>testevent</b> is created to insert value in it at scheduled time</br> But it is not working because the enent scheduler is not on";

mysql_close($con);

?>

Output

 

Step 4

To resolve the problem you must run the command "SET GLOBAL event_scheduler = 'ON';".

Step 5

After the completion of step 4 again, I try to fetch table "ForEvent" data using the following code:

Code

<?php

$con=mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("mysql", $con);

print "<h2>SElect Data from table ForEvent After event scheduler on</h2>";

$qry=mysql_query("select * from ForEvent");

while($row = mysql_fetch_array($qry))

{

echo $row['EventTime'];

echo "<br/>";

}

mysql_close($con);

?>

Output



Summary

An event, or scheduled event, is a named block of code that executes, or fires according to the event scheduler and before you begin working with events, you need to be sure that the event scheduler is on. By default, it is off.