2.0Modifying the PDO Select and PDO Insert PHP Scripts

PDO Select PHPScript

1.0Creating an Select PDO Query

3.0Creating and Executing the Select query

4.0Closing the connection to the MySQL database

PDO Select PHP Script v2Prepared by Ivailo Chakarov

PDOSelect PHP Script

1.0Creating an Select PDO Query

In your previous session you learnt how to open and close a connection to a MySQL database using PDO. The simplest form of query is the PDO query method. Although we are using MySQL, considering that PDO provides a common set of tools for databases, once we have the correct connection established, the rest of the code can be used regardless of the type of database that we have chosen (Source: Introduction to PHP PDO).

Here is an example PHP file (called all_employees.php) which is used to display all the employee recordsfrom the employee table of your employeeproject database.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

<html xmlns="

<head>

<title>Display All Employees</title>

</head>

<body>

<?php

//setting the database connection parameters

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "employeeproject";

PDO Select PHP Script v2Prepared by Ivailo Chakarov

try {

//creating the database connection object

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

// set the PDO error mode to exception

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

//prepare the SELECT statement to choose all employees

$employees = $conn->prepare("SELECT * FROM employee");

//execute the SELECT statement

$employees->execute();

//display all the records from the employees table in a table format.

echo "<h2>Display all employee records</h2>";

echo "<table border='1'>";

echo "<tr>";

//print out the Emp_No heading

echo "<td width='50'>Emp_No</td>";

//print out the Emp_fname heading

echo "<td width='100'>Emp_fname</td>";

//print out the Emp_sname heading

echo "<td width='100'>Emp_sname</td>";

//print out the Grade heading

echo "<td>Grade</td>";

echo "</tr>";

//loop over all table rows and fetch them as an object

while($result = $employees->fetch(PDO::FETCH_OBJ))

{

//print out the details of each employee

echo "<tr>";

//print out the Employee's No

echo "<td width='50'>" . $result->Emp_No . "</td>";

//print out the Employee's First Name

echo "<td width='100'>" . $result->Emp_fname . "</td>";

//print out the Employee's Surname

echo "<td width='100'>" . $result->Emp_sname . "</td>";

//print out the Employee's Grade

echo "<td>" . $result->Grade . "</td>";

echo "</tr>";

echo("\n");

}

echo "</table>";

//close the database connection

$conn = null;

}

//deal with any PDO connection execeptions.

catch(PDOException $e)

{

echo "Connection failed: " . $e->getMessage();

}

</body>

</html>

PDO Select PHP Script v2Prepared by Ivailo Chakarov

Note:

The names of the columns in your database table employees have to match the names which are following the $result variable. (e.g. $result->Emp_No – there has to be a column called Emp_No in existence in your employeetable).

After running the PHP script, the following should be displayed on your screen (this is an example: your output may vary, depending on how many records have been inserted in your table).

2.0Modifying the PDO Select and PDO Insert PHP Scripts

Please include the following PHP code towards the end of the PDO Select PHP script which you have just created. The name of your php file in the link should match the name of the PDO Insert PHP script which you created in last week’s session. In my case this was new_employee.php

echo "</table>";

//link this PHP file to the PDO Insert script (insert_new_employee.php)

echo "<p>";

echo "<a href='new_employee.php'>Insert a new employee record</a>";

echo "</p>";

//close the database connection

$conn = null;

}

//deal with any PDO connection execeptions.

catch(PDOException $e)

{

echo "Connection failed: " . $e->getMessage();

}

</body>

</html>

Please include the following PHP code towards the end of the PDO insert PHP script which you created during last week’s session. The name of your php file in the link should match the name of the PDO select PHP script which you created during today’s session. In my case it is all_employees.php

<p</p>

</form>

<?php

//link this PHP file to the PDO Select script (select_all_employees.php)

echo "<p>";

echo "<a href='all_employees.php'>Display all employee records</a>";

echo "</p>";

3.0Creating and Executing the Select query

The$employees variable is created to store the SQL Select statement which is used to displayall the recordsfrom the employee table of your database:

//prepare the SELECT statement to choose all employees

$employees = $conn->prepare("SELECT * FROM employee");

//execute the statement

//execute the SELECT statement

$employees->execute();

If successful, when executed the query would return all the employee records and display them in table rows (one record at a time):

//loop over all table rows and fetch them as an object

while($result = $employees->fetch(PDO::FETCH_OBJ))

{

//print out the details of each employee

echo "<tr>";

//print out the Employee's No

echo "<td width='50'>" . $result->Emp_No . "</td>";

//print out the Employee's First Name

echo "<td width='100'>" . $result->Emp_fname . "</td>";

//print out the Employee's Surname

echo "<td width='100'>" . $result->Emp_sname . "</td>";

//print out the Employee's Grade

echo "<td>" . $result->Grade . "</td>";

echo "</tr>";

echo("\n");

}

The while statement has been used to return all records from the employee table into an object called $result and then display each record’s value for Emp_No, Emp_fname, Emp_sname and Grade (these have to match the names of your columns in the respective table of your database).

In this instance, the names of the columns in the employee table are shown below:

Each of the column names is accessed in the PHP script using the following reference: $result->Grade(whereby Grade matches the name of your column in the employee table).

4.0Closing the connection to the MySQL database

The connection to the database would need to be closed by using the statement:

$conn =null;

PDO Select PHP Script v2Prepared by Ivailo Chakarov