25 PHP and MySQL

Hiren Joshi

 

Objectives

 

• Ways to connect PHP and MySQL

• Useful methods of PDO

• PHP and MySQL connectivity

 

Web Database Architecture

 

Web Application is a client/server architecture. In typical web application client/server architecture consist of two objects: 1. A web browser and 2. A web server. Web Browser and Web Server communicate via a communication link – generally internet or through http protocol. A web browser send request to web server. The Web server send response to the web server. This architecture is used for a server sends static web pages. which Following figure shows the simple client/server architecture.

 

 

The typical web database application consists of following stages.

 

1.Using browser, user send request to webserver using HTTP. For example, by entering city name, the user wants to get all the vendor details who belongs to city. By entering city name in HTML form and click submit button, he sends request to webserver. The search result page is fetch.php

2. The web server – Apache – receives the request and retrieve the file fetch.php. The webserver passes the file to PHP engine for processing.

3. The PHP engine starts parsing the script. Inside the script, there is a command to connect to the database and execute the query. For example, here the query is to search vendor details based on their city name. PHP opens a connection to MySQL server and send query to MySQL server.

4. MySQL server receives the query, process it and send the result back to the PHP engine

5. The PHP engine get the query result, finishes the script and formatting the query result in HTML. Then returns the resulting HTML to web server.

6. The web server sends HTML back to the browser.

   The php engine and database server can run on the same machine or different machine.

 

Three ways to connect PHP and MySQL

  • An Application Programming Interface (API) is used to communicate an application with other applications. MySQL provides an API which specifies how PHP can work with MySQL.
  • PHP provides 3 extensions to work with MySQL API.
  1. MySQL extension
  2. MySQLi extension
  3. PDO MySQL driver extension
  • PDO (PHP Data Objects) is a database abstraction layer. It is used specifically for PHP applications. To communicate with MySQL, It uses PDO MySQL driver extension. PDO can use other drivers extension to communicate with other databases.
  • Each extension has its own pros and cons.

 

PDO (PHP Data Objects) Pros

  • Available for PHP 5.0 and later
  • Object Oriented Interface
  • Consistent Interface which is portable between many database servers such as Oracle, DB2, MS SQL Server and Postgre SQL
  • Use features available MySQL 4.1.3 and later

     PDO Cons

  • Does not work PHP 4.X or earlier
  • Not use advances features of MySQL 4.1.3 and later such as multiple statements

 

MySQLi (MySQL improved extension) Pros

  • Included with PHP 5.X or later
  • Provides Object-Oriented and Procedure Oriented Interface
  • use all advances features of MySQL 4.1.3 and later

    MySQLi Cons

  • Cannot used with other database servers

    MySQL (MySQL extension) pros

 

•  Available for PHP 3.X, 4.X and 5.X

 

MySQL cons

  • Not use advances features of MySQL 4.1.3 and later such as multiple statements
  • Not under active development

PDO is used in this module for demonstration.

  • To use PDO object for making connection with MySQL, you must create a PDO object with three arguments. The three arguments are – DSN, User Name and Password.
  • The general syntax is:

new PDO($dsn, $username, $password)

  • DSN is short form of Data Source Name. The DSN specifies host name and database name. If MySQL database runs on the same server as PHP, then host name is localhost. The syntax of DSN for MySQL database is

 

mysql:host=hostaddress;dbname=databasename

 

or you can set the values in a variable.

 

$dsn = ‘mysql:host=localhost;dbname=account’

  • To call a method of the object, type object name, followed by object access operator – code as -> – and followed by method name with arguments required for method in parentheses. Multiple arguments are separated by comma. The general syntax is

objectName->methodName(Argument1 [, Argument2 ..])

  • To execute the select statement of SQL, query method of PDO object is used. The query method takes one parameter.

 

Method Description
query($select)

 

 

 Execute the $select statement and returns a PDO statement object. The PDO

statement object contains the result set of $select. If no result set is returned,

then it returns false value.

 

  • The general syntax to use query method is  $PDOobject->query($select)
  • PDOStatement class has many methods. Below table describe some methods of the PDOStatement Class
Method Description
fetch( )

 

 Returns an array having result set for next row. Array can be index by

