Most Popular Tutorials
Most Popular Tutorials :-

Simply Easy Learning at Your Fingertips. Click Tutorials Menu to view More Tutorial List





How to create trigger in php with mysql?

MySql Trigger Syntax :-

CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW

BEGIN

//code here

END;

After CREATE TRIGGER statement trigger name is added. Mysql trigger should be named as [trigger time]_[table name]_[trigger event]

Eg :- before_itechxpert_update

Trigger runs before update of an employee information can be named before_itechxpert_update.

Trigger is activated BEFORE or AFTER insert, update or delete of a data.

SQL statements are written between BEGIN and END block. Here we define the logic of the trigger.

Trigger cannot exist without association with a table so you have to specify table name after the ON keyword.

Table subscribers :-

Table Name : subscribers

CREATE TABLE `subscribers` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`fname` varchar(200) NOT NULL,
`email` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table audit_subscriber

Table Name : audit_subscriber

CREATE TABLE `audit_subscribers` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`subscriber_name` varchar(200) NOT NULL,
`action_performed` varchar(400) NOT NULL,
`date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Trigger to execute before a subscriber insert

Trigger Name : before_subscriber_insert

DELIMITER $$
CREATE TRIGGER `before_subscriber_insert` BEFORE INSERT ON `subscribers` FOR EACH ROW
BEGIN
INSERT INTO audit_subscribers
SET action_performed = 'A new subscriber Inserted',
subscriber_name = new.fname;
END
$$
DELIMITER ;

Note

Trigger syntax starts with DELIMITER $$. Delimiters are used in start of a mysql trigger or procedure because in SQL we use semicolon (;) in each statement as a delimiter. In a mysql trigger or procedure we have to write multiple SQL statement and each statement ends with a semicolon.
To tell MySQL that a semicolon is not the end of our trigger, we changed delimiter from semicolon to $$. So MySQL knows that our trigger is starting with $$ and ends with $$. Trigger will execute before insert of a new subscriber.
FOR EACH ROW statement states that trigger is going to be executed for each row that is effected by the event.

Trigger execute after a subscriber delete

File Name : after_subscriber_delete

DELIMITER $$
CREATE TRIGGER `after_subscriber_delete` AFTER DELETE ON `subscribers`
FOR EACH ROW
BEGIN
INSERT INTO audit_subscribers
SET action_performed = 'A subscriber Deleted',
subscriber_name = OLD.fname;
END
$$
DELIMITER ;

Above Trigger execute after a subscriber is deleted. It is executed for each row and action_performed and subscriber name is inserted into audit_subscriber table, current date is inserted in to date_added column. Here we used OLD.fname that means existing name of subscriber.

Trigger execute after a subscriber edit

Trigger Name : after_subscriber_edit

DELIMITER $$
CREATE TRIGGER `after_subscriber_edit` AFTER UPDATE ON `subscribers`
FOR EACH ROW
BEGIN
INSERT INTO audit_subscribers
SET action_performed = 'Subscriber Updated',
subscriber_name = OLD.fname;
END
$$
DELIMITER ;

Above Trigger execute after a subscriber record is updated. Action performed and subscriber_name are entered into audit_subscriber table.

Create a database connection

File Name : dbconnection.php

<?php
$host = "localhost";
$username ="ittutorial";
$password = "sana_mahtab";
$db_name = "itechxpert";

$conn = mysqli_connect($host,$username,$password)or die(mysqli_error());
mysqli_select_db($conn,$db_name)or die(mysqli_error($conn));
// $conn = mysqli_connect("localhost", "root", "", "itechxpert");

// $conn = mysqli_connect($host,$username,$password,$db_name)or die(mysqli_error());
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

#################### OR ##############################


define ( 'DB_HOST', 'localhost' );
define ( 'DB_USER', 'gmax' );
define ( 'DB_PASSWORD', 'gmaxpass' );
define ( 'DB_NAME', 'gmaxdb' );

$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('Could not connect to database server.');
mysqli_select_db($conn,DB_NAME) or die('Could not select database.');
// // $conn = mysqli_connect("localhost", "root", "", "itechxpert");

// $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD,DB_NAME) or die('Could not connect to database server.');
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>

Create HTML form for save user information into database

File Name : index.php

<?php
require('dbconnection.php');
$fname = '';
$email = '';
$error = '';
$success = '';
if(isset($_POST['sub']))
{
$fname = $_POST['fname'];
$email = $_POST['email'];
if(!$fname || !$email){
$error .= 'All fields are required. <br />';
}elseif(!strpos($email, "@" ) || !strpos($email, "."))
{
$error .= 'Email is invalid. <br />';
}
if(!$error)
{
$sql = "insert into subscribers (fname, email) values (?, ?) ";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $fname, $email);
if($stmt->execute()){
$success = 'Subscriber added successfully.';
$fname = '';
$email = '';
}else{
$error .= 'Error while saving subscriber. Try again. <br />';
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Mysql trigger Example with PHP</title>
<style>
.container {
margin: 0 auto;
padding: 10px;
}
.error {
width: 100%;
color: red;
}
.success {
width: 100%;
color: green;
}
</style>
</head>
<body>
<div class="container">
<h2>Trigger on before insert of record</h2>
<h4>Subscriber Register </h4>
<?php if($error) { ?>
<p class="error"><?php echo $error; ?></p>
<? } ?>
<?php if($success) { ?>
<p class="success"><?php echo $success; ?></p>
<? } ?>
<form name="form1" method = "post">
<p>
First name:<br>
<input type="text" placeholder='First Name' name="fname" value="<?php echo $fname; ?>" required >
</p>
<p>
Email:<br>
<input type="email" placeholder='Email' name="email" value="<?php echo $email; ?>" required >
</p>
<p>
<input type="submit" value="Register Subscriber" name='sub'>
</p>
</form>
<p>Upon clicking "Submit" button, form data is saved into subscriber table and a trigger
before_subscriber_insert will execute.</p>
</div>
</body>
</html>

Note

File Name :

when form is submit then before_subscriber_insert trigger execute first and data is inserted into audit_subscribers table. after that insertion query execute and data is inserted into subscribers Table.

How to Execute mysql trigger on delete and edit of a record

  • Display records from database on the page.
  • A php script to delete record from database table
  • After deletion of records, a mysql trigger after_subscriber_delete on subscriber?s table

    • Show Data From Database Table.

    To execute delete or edit mysql triggers you have to display records first.

    File Name : view_subscribers.php

    <?php
    require('dbconnection.php');
    $error = '';
    $success = '';
    $result_array = array();
    $sql = " select id, fname, email from subscribers ";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
    array_push($result_array, $row);
    }
    }
    ?>
    <!DOCTYPE html>
    <html>
    <head>
    <title>Trigger Example </title>
    <style>
    .container {
    margin: 0 auto;
    padding: 10px;
    }
    .error {
    width: 100%;
    color: red;
    }
    .success {
    width: 100%;
    color: green;
    }

    table {
    border-collapse: collapse;
    }
    table, th, td {
    border: 1px solid black;
    }
    </style>
    </head>
    <body>
    <div class="container">
    <h2>Subscribers View </h2>

    <h4>If you click on delete link. Record will be deleted and after delete trigger will execute.</h4>

    <h4>If you click on edit link. On update of record and after update trigger will execute.</h4>

    <?php if($error) { ?>
    <p class="error"><?php echo $error; ?></p>
    <? } ?>
    <?php if($success) { ?>
    <p class="success"><?php echo $success; ?></p>
    <? } ?>
    <table width="90%" >
    <tr>
    <th>#</th>
    <th>Name</th>
    <th>Email</th>
    <th>Action</th>
    </tr>
    <?php for($i=0; $i<count($result_array); $i++){ ?>
    <tr>
    <td><?php echo $i+1 ?></td>
    <td><?php echo $result_array[$i]['fname'] ?></td>
    <td><?php echo $result_array[$i]['email'] ?></td>
    <td><a href="subscriber_edit.php?id=<?php echo $result_array[$i]['id'] ?>" >Edit</a>
    &nbsp;|&nbsp;<a onclick="return confirm('Are you sure you want to delete this subscriber?');"
    href="subscriber_del.php?id=<?php echo $result_array[$i]['id'] ?>">Delete</a>
    </td>
    </tr>

    <?php } ?>

    </table>
    </div>
    </body>
    </html>

    Execute mysql trigger on delete of a record

    In the picture above we have displayed subscribers records. Clicking on Delete link will display a confirmation message, If user clicks OK then she is redirected to subscriber_del.php page. In this page record is deleted from database and a trigger after_subscriber_delete is triggered automatically and deleted user name.

    File Name : subscriber_del.php

    <?php
    require('dbconnection.php');
    $id = intval($_GET['id']);
    if($id){
    $sql = " delete from subscribers where id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $id);
    $stmt->execute();
    header("Location: records.php");
    exit;
    }

    Execute mysql trigger on update of a record

    fter update trigger. Records are already displayed. When a user clicks on Edit link, browser redirects to subscriber_edit.php page.

    File Name : subscriber_edit.php

    <?php
    require('dbconnection.php');
    $error = '';
    $success = '';
    $fname = '';
    $email = '';
    $error = '';
    $success = '';
    $id = intval($_GET['id']);
    if(isset($_POST['sub'])){
    $fname = $_POST['fname'];
    $email = $_POST['email'];
    if(!$fname || !$email){

    $error .= 'All fields are required. <br />';
    }elseif(!strpos($email, "@" ) || !strpos($email, ".")){
    $error .= 'Email is invalid. <br />';
    }
    if(!$error){
    //update data in database

    $sql = "update subscribers set fname = ?, email = ? where id = ? ";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('sss', $fname, $email, $id);
    if($stmt->execute()){

    $success = 'Subscriber added successfully.';
    $fname = '';
    $email = '';
    }else{
    $error .= 'Error while saving subscriber. Try again. <br />';
    }
    }
    }
    //Fetch existing record to edit
    $sql = " select id, fname, email from subscribers where id = $id";
    $result = $conn->query($sql);
    $record = $result->fetch_assoc();
    $fname = $record['fname'];
    $email = $record['email'];
    ?>
    <!DOCTYPE html>
    <html>
    <head>
    <title>Mysql trigger example with PHP</title>
    <style>
    .container {
    margin: 0 auto;
    padding: 10px;
    }
    .error {
    width: 100%;
    color: red;
    }
    .success {
    width: 100%;
    color: green;
    }
    </style>
    </head>
    <body>
    <div class="container">
    <h2>Example mysql trigger: after update of a record</h2>
    <h4>Update a subscriber information</h4>
    <?php if($error) { ?>
    <p class="error"><?php echo $error; ?></p>
    <? } ?>
    <?php if($success) { ?>
    <p class="success"><?php echo $success; ?></p>
    <? } ?>
    <form name="form1" method = "post">
    <input type="hidden" name='id' value="<?php echo $id; ?>" >
    <p>
    First name:<br>
    <input type="text" placeholder='First Name' name="fname" value="<?php echo $fname; ?>" required >
    </p>
    <p>
    Email:<br>
    <input type="email" placeholder='Email' name="email" value="<?php echo $email; ?>" required >
    </p>
    <p>
    <input type="submit" value="Update" name='sub'>
    </p>
    </form>
    <p>Upon clicking "Update" button, form data is updated into subscriber table, a mysql trigger named
    after_subscriber_update will execute.</p>
    </div>
    </body>
    </html>





    Previous Next


    Trending Tutorials


    PHP

    PHP

    100+ Tutorials
    oops

    PHP OOPs

    80+ Tutorial
    Codeigniter

    Codeigniter

    100+ Tutorial
    Laravel

    Laravel

    100+ Tutorials
    Node.js

    NODE.JS

    100+ Tutorials
    Javascript

    Javascript

    100+ Tutorials


    Review & Rating

    0.0 / 5

    0 Review

    5
    (0)

    4
    (0)

    3
    (0)

    2
    (0)

    1
    (0)

    Write Review Here


    Most Popular Tutorials

     Important Link