{"id":285,"date":"2018-07-13T07:27:28","date_gmt":"2018-07-13T07:27:28","guid":{"rendered":"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=285"},"modified":"2018-12-07T11:50:22","modified_gmt":"2018-12-07T11:50:22","slug":"integrating-php-and-mysql","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/chapter\/integrating-php-and-mysql\/","title":{"rendered":"Integrating PHP and MySQL"},"content":{"raw":"
\"epgp<\/a>\r\n<\/span><\/div>\r\n
\r\n\r\n \r\n\r\n \r\n\r\n \r\n\r\nPHP<\/strong>\r\n\r\n \r\n

PHP (recursive acronym* ,Hypertext Preprocessor) is an open source scripting language which is used for web development and can be embedded into HTML. It is widely used open source language for developing web applications. Any text editor like notepad or notepad++ can be used to write the scripts for PHP. Various Frameworks are also available for managing PHP code, which we will cover at a later stage.<\/p>\r\n \r\n

*PHP- Recursive acronym (Hypertext Preprocessor ) (Originally \u201cPersonal home page tools\u201d)<\/p>\r\nXAMP \u2013 XAMP APACHE MARIADB PHP PERl\r\n\r\n \r\n

Note <\/strong>: There are other variants like Lamp for Linux , Wamp for Windows , MAMP for MAC.We will be using XAMP<\/strong><\/p>\r\n \r\n

Before you start running your PHP scripts, you need to start some of the Services like Apache , MySQL etc.<\/p>\r\n \r\n\r\n\"\"\r\n\r\n \r\n\r\nXAMP<\/strong>\r\n\r\n \r\n

You can also edit the XAMPP setting from my.ini , which is as follows<\/p>\r\n\r\n<\/div>\r\n\"\"\r\n

\r\n\r\n \r\n\r\nBut , if you have MySQL workbench installed , services might not start, the way they should.\r\n\r\n \r\n\r\nTroubleshooting Services :\r\n\r\n \r\n\r\nXAMP & MySQL<\/strong>\r\n\r\n \r\n\r\nYou can edit the settings in my.ini as follows: We have changed the port number to 3307\r\n\r\n<\/div>\r\n\"\"\r\n
\r\n\r\n \r\n\r\nServices (services.msc)<\/strong>\r\n\r\n \r\n\r\n\"\"\r\n\r\nPHPMyadmin<\/strong>\r\n\r\n \r\n

PhpMyAdmin is Open Source administration tool for MySQL and MariaDB. It is written in PHP and supports wide range of operations like managing tables , columns , indexes , user<\/p>\r\n\r\n<\/div>\r\n\"\"\r\n

\r\n

\u00a0 \u00a0 \u00a0 permissions etc.<\/p>\r\n \r\n

Data objects in PHP<\/strong><\/p>\r\n \r\n

\u2022 There are different ways of accessing Database in PHP<\/p>\r\n

\u2022 PDO : Lightweight consistent interface for accessing databases in PHP<\/p>\r\n

\u2022 SDO : Sequence Data objects (for unstructured data)<\/p>\r\n

\u2022 Various functions for manipulating Databases like , mysql_connect , mysqli_connect etc.<\/p>\r\n \r\n\r\n\"\"\r\n\r\n \r\n\r\nDB connectivity testing<\/strong>\r\n\r\n \r\n\r\n<?php\r\n\r\n$dbname = 'test';\r\n\r\n$dbuser = 'root';\r\n\r\n$dbpass = 'Your Password';\r\n\r\n$dbhost = 'localhost';<\/span>\r\n\r\n\/\/$connect = mysql_connect($dbhost, $dbuser, $dbpass) or die(\"Unable to Connect to '$dbhost'\");<\/span>\r\n\r\n$connect = mysqli_connect ($dbhost, $dbuser, $dbpass) or die (\"could not connect to '$dbhost'\");<\/span>\r\n\r\nif (!$connect)<\/span>\r\n\r\n{<\/span>\r\n\r\ndie (\"connection failed \". mysqli_connect_error());<\/span>\r\n\r\n}<\/span>\r\n\r\necho \"connected successfully\";<\/span>\r\n\r\n?><\/span>\r\n\r\n<\/div>\r\n

\r\n

Please note that the commented lines of mysql_connect might not be supported by some data editors now. Hence , we have used , mysqli_connect.<\/p>\r\n \r\n\r\nYou can test the above script by running into your browser.\r\n\r\nNow , let us start with calling the stored procedures, which we created in MySQL into our PHP script\r\n\r\n \r\n\r\nStored Procedure:<\/strong>\r\n\r\n \r\n

\u2022 A stored procedure or a function is a named Pl\/SQL block which resides in the Database engine\u2019s tables. A stored procedure can be invoked by other procedure, triggers or by other applications like PHP, java etc.<\/p>\r\n

\u2022 It is a logically grouped set of SQL and Pl\/SQL statements which are written to perform a specific task.<\/p>\r\n

\u2022 Procedures and functions are also referred to as Database objects and such objects can be invoked or called by any Pl\/SQL block within the application.<\/p>\r\n \r\n\r\nCalling Stored procedure in PHP<\/strong>\r\n\r\n \r\n\r\n<html>\r\n\r\n<head>\r\n\r\n<title> GEt salary <\/title>\r\n\r\n<link rel=\"stylesheet\" href=\"css\/table.css\" type=\"text\/css\" \/>\r\n\r\n<\/head><\/span>\r\n\r\n<body><\/span>\r\n\r\n<?php<\/span>\r\n\r\n\/\/require_once 'dbconfig.php';<\/span>\r\n\r\n$host = 'localhost';<\/span>\r\n\r\n$dbname = 'test';<\/span>\r\n\r\n$username = 'root';<\/span>\r\n\r\n$password = 'Your Password';<\/span>\r\n\r\ntry {<\/span>\r\n\r\n$pdo = new PDO(\"mysql:host=$host;dbname=$dbname\", $username, $password);<\/span>\r\n\r\n\/\/\u00a0 execute the stored procedure $sql = 'CALL Get_sal()';<\/span>\r\n\r\n\/\/\u00a0 call the stored procedure<\/span>\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 \u00a0 $q = $pdo\u00a0\u00a0\u00a0 query($sql);\r\n\r\n$q->setFetchMode(PDO::FETCH_ASSOC);\r\n\r\n} catch (PDOException $e) {\r\n\r\ndie(\"Error occurred:\" . $e->getMessage());\r\n\r\n}\r\n\r\n?>\r\n\r\n<table>\r\n\r\n<tr>\r\n\r\n<th>Employee Name<\/th>\r\n\r\n<th>Salary<\/th>\r\n\r\n<\/tr>\r\n\r\n<?php while ($r = $q->fetch()): ?>\r\n\r\n<tr>\r\n\r\n<td><?php echo $r['name'] ?><\/td>\r\n\r\n<td><?php echo '\u20b9' . number_format($r['salary'], 2) ?> <\/td>\r\n\r\n<\/div>\r\n
\r\n\r\n\u00a0 \u00a0<\/tr>\r\n\r\n<?php endwhile; ?>\r\n\r\n<\/table>\r\n\r\n<\/body>\r\n\r\n<\/html>\r\n\r\n \r\n\r\nNote: To insert Rupee symbol, you can use ALT+8377 or ALT+4 on your keyboard\r\n\r\nAfter running your program in the browser, you get the following output:\r\n\r\n \r\n\r\n\"\"\r\n\r\n \r\n

In the previous example ,we had used PDO and hence here we have used mysql_iconnect, so you can have clarity for both and use whichever you are comfortable with.<\/p>\r\n \r\n\r\nTriggers<\/strong>\r\n\r\n \r\n

\u2022 Database Triggers are the database objects which reside in system catalog. The triggers are special type of procedures which can be called implicitly.<\/p>\r\n

\u2022 Each trigger is associated with a table which can be activated on any DML statement like (Insert , update or delete).<\/p>\r\n

To view the effect of Triggers created , you need to issue any DML statements in order to see the corresponding effect.<\/p>\r\n \r\n\r\nSo, let us have a program where we fire Update statement and see the Triggers effect.\r\n\r\n<\/div>\r\n \r\n\r\nUpdateSalary.PHP<\/strong>\r\n\r\n \r\n

\r\n\r\n\u00a0 \u00a0 <html>\r\n\r\n<head>\r\n\r\n<title>Trigger effect with Update EMP<\/strong><\/title>\r\n\r\n<\/head>\r\n\r\n<body>\r\n\r\n<?php\r\n\r\nif(isset($_POST['update'])) {\r\n\r\n$host = 'localhost';\r\n\r\n$dbname = 'test';\r\n\r\n$username = 'root';\r\n\r\n$password = 'Your Password';\r\n\r\n$conn = mysqli_connect($host, $username, $password, $dbname); if(! $conn ) {\r\n\r\ndie('Could not connect: ' . mysqli_error($conn));\r\n\r\n}\r\n\r\n$empid = $_POST['empid'];\r\n\r\n$salary = $_POST['salary'];\r\n\r\n$sql = \"UPDATE emp \". \"SET salary = $salary \".\r\n\r\n\"WHERE empid = $empid\" ;\r\n\r\n$retval = mysqli_query($conn, $sql);\r\n\r\nif(! $retval ) {\r\n\r\ndie('Could not update data: ' . mysqli_error($conn));<\/span>\r\n\r\n}<\/span>\r\n\r\necho \"Updated data successfully\\n\";<\/span>\r\n\r\nmysqli_close($conn);<\/span>\r\n\r\n}else {<\/span>\r\n\r\n?><\/span>\r\n\r\n<\/div>\r\n
\r\n\r\n\u00a0 \u00a0<form method = \"post\" action = \"<?php $_PHP_SELF ?>\"> <table width = \"400\" border =\" 0\" cellspacing = \"1\"\r\n\r\ncellpadding = \"2\">\r\n\r\n<tr>\r\n\r\n<td width = \"100\">Employee ID<\/td>\r\n\r\n<td><input name = \"empid\" type = \"text\"\r\n\r\nid = \"empid\"><\/td>\r\n\r\n<\/tr>\r\n\r\n<tr>\r\n\r\n<td width = \"100\">Employee Salary<\/td>\r\n\r\n<td><input name = \"salary\" type = \"text\"\r\n\r\nid = \"salary\"><\/td>\r\n\r\n<\/tr>\r\n\r\n<tr>\r\n\r\n<td width = \"100\"> <\/td>\r\n\r\n<td> <\/td>\r\n\r\n<\/tr>\r\n\r\n<\/div>\r\n
\r\n\r\n\u00a0 \u00a0<tr>\r\n\r\n<\/div>\r\n
\r\n\r\n\u00a0 \u00a0 <td width = \"100\"> <\/td>\r\n\r\n<td>\r\n\r\n<input name = \"update\" type = \"submit\"\r\n\r\nid = \"update\" value = \"Update\">\r\n\r\n<\/td>\r\n\r\n<\/tr>\r\n\r\n<\/table>\r\n\r\n<\/form>\r\n\r\n<?php\r\n\r\n}\r\n\r\n?>\r\n\r\n<\/body>\r\n\r\n<\/html>\r\n\r\n \r\n\r\nAfter writing the program , execute in the browser , accept values from the user.\r\n\r\n \r\n\r\n\"\"\r\n\r\n \r\n

Here, we are accepting the amount and id to be updated and then you can go and check into the tables the trigger effects.<\/p>\r\n\r\n<\/div>\r\n

\r\n\r\n \r\n\r\nWe saw the above program with Mysqli_connect.\r\n\r\n \r\n\r\nNow , let us also have an example with PDO\r\n\r\n \r\n\r\nUpdateSalary_pdo.PHP<\/strong>\r\n\r\n \r\n\r\n<html>\r\n\r\n<head>\r\n\r\n<title>Trigger effect with Update EMp PDO<\/title> <\/head>\r\n\r\n<body>\r\n\r\n<form method = \"post\" action = \"<?php $_PHP_SELF ?>\"> <table width = \"400\" border =\" 0\" cellspacing = \"1\"\r\n\r\ncellpadding = \"2\">\r\n\r\n<tr>\r\n\r\n<td width = \"100\">Employee ID<\/td>\r\n\r\n<td><input name = \"empid\" type = \"text\"\r\n\r\nid = \"empid\" value=\"\"><\/td>\r\n\r\n<\/tr>\r\n\r\n<tr>\r\n\r\n<td width = \"100\">Employee Salary<\/td>\r\n\r\n<td><input name = \"salary\" type = \"text\"\r\n\r\nid = \"salary\" value=\"\"><\/td>\r\n\r\n<\/tr>\r\n\r\n<tr>\r\n\r\n<td width = \"100\"> <\/td><\/span>\r\n\r\n<\/div>\r\n
\r\n\r\n\u00a0 \u00a0 <td> <\/td>\r\n\r\n<\/tr>\r\n\r\n<tr>\r\n\r\n<td width = \"100\"> <\/td>\r\n\r\n<td>\r\n\r\n<input name = \"update\" type = \"submit\"\r\n\r\nid = \"update\" value = \"Update\">\r\n\r\n<\/td>\r\n\r\n<\/tr>\r\n\r\n<\/table>\r\n\r\n<\/form>\r\n\r\n \r\n\r\n<?php\r\n\r\n$host = 'localhost';\r\n\r\n$dbname = 'test';\r\n\r\n$username = 'root';\r\n\r\n$password = '';\r\n\r\nif (isset ($_POST[\"update\"])){\r\n\r\n$empid = $_POST['empid'];\r\n\r\n$salary = $_POST['salary'];\r\n\r\ntry {\r\n\r\n$connection = new\r\n\r\nPDO(\"mysql:host=$host;dbname=$dbname\",$username,$password);\r\n\r\n$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);\r\n\r\n$sql = \"UPDATE emp SET salary=:salary WHERE empid=:empid\";\r\n\r\n$statement = $connection->prepare($sql);<\/span>\r\n\r\n<\/div>\r\n$statement->bindValue(\":empid\", $empid);\r\n\r\n$statement->bindValue(\":salary\", $salary);\r\n\r\n$count = $statement->execute();\r\n\r\n$connection = null;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Disconnect\r\n\r\n}\r\n\r\ncatch(PDOException $e) {\r\n\r\necho $e->getMessage();\r\n\r\n}\r\n\r\n \r\n\r\n}\r\n\r\n?>\r\n\r\n \r\n\r\nYou can run the above program in the browser and notice the effects.\r\n\r\nYou can also add messages like Number of rows affected etc.\r\n\r\n\r\n\r\n
you can view video on Integrating PHP and MySQL<\/strong><\/td>\r\n\"\"<\/a><\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\nAdditional Reading:<\/strong>\r\n\r\n \r\n

1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers<\/p>\r\n

2) MySQL documentation : dev.mysql.com\/doc\/<\/p>\r\n

3) PHP and MySQL Web Development, \u201cLuke Welling, Laura Thomson\u201d,Pearson Publications.<\/p>","rendered":"

\"epgp<\/a>
\n<\/span><\/div>\n
\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

PHP<\/strong><\/p>\n

 <\/p>\n

PHP (recursive acronym* ,Hypertext Preprocessor) is an open source scripting language which is used for web development and can be embedded into HTML. It is widely used open source language for developing web applications. Any text editor like notepad or notepad++ can be used to write the scripts for PHP. Various Frameworks are also available for managing PHP code, which we will cover at a later stage.<\/p>\n

 <\/p>\n

*PHP- Recursive acronym (Hypertext Preprocessor ) (Originally \u201cPersonal home page tools\u201d)<\/p>\n

XAMP \u2013 XAMP APACHE MARIADB PHP PERl<\/p>\n

 <\/p>\n

Note <\/strong>: There are other variants like Lamp for Linux , Wamp for Windows , MAMP for MAC.We will be using XAMP<\/strong><\/p>\n

 <\/p>\n

Before you start running your PHP scripts, you need to start some of the Services like Apache , MySQL etc.<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

XAMP<\/strong><\/p>\n

 <\/p>\n

You can also edit the XAMPP setting from my.ini , which is as follows<\/p>\n<\/div>\n

\"\"<\/p>\n

\n

 <\/p>\n

But , if you have MySQL workbench installed , services might not start, the way they should.<\/p>\n

 <\/p>\n

Troubleshooting Services :<\/p>\n

 <\/p>\n

XAMP & MySQL<\/strong><\/p>\n

 <\/p>\n

You can edit the settings in my.ini as follows: We have changed the port number to 3307<\/p>\n<\/div>\n

\"\"<\/p>\n

\n

 <\/p>\n

Services (services.msc)<\/strong><\/p>\n

 <\/p>\n

\"\"<\/p>\n

PHPMyadmin<\/strong><\/p>\n

 <\/p>\n

PhpMyAdmin is Open Source administration tool for MySQL and MariaDB. It is written in PHP and supports wide range of operations like managing tables , columns , indexes , user<\/p>\n<\/div>\n

\"\"<\/p>\n

\n

\u00a0 \u00a0 \u00a0 permissions etc.<\/p>\n

 <\/p>\n

Data objects in PHP<\/strong><\/p>\n

 <\/p>\n

\u2022 There are different ways of accessing Database in PHP<\/p>\n

\u2022 PDO : Lightweight consistent interface for accessing databases in PHP<\/p>\n

\u2022 SDO : Sequence Data objects (for unstructured data)<\/p>\n

\u2022 Various functions for manipulating Databases like , mysql_connect , mysqli_connect etc.<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

DB connectivity testing<\/strong><\/p>\n

 <\/p>\n

<?php<\/p>\n

$dbname = ‘test’;<\/p>\n

$dbuser = ‘root’;<\/p>\n

$dbpass = ‘Your Password’;<\/p>\n

$dbhost = ‘localhost’;<\/span><\/p>\n

\/\/$connect = mysql_connect($dbhost, $dbuser, $dbpass) or die(“Unable to Connect to ‘$dbhost'”);<\/span><\/p>\n

$connect = mysqli_connect ($dbhost, $dbuser, $dbpass) or die (“could not connect to ‘$dbhost'”);<\/span><\/p>\n

if (!$connect)<\/span><\/p>\n

{<\/span><\/p>\n

die (“connection failed “. mysqli_connect_error());<\/span><\/p>\n

}<\/span><\/p>\n

echo “connected successfully”;<\/span><\/p>\n

?><\/span><\/p>\n<\/div>\n

\n

Please note that the commented lines of mysql_connect might not be supported by some data editors now. Hence , we have used , mysqli_connect.<\/p>\n

 <\/p>\n

You can test the above script by running into your browser.<\/p>\n

Now , let us start with calling the stored procedures, which we created in MySQL into our PHP script<\/p>\n

 <\/p>\n

Stored Procedure:<\/strong><\/p>\n

 <\/p>\n

\u2022 A stored procedure or a function is a named Pl\/SQL block which resides in the Database engine\u2019s tables. A stored procedure can be invoked by other procedure, triggers or by other applications like PHP, java etc.<\/p>\n

\u2022 It is a logically grouped set of SQL and Pl\/SQL statements which are written to perform a specific task.<\/p>\n

\u2022 Procedures and functions are also referred to as Database objects and such objects can be invoked or called by any Pl\/SQL block within the application.<\/p>\n

 <\/p>\n

Calling Stored procedure in PHP<\/strong><\/p>\n

 <\/p>\n

<html><\/p>\n

<head><\/p>\n

<title> GEt salary <\/title><\/p>\n

<link rel=”stylesheet” href=”css\/table.css” type=”text\/css” \/><\/p>\n

<\/head><\/span><\/p>\n

<body><\/span><\/p>\n

<?php<\/span><\/p>\n

\/\/require_once ‘dbconfig.php’;<\/span><\/p>\n

$host = ‘localhost’;<\/span><\/p>\n

$dbname = ‘test’;<\/span><\/p>\n

$username = ‘root’;<\/span><\/p>\n

$password = ‘Your Password’;<\/span><\/p>\n

try {<\/span><\/p>\n

$pdo = new PDO(“mysql:host=$host;dbname=$dbname”, $username, $password);<\/span><\/p>\n

\/\/\u00a0 execute the stored procedure $sql = ‘CALL Get_sal()’;<\/span><\/p>\n

\/\/\u00a0 call the stored procedure<\/span><\/p>\n<\/div>\n

\n

\u00a0 \u00a0 $q = $pdo\u00a0\u00a0\u00a0 query($sql);<\/p>\n

$q->setFetchMode(PDO::FETCH_ASSOC);<\/p>\n

} catch (PDOException $e) {<\/p>\n

die(“Error occurred:” . $e->getMessage());<\/p>\n

}<\/p>\n

?><\/p>\n

<table><\/p>\n

<tr><\/p>\n

<th>Employee Name<\/th><\/p>\n

<th>Salary<\/th><\/p>\n

<\/tr><\/p>\n

<?php while ($r = $q->fetch()): ?><\/p>\n

<tr><\/p>\n

<td><?php echo $r[‘name’] ?><\/td><\/p>\n

<td><?php echo ‘\u20b9’ . number_format($r[‘salary’], 2) ?> <\/td><\/p>\n<\/div>\n

\n

\u00a0 \u00a0<\/tr><\/p>\n

<?php endwhile; ?><\/p>\n

<\/table><\/p>\n

<\/body><\/p>\n

<\/html><\/p>\n

 <\/p>\n

Note: To insert Rupee symbol, you can use ALT+8377 or ALT+4 on your keyboard<\/p>\n

After running your program in the browser, you get the following output:<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

In the previous example ,we had used PDO and hence here we have used mysql_iconnect, so you can have clarity for both and use whichever you are comfortable with.<\/p>\n

 <\/p>\n

Triggers<\/strong><\/p>\n

 <\/p>\n

\u2022 Database Triggers are the database objects which reside in system catalog. The triggers are special type of procedures which can be called implicitly.<\/p>\n

\u2022 Each trigger is associated with a table which can be activated on any DML statement like (Insert , update or delete).<\/p>\n

To view the effect of Triggers created , you need to issue any DML statements in order to see the corresponding effect.<\/p>\n

 <\/p>\n

So, let us have a program where we fire Update statement and see the Triggers effect.<\/p>\n<\/div>\n

 <\/p>\n

UpdateSalary.PHP<\/strong><\/p>\n

 <\/p>\n

\n

\u00a0 \u00a0 <html><\/p>\n

<head><\/p>\n

<title>Trigger effect with Update EMP<\/strong><\/title><\/p>\n

<\/head><\/p>\n

<body><\/p>\n

<?php<\/p>\n

if(isset($_POST[‘update’])) {<\/p>\n

$host = ‘localhost’;<\/p>\n

$dbname = ‘test’;<\/p>\n

$username = ‘root’;<\/p>\n

$password = ‘Your Password’;<\/p>\n

$conn = mysqli_connect($host, $username, $password, $dbname); if(! $conn ) {<\/p>\n

die(‘Could not connect: ‘ . mysqli_error($conn));<\/p>\n

}<\/p>\n

$empid = $_POST[’empid’];<\/p>\n

$salary = $_POST[‘salary’];<\/p>\n

$sql = “UPDATE emp “. “SET salary = $salary “.<\/p>\n

“WHERE empid = $empid” ;<\/p>\n

$retval = mysqli_query($conn, $sql);<\/p>\n

if(! $retval ) {<\/p>\n

die(‘Could not update data: ‘ . mysqli_error($conn));<\/span><\/p>\n

}<\/span><\/p>\n

echo “Updated data successfully\\n”;<\/span><\/p>\n

mysqli_close($conn);<\/span><\/p>\n

}else {<\/span><\/p>\n

?><\/span><\/p>\n<\/div>\n

\n

\u00a0 \u00a0<form method = “post” action = “<?php $_PHP_SELF ?>”> <table width = “400” border =” 0″ cellspacing = “1”<\/p>\n

cellpadding = “2”><\/p>\n

<tr><\/p>\n

<td width = “100”>Employee ID<\/td><\/p>\n

<td><input name = “empid” type = “text”<\/p>\n

id = “empid”><\/td><\/p>\n

<\/tr><\/p>\n

<tr><\/p>\n

<td width = “100”>Employee Salary<\/td><\/p>\n

<td><input name = “salary” type = “text”<\/p>\n

id = “salary”><\/td><\/p>\n

<\/tr><\/p>\n

<tr><\/p>\n

<td width = “100”> <\/td><\/p>\n

<td> <\/td><\/p>\n

<\/tr><\/p>\n<\/div>\n

\n

\u00a0 \u00a0<tr><\/p>\n<\/div>\n

\n

\u00a0 \u00a0 <td width = “100”> <\/td><\/p>\n

<td><\/p>\n

<input name = “update” type = “submit”<\/p>\n

id = “update” value = “Update”><\/p>\n

<\/td><\/p>\n

<\/tr><\/p>\n

<\/table><\/p>\n

<\/form><\/p>\n

<?php<\/p>\n

}<\/p>\n

?><\/p>\n

<\/body><\/p>\n

<\/html><\/p>\n

 <\/p>\n

After writing the program , execute in the browser , accept values from the user.<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

Here, we are accepting the amount and id to be updated and then you can go and check into the tables the trigger effects.<\/p>\n<\/div>\n

\n

 <\/p>\n

We saw the above program with Mysqli_connect.<\/p>\n

 <\/p>\n

Now , let us also have an example with PDO<\/p>\n

 <\/p>\n

UpdateSalary_pdo.PHP<\/strong><\/p>\n

 <\/p>\n

<html><\/p>\n

<head><\/p>\n

<title>Trigger effect with Update EMp PDO<\/title> <\/head><\/p>\n

<body><\/p>\n

<form method = “post” action = “<?php $_PHP_SELF ?>”> <table width = “400” border =” 0″ cellspacing = “1”<\/p>\n

cellpadding = “2”><\/p>\n

<tr><\/p>\n

<td width = “100”>Employee ID<\/td><\/p>\n

<td><input name = “empid” type = “text”<\/p>\n

id = “empid” value=””><\/td><\/p>\n

<\/tr><\/p>\n

<tr><\/p>\n

<td width = “100”>Employee Salary<\/td><\/p>\n

<td><input name = “salary” type = “text”<\/p>\n

id = “salary” value=””><\/td><\/p>\n

<\/tr><\/p>\n

<tr><\/p>\n

<td width = “100”> <\/td><\/span><\/p>\n<\/div>\n

\n

\u00a0 \u00a0 <td> <\/td><\/p>\n

<\/tr><\/p>\n

<tr><\/p>\n

<td width = “100”> <\/td><\/p>\n

<td><\/p>\n

<input name = “update” type = “submit”<\/p>\n

id = “update” value = “Update”><\/p>\n

<\/td><\/p>\n

<\/tr><\/p>\n

<\/table><\/p>\n

<\/form><\/p>\n

 <\/p>\n

<?php<\/p>\n

$host = ‘localhost’;<\/p>\n

$dbname = ‘test’;<\/p>\n

$username = ‘root’;<\/p>\n

$password = ”;<\/p>\n

if (isset ($_POST[“update”])){<\/p>\n

$empid = $_POST[’empid’];<\/p>\n

$salary = $_POST[‘salary’];<\/p>\n

try {<\/p>\n

$connection = new<\/p>\n

PDO(“mysql:host=$host;dbname=$dbname”,$username,$password);<\/p>\n

$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);<\/p>\n

$sql = “UPDATE emp SET salary=:salary WHERE empid=:empid”;<\/p>\n

$statement = $connection->prepare($sql);<\/span><\/p>\n<\/div>\n

$statement->bindValue(“:empid”, $empid);<\/p>\n

$statement->bindValue(“:salary”, $salary);<\/p>\n

$count = $statement->execute();<\/p>\n

$connection = null;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Disconnect<\/p>\n

}<\/p>\n

catch(PDOException $e) {<\/p>\n

echo $e->getMessage();<\/p>\n

}<\/p>\n

 <\/p>\n

}<\/p>\n

?><\/p>\n

 <\/p>\n

You can run the above program in the browser and notice the effects.<\/p>\n

You can also add messages like Number of rows affected etc.<\/p>\n\n\n\n
you can view video on Integrating PHP and MySQL<\/strong><\/td>\n\"\"<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Additional Reading:<\/strong><\/p>\n

 <\/p>\n

1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers<\/p>\n

2) MySQL documentation : dev.mysql.com\/doc\/<\/p>\n

3) PHP and MySQL Web Development, \u201cLuke Welling, Laura Thomson\u201d,Pearson Publications.<\/p>\n","protected":false},"author":3,"menu_order":30,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["miss-bhumika-shah"],"pb_section_license":""},"chapter-type":[],"contributor":[60],"license":[],"class_list":["post-285","chapter","type-chapter","status-publish","hentry","contributor-miss-bhumika-shah"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/285","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/users\/3"}],"version-history":[{"count":14,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/285\/revisions"}],"predecessor-version":[{"id":519,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/285\/revisions\/519"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/285\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/media?parent=285"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapter-type?post=285"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/contributor?post=285"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/license?post=285"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}