string and number. String is used for column name and number for column
position. If no array is available , it returns false.
fetchall( ) Returns an array having all the rows of the result set.
rowcount( ) Returns the number of rows affected by the executing of last statement.
closeCursor( )

 

 Close the cursor and frees the connection to the server so other SQL

statement can be issued.
  • The following code shows one example using the query and fetch method. There is a GUI which ask user to enter city name and click on submit button. The output is displayed all the details of vendor who lives in the city entered by user.
  • The HTML code for GUI is shown below.

<html>

<body>

<form action=”fetch.php” method=”post”> Enter City Name:

<input type=”text” name=”city”> <br><br>

<input type=”submit” name=”submit” value=”Submit”> <input type=”reset” name=”reset” value=”Reset”>              </form>

</body>

</html>

  • The output of HTML code is shown below:

 

  • The PHP Code for file fetch.php is shown below

 

<?php

$dsn = ‘mysql:host=localhost;dbname=account’; $uname = ‘root’;

$pwd = ”;

$db = new PDO($dsn,$uname,$pwd); $vcity = $_POST[‘city’];

$query = “select * from vendor

where vcity = ‘$vcity'”;

echo “<br> Query : $query <br>”;

$result = $db->query($query);

$row = $result->fetch();

while($row != null)

{

$vid = $row[‘vid’];

$vname = $row[‘vname’];

$vaddress = $row[‘vaddress’];

$vcity = $row[‘vcity’];

$vmobile = $row[‘vmobile’];

echo “<br>”;

echo “<br> Vendor ID : $vid”;

echo “<br> Vendor Name: $vname”;

echo “<br> Vendor Address: $vaddress”;

echo “<br> Vendor City : $vcity”;

echo “<br> Vendor Mobile : $vmobile”;

$row = $result->fetch();

}

$result->closeCursor();

?>

  • The output is given below:
  • Query : select * from vendor where vcity = ‘Rajkot’

    Vendor ID : 1

Vendor Name: Anand

Vendor Address: Ravi Park

Vendor City : Rajkot

Vendor Mobile : 9876034521

Vendor ID : 2

Vendor Name: Dharmesh

Vendor Address: 19, Park Avenue

Vendor City : Rajkot

Vendor Mobile : 1234567890

Vendor ID : 7

Vendor Name: Manish

Vendor Address: University Road

Vendor City : Rajkot

Vendor Mobile : 9087654321

  • In the above code, fetch() method is used. The same code be written using fetchall() method. Following code shows HTML and PHP code of the above program using fetchall() method.
  • HTML code

<html>

<body>

<form action=”fetchall.php” method=”post”>

Enter City Name:

<input type=”text” name=”city”>

<br><br>

<input type=”submit” name=”submit” value=”Submit”>

<input type=”reset” name=”reset” value=”Reset”>

</form>

</body>

</html>

  • PHP Code

<?php

$dsn = ‘mysql:host=localhost;dbname=account’; $uname = ‘root’;

$pwd = ”;

$db = new PDO($dsn,$uname,$pwd); $vcity = $_POST[‘city’];

$query = “select * from vendor where vcity = ‘$vcity'”;

//echo “<br> Query : $query <br>”;

$result = $db->query($query);

$rows = $result->fetchall();

foreach($rows as $row)

{

$vid = $row[‘vid’];

$vname = $row[‘vname’];

$vaddress = $row[‘vaddress’];

$vcity = $row[‘vcity’];

$vmobile = $row[‘vmobile’];

echo “<br>”;

echo “<br> Vendor ID : $vid”;

echo “<br> Vendor Name: $vname”;

echo “<br> Vendor Address: $vaddress”;

echo “<br> Vendor City : $vcity”;

echo “<br> Vendor Mobile : $vmobile”;

}

$result->closeCursor();

?>

  • To execute insert, update or delete statement exec method of PDO object is used. Exec method takes insert, update or delete SQL statement as argument.
Method Description
exec($SQLSelect)

 

 Execute the $select and returns a number indicate how many rows are

affected. If no rows are affected, then return zero.
  • For example, to insert a new record in vendor table first create GUI. Following code shows

 

<html>

<body>

