Skip to main content

[PHP] Create MySQL Trigger in PHP

phpmysql
IntroductionWhen we want to execute a specific task at a scheduled time, MySQL provides special functionality called triggers. Triggers are basically named blocks of code that are executed, or fired, automatically when a specified type of SQL statement is executed. Triggers can be executed before after an INSERT, UPDATE or DELETE statement is executed on the table.

Syntax



CREATE TRIGGER trigger_Name
{Before|After}{Insert|Update|Delete}ON table_name
For  EACH  ROW
sql_block

For ExampleUsing the following query, you can better understand what a trigger is and how to use a trigger to execute a specific task at a scheduled time.

QueryThe following query creates a  trigger named "MysqlTrigger", this trigger is associated with the name emp table and is fired before an update. When you try to update a name with lower case, it will automatically convert it to UPPER case .
"CREATE TRIGGER MysqlTrigger BEFORE UPDATE ON "emp" FOR EACH ROW SET NEW.name=UPPER(NEW.name);"

The following is an Update statement that fires the trigger:

UPDATE emp SET name='ram' where id=2


The following is a select statement that shows the new row:

Select * from emp where id=2





Example of Creating a trigger in PHP



In this example a simple "CREATE TRIGGER MysqlTrigger BEFORE UPDATE ON emp FOR EACH ROW SET NEW.name=UPPER(NEW.name);" query creates a trigger that will be useful when for updating an employee's name based on their id (in other words emp id). Suppose you write the query "UPDATE emp SET name='ram' where id=2", when this query fires, the name corresponding to id 2 is updated. Suppose that before the update, the name corresponding to id 2 is "vinod", then this query changes "vinod" to "ram". In other words, the trigger that was previously created by you, changes "ram" to "RAM".

<?php

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

if (!$con)

{

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

}

mysql_select_db("sharad", $con);

print "<h2>MySQL: Simple Select statement</h2>";

$result = mysql_query("select * from emp");

echo "<table border='1'>

<tr>

<th>EmpId</th>

<th>Firstname</th>

<th>Salary</th>

</tr>";

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td>" . $row['id'] . "</td>";

echo "<td>" . $row['name'] . "</td>";

echo "<td>" . $row['salary'] . "</td>";

echo "</tr>";

}

echo "</table>";

print "<h2>CREATE MySQL Trigger In PHP</h2>";

$sql = "CREATE TRIGGER MysqlTrigger BEFORE UPDATE ON emp FOR EACH ROW SET NEW.name=UPPER(NEW.name);";

mysql_query($sql,$con);

print "<h2>MySQL: Update Statement</h2>";

$qry = mysql_query("UPDATE emp SET name='ram' where id=2");

echo "Table has been updated.";

mysql_query($qry,$con);

print "<h2>MySQL: Effect of Trigger</h2>";

<?php

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

if (!$con)

{

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

}$result = mysql_query("select * from emp");

echo "<table border='1'>

<tr>

<th>EmpId</th>

<th>Firstname</th>

<th>Salary</th>

<?php

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

if (!$con)

{

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

}

mysql_select_db("sharad", $con);

print "<h2>MySQL: Simple Select statement</h2>";

$result = mysql_query("select * from emp");

echo "<table border='1'>

<tr>

<th>EmpId</th>

<th>Firstname</th>

<th>Salary</th>

</tr>";

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td>" . $row['id'] . "</td>";

echo "<td>" . $row['name'] . "</td>";

echo "<td>" . $row['salary'] . "</td>";

echo "</tr>";

}

echo "</table>";

print "<h2>CREATE MySQL Trigger In PHP</h2>";

$sql = "CREATE TRIGGER MysqlTrigger BEFORE UPDATE ON emp FOR EACH ROW SET NEW.name=UPPER(NEW.name);";
</tr>";

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td>" . $row['id'] . "</td>";

echo "<td>" . $row['name'] . "</td>";

echo "<td>" . $row['salary'] . "</td>";

echo "</tr>";

}

echo "</table>";

m<?php

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

if (!$con)

{

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

}ysql_close($con);



<?php

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

if (!$con)

{

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

}?>Note: You can not create a trigger on "system tables".

Following one also better example . make trigger before insert qurey


$con=mysql_connect("localhost", "root", "");
$connect=mysql_select_db("ci",$con);
if(isset($connect))
{
    echo 'successfully connected';
  
   
<?php

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

if (!$con)

{

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

}
$
<?php

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

if (!$con)

{

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

}
s
<?php
<?php

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

if (!$con)

{

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

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

if (!$con)

{

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

}
ql="CREATE TRIGGER captial BEFORE INSERT ON details FOR EACH ROW SET NEW.lastname=upper(NEW.lastname)";
    mysql_query($sql);
  
  
    $sql1="INSERT INTO details(lastname) VALUES('desai')";
    mysql_query($sql1);
      
}  


Popular posts from this blog

Draw pyramid shape of stars in PHP using loop structure Write down the code in PHP using loop structure to draw the 3 pyramid shapes of stars as given bellow:

ravimanephpmysql

// problem 01 solution
for($i=5;$i>=1;$i--)
{
echo  str_repeat('*',$i);
echo "<br />";
}

echo "<hr />";

// problem 02 solution

for($i=1;$i<=5;$i++)
{
echo  str_repeat('*',$i);
echo "<br />";
}


echo "<hr />";
// problem 03 solution

$j=1;

for($i=5;$i>=1;$i--)
{
//echo  str_repeat("&nbsp;",$i-1);
echo  str_repeat('*',$j++);
echo "<br />";
}

Google reCAPTCHA with PHP tutorial

Login to your Google account and submit the form. Once submit, Google will provide you following two information. Site keySecret key

Integrate it into your website. To integrate it into your website you need to put it in client side as well as in Server side. In client HTML page you need to integrate this line before <HEAD> tag.
<script src='https://www.google.com/recaptcha/api.js'></script> And to show the widget into your form you need to put this below contact form, comment form etc.
<divclass="g-recaptcha" data-sitekey="== Your site Key =="></div> When the form get submit to Server, this script will send ‘g-recaptcha-response’ as a POST data. You need to verify it in order to see whether user has checked the Captcha or not. Simple comment form with Google reCAPTCH:Index.html <html>
<head>
<title>Google recapcha demo - Codeforgeek</title>
<scriptsrc='https://www.google.com/recaptcha/api.js'&g…

Simple Laravel CRUD with Resource Controllers

Creating, reading, updating, and deleting resources is used in pretty much every application. Laravel helps make the process easy using resource controllers. Resource Controllers can make life much easier and takes advantage of some cool Laravel routing techniques. Today, we'll go through the steps necessary to get a fully functioning CRUD application using resource controllers. For this tutorial, we will go through the process of having an admin panel to create, read, update, and delete (CRUD) a resource. Let's use nerds as our example. We will also make use of Eloquent ORM. This tutorial will walk us through: * Setting up the database and models * Creating the resource controller and its routes * Creating the necessary views * Explaining each method in a resource controller Table of ContentsGetting our Database ReadyEloquent Model for the NerdsCreating the ControllerSetting Up the RoutesThe ViewsMaking It All Work TogetherConclusion To get started, we will need the controll…