30 Integrating PHP and MySQL

Bhumika Shah

PHP

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.

*PHP- Recursive acronym (Hypertext Preprocessor ) (Originally “Personal home page tools”)

XAMP – XAMP APACHE MARIADB PHP PERl

Note : There are other variants like Lamp for Linux , Wamp for Windows , MAMP for MAC.We will be using XAMP

Before you start running your PHP scripts, you need to start some of the Services like Apache , MySQL etc.

XAMP

You can also edit the XAMPP setting from my.ini , which is as follows

But , if you have MySQL workbench installed , services might not start, the way they should.

Troubleshooting Services :

XAMP & MySQL

You can edit the settings in my.ini as follows: We have changed the port number to 3307

Services (services.msc)

PHPMyadmin

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

permissions etc.

Data objects in PHP

• There are different ways of accessing Database in PHP

• PDO : Lightweight consistent interface for accessing databases in PHP

• SDO : Sequence Data objects (for unstructured data)

• Various functions for manipulating Databases like , mysql_connect , mysqli_connect etc.

DB connectivity testing

<?php

$dbname = ‘test’;

$dbuser = ‘root’;

$dbpass = ‘Your Password’;

$dbhost = ‘localhost’;

//$connect = mysql_connect($dbhost, $dbuser, $dbpass) or die(“Unable to Connect to ‘$dbhost'”);

$connect = mysqli_connect ($dbhost, $dbuser, $dbpass) or die (“could not connect to ‘$dbhost'”);

if (!$connect)

{

die (“connection failed “. mysqli_connect_error());

}

echo “connected successfully”;

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

You can test the above script by running into your browser.

Now , let us start with calling the stored procedures, which we created in MySQL into our PHP script

Stored Procedure:

• A stored procedure or a function is a named Pl/SQL block which resides in the Database engine’s tables. A stored procedure can be invoked by other procedure, triggers or by other applications like PHP, java etc.

• It is a logically grouped set of SQL and Pl/SQL statements which are written to perform a specific task.

• 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.

Calling Stored procedure in PHP

<html>

<head>

<title> GEt salary </title>

</head>

<body>

<?php

//require_once ‘dbconfig.php’;

$host = ‘localhost’;

$dbname = ‘test’;

$username = ‘root’;

$password = ‘Your Password’;

try {

$pdo = new PDO(“mysql:host=$host;dbname=$dbname”, $username, $password);

// execute the stored procedure $sql = ‘CALL Get_sal()’;

// call the stored procedure

$q = $pdo query($sql);

$q->setFetchMode(PDO::FETCH_ASSOC);

} catch (PDOException $e) {

die(“Error occurred:” . $e->getMessage());

}

<table>

<tr>

<th>Employee Name</th>

<th>Salary</th>

</tr>

<?php while ($r = $q->fetch()): ?>

<tr>

</tr>

<?php endwhile; ?>

</table>

</body>

</html>

Note: To insert Rupee symbol, you can use ALT+8377 or ALT+4 on your keyboard

After running your program in the browser, you get the following output:

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.

Triggers

• Database Triggers are the database objects which reside in system catalog. The triggers are special type of procedures which can be called implicitly.

• Each trigger is associated with a table which can be activated on any DML statement like (Insert , update or delete).

To view the effect of Triggers created , you need to issue any DML statements in order to see the corresponding effect.

So, let us have a program where we fire Update statement and see the Triggers effect.

UpdateSalary.PHP

<html>

<head>

<title>Trigger effect with Update EMP</title>

</head>

<body>

<?php

if(isset($_POST[‘update’])) {

$host = ‘localhost’;

$dbname = ‘test’;

$username = ‘root’;

$password = ‘Your Password’;

$conn = mysqli_connect($host, $username, $password, $dbname); if(! $conn ) {

die(‘Could not connect: ‘ . mysqli_error($conn));

}

$empid = $_POST[’empid’];

$salary = $_POST[‘salary’];

$sql = “UPDATE emp “. “SET salary = $salary “.

“WHERE empid = $empid” ;

$retval = mysqli_query($conn, $sql);

if(! $retval ) {

die(‘Could not update data: ‘ . mysqli_error($conn));

}

echo “Updated data successfully\n”;

mysqli_close($conn);

}else {

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

cellpadding = “2”>

<tr>

<td width = “100”>Employee ID</td>

<td><input name = “empid” type = “text”

id = “empid”></td>

</tr>

<tr>

<td width = “100”>Employee Salary</td>

<td><input name = “salary” type = “text”

id = “salary”></td>

</tr>

<tr>

</tr>

<tr>

<td>

<input name = “update” type = “submit”

id = “update” value = “Update”>

</td>

</tr>

</table>

</form>

<?php

}

</body>

</html>

After writing the program , execute in the browser , accept values from the user.

Here, we are accepting the amount and id to be updated and then you can go and check into the tables the trigger effects.

We saw the above program with Mysqli_connect.

Now , let us also have an example with PDO

UpdateSalary_pdo.PHP

<html>

<head>

<title>Trigger effect with Update EMp PDO</title> </head>

<body>

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

cellpadding = “2”>

<tr>

<td width = “100”>Employee ID</td>

<td><input name = “empid” type = “text”

id = “empid” value=””></td>

</tr>

<tr>

<td width = “100”>Employee Salary</td>

<td><input name = “salary” type = “text”

id = “salary” value=””></td>

</tr>

<tr>

</tr>

<tr>

<td>

<input name = “update” type = “submit”

id = “update” value = “Update”>

</td>

</tr>

</table>

</form>

<?php

$host = ‘localhost’;

$dbname = ‘test’;

$username = ‘root’;

$password = ”;

if (isset ($_POST[“update”])){

$empid = $_POST[’empid’];

$salary = $_POST[‘salary’];

try {

$connection = new

PDO(“mysql:host=$host;dbname=$dbname”,$username,$password);

$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = “UPDATE emp SET salary=:salary WHERE empid=:empid”;

$statement = $connection->prepare($sql);

$statement->bindValue(“:empid”, $empid);

$statement->bindValue(“:salary”, $salary);

$count = $statement->execute();

$connection = null; // Disconnect

}

catch(PDOException $e) {

echo $e->getMessage();

}

You can run the above program in the browser and notice the effects.

You can also add messages like Number of rows affected etc.

you can view video on Integrating PHP and MySQL

Additional Reading:

1) MySQL 5 for professionals , Ivan Bayross , Sharanam Shah, Shroff Publishers

2) MySQL documentation : dev.mysql.com/doc/

3) PHP and MySQL Web Development, “Luke Welling, Laura Thomson”,Pearson Publications.