<form action=’exec.php’ method=’post’> <table>

<!– vid –> <tr>

<td>Vendor ID:</td>

<td> <input type=’text’ name=’vid’> </td>

</tr>

<!– vname –>

<tr>

<td>Vendor Name:</td>

<td> <input type=’text’ name=’vname’> </td>

</tr>

<!– vaddress  –>

<tr>

<td>Vendor Address:</td>

<td> <input type=’text’ name=’vaddress’> </td>

</tr>

<!– vcity –>

<tr>

<td> Vendor City: </td>

<td> <input type=’text’ name=’vcity’> </td>

</tr>

<!– vmobile –>

<tr>

<td> Vendor Mobile: </td>

<td> <input type=’text’ name=’vmobile’> </td>

</tr>

<!– submit and reset –>

<tr>

<td> <input type=’submit’ name=’submit’ value=’Submit’> </td> <td> <input type=’reset’ name=’reset’ value=’Reset’> </td> </tr>

</table>

</form>

</body>

</html>

The output of the HTML code is :

 

 

  • The PHP code for file exec.php is given below.

   <?php

$dsn = ‘mysql:host=localhost;dbname=account’; $uname = ‘root’;

$pwd = ”;

$db = new PDO($dsn,$uname,$pwd);

$vid = $_POST[‘vid’];

$vname = $_POST[‘vname’];

$vaddress = $_POST[‘vaddress’]; $vcity = $_POST[‘vcity’]; $vmobile = $_POST[‘vmobile’];

$query = “insert into vendor

values($vid,’$vname’,’$vaddress’,’$vcity’,’$vmobile’) “;

echo “<br> Query : $query <br>”; $result = $db->exec($query);

echo “<br> Result is : $result”; if($result > 0)

{

echo “<br> Record Insrted Successfully”;

}

?>

  • The output is

   Query : insert into vendor values(22,’Kalpesh’,’Bopal’,’Ahmedabad’,’9753124680′)

Result is : 1

Record Insrted Successfully

  • For live application development, prepare method is used. Prepare method is a method of PDO object. Prepare statement improves database performance and security.
  • When a statement is executed multiple times, the statements allow the database server to reuse of the work which is done when prepare a statement.
  • Prepare statement also improves security because it can prevent most types of the SQL injection attacks.

Let’s take one example using prepare statement. The HTML code is shown below

 

<html>

<body>

<form action=’prepareselect.php’ method=’post’> Enter city name :

<input type=”text” name=”vcity”> </input>

<br><br>

<input type=’submit’ name=’submit’ value=’Submit’> <input type=’reset’ name=’reset’ value=’Reset’> </form>

</body>

</html>

   The PHP code for the file prepareselect.php is shown below.

 

<?php

$dsn=’mysql:host=localhost;dbname=account’;

$uname = ‘root’;

$pwd = ”;

$db = new PDO($dsn,$uname,$pwd); $vcity = $_POST[‘vcity’];

$query = “select * from vendor

where vcity = :vcity”;

$result = $db->prepare($query);

$result->bindValue(‘:vcity’,$vcity); $result->execute();

$rows = $result->fetchAll();

foreach($rows as $r)

{

echo ‘<br> Vendor ID :’. $r[‘vid’];

echo ‘<br> Vendor Name :’. $r[‘vname’];

echo ‘<br> Vendor Address :’. $r[‘vaddress’];

echo ‘<br> Vendor City :’. $r[‘vcity’];

echo ‘<br> Vendor Mobile :’. $r[‘vmobile’];

echo “<br>”;

}

$result->closeCursor();

?>

 

As the user clicks on submit button, he/she will get the output as shown below.

    Vendor ID :1

Vendor Name :Anand

Vendor Address :Ravi Park

Vendor City :Rajkot

Vendor Mobile :9876034521

 

Vendor ID :2

Vendor Name :Dharmesh

Vendor Address :19, Park Avenue

Vendor City :Rajkot

Vendor Mobile :1234567890

 

Vendor ID :7

Vendor Name :Manish

Vendor Address :University Road

Vendor City :Rajkot

Vendor Mobile :9087654321

 

In the similar way, prepare and bindValue method can be used to insert,update or delete statement. Following code shows to insert a record using prpare and bindValue.

 

<html>

<body>

<form action=’prepareinsert.php’ method=’post’>

<table>

<!– vid –> <tr>

<td>Vendor ID:</td>

<td> <input type=’text’ name=’vid’> </td> </tr>

<!– vname –> <tr>

<td>Vendor Name:</td>

<td> <input type=’text’ name=’vname’> </td> </tr>

<!– vaddress  –>

<tr>

<td>Vendor Address:</td>

<td> <input type=’text’ name=’vaddress’> </td>

</tr>

<!– vcity –>

<tr>

<td> Vendor City: </td>

<td> <input type=’text’ name=’vcity’> </td>

</tr>

<!– vmobile –>

<tr>

<td> Vendor Mobile: </td>

<td> <input type=’text’ name=’vmobile’> </td>

</tr>

<!– submit and reset –>

<tr>

<td> <input type=’submit’ name=’submit’ value=’Submit’> </td> <td> <input type=’reset’ name=’reset’ value=’Reset’> </td> </tr>

</table>

</form>

</body>

</html>

 

 

The code for prepareinsert.php is shown below.

 

 <?php

$dsn=’mysql:host=localhost;dbname=account’;

$uname = ‘root’;

$pwd = ”;

$db = new PDO($dsn,$uname,$pwd);

$vid = $_POST[‘vid’];

$vname = $_POST[‘vname’];

$vaddress = $_POST[‘vaddress’];

$vcity = $_POST[‘vcity’];

$vmobile = $_POST[‘vmobile’];

$query = “insert into vendor values (:vid, :vname, :vaddress, :vcity, :vmobile)”;

$result = $db->prepare($query);

$result->bindValue(‘:vid’,$vid);

$result->bindValue(‘:vname’,$vname);

$result->bindValue(‘:vaddress’,$vaddress);

$result->bindValue(‘:vcity’,$vcity);

$result->bindValue(‘:vmobile’,$vmobile);

$success = $result->execute();

if($success > 0)

{

echo “<br> Record Inserted Successfully “;

}

$result->closeCursor();

?>

 

 Output for the program is

 

Record Inserted Successfully

  • An exception is an object which contains information about error which has occurred. Some PHP statements throw exception when an error is occurred. If an exception is not handled, then the applications terminate with error. To handle exception, try catch statement is used.
  • To handle the errors thrown by the PDO library, the PDO Exception class is used.
  • The below code shows an example of PDO Exception.

 

    <?php

// PDO Database Object created

$dsn =’mysql:host=localhost;dbname=account’;

$uname = ‘root’;

$pwd = ‘pwd’;

try

{

$db = new PDO($dsn,$uname,$pwd);

echo “<br> Connection established successfully”;

//

$query = “select * from vendor”;

echo ‘<br>’. $query . ‘<br>’;

$rows = $db->query($query);

$row = $rows->fetch();

while ($row != null)

{

$vid = $row[‘vid’];

$vname = $row[‘vname’];

$vaddress = $row[‘vaddress’];

$vcity = $row[‘vcity’];

$vmobile = $row[‘vmobile’];

echo ‘<br>’;

echo “vid : $vid <br>”;

echo “vname : $vname <br>”;

echo “vaddress : $vaddress <br> “;

echo “vcity : $vcity <br> “;

echo “vmobile : $vmobile <br> “;

// fetch next record

$row = $rows->fetch();

}

}

catch(PDOException $e)

{

$errormessage = $e->getMessage();

echo “<br> An error occured when connecting with database. The error is :

$errormessage”;

}

?>

  • The output of the program is

 

An error occured when connecting with database. The error is : SQLSTATE[28000]

[1045] Access denied for user ‘root’@’localhost’ (using password: YES)

 

References:

 

1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,

2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress

3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,

4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O’Reilly Media

5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox

6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible

7. Joel Murach, Ray Harris: Murach’s PHP and MySQL, Shroff/Murach

8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML/Javascript/DHTML/PHP , BPB Publications

9. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams

10. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education

11. http://www.php.net/

12. http://www.w3schools.com/

13. http://www.tutorialspoint.com/