PDO Delete and Update Phpscripts
PDO Delete and Update PHPScripts
1.0Creating a Delete PDO Query
2.0Modifying the PDO Select Script
3.0Creating an Update PDO Query
4.0Modifying the PDO Insert Script
5.0Closing the connection to the MySQL database
1
PDO Delete and Update PHP Scripts v2 Ivailo Chakarov
PDODelete and Update PHP Scripts
1.0Creating aDelete PDO Query
In your previous sessions you learnt how to open and close a connection to a MySQL database using PDO as well as learnt how to create a PDO Insert and Select PHP scripts. This handout examines how you can create PDO Delete and Update PHP scripts. 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 (delete_employee.php) which is used to deletean employee record from the employee table of your employeeproject database.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "
<html xmlns="
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Delete An Employee</title>
</head>
<body>
<?php
//setting the database connection parameters
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "employeeproject";
//initialise a parameter which would be set up to pass the employee's number to be deleted
$employeeid = "";
//check whether the parameter of the employee's id has been specified when the Delete PHP script is called
if(isset($_GET['id'])) {
$employeeid = $_GET["id"];
}
//if the id of the employee has not been specified a default emp_no of 12 is being used
else {
$employeeid = '12';
}
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 sql statement which returns how many employees match this employee id
$selected_employee = "SELECT COUNT(*) FROM Employee WHERE Emp_No='$employeeid'";
//prepare the select statement to be executed
$no_of_employees = $conn->prepare($selected_employee);
//prepare the sql statement to delete a record
$delete_sql = "DELETE FROM Employee WHERE emp_no='$employeeid'";
/*prepare the select statement to return the number of projects (if any which
the specific selected employee has allocated against their name */
$no_of_projects = "SELECT COUNT(*) FROM Emp_On_Project WHERE Emp_No='$employeeid'";
//execute the no_of_projects query
if ($res2 = $conn->query($no_of_projects)) {
/* Check the number of rows that match the SELECT statement */
if ($res2->fetchColumn() > 0) {
//if the number of projects allocated against the selected employee is more than zero
//then issue a warning - i.e. you must delete the projects first before deleting the
//employees! - the foreign key constraint will stop you executing the DELETE query!
echo "<b>Warning!</b<p>";
echo "There are project allocations for <b>Emp_No $employeeid</b>.";
echo "You must delete the employee allocations first before deleting the employee!</p>";
echo "<p<a href='all_employees.php'>Display All Employees</a ";
echo "<a href='new_employee.php'>Insert New Employee</a</p>";
}
//otherwise you can prepare to delete the chosen employee from the employee table of the database
else
{
//executed the query to find out how many employees match this employee id
if ($res = $conn->query($selected_employee)) {
/* Check the number of rows that match the SELECT statement */
if ($res->fetchColumn() > 0) {
//if more than one row is returned then execute the delete query
// use exec() because no results are returned
$count = $conn->exec($delete_sql);
/* Delete statement executed */
print("You executed the following SQL statement: <b>$delete_sql</b<br />");
/* Return number of rows that were deleted */
print("<p>Deleted $count rows from the employee table.</p>");
}
/* No rows matched -- do something else */
else {
/* Return the message below */
print "No rows matched the query.";
}
}
//display the links at the bottom of the page to the other two php pages
echo "<p<a href='all_employees.php'>Display All Employees</a ";
echo "<a href='new_employee.php'>Insert New Employee</a</p>";
}
}
}
//deal with any PDO connection execeptions.
catch(PDOException $e)
{
echo $selected_employee . "<br>" . $e->getMessage();
}
//close the database connection
$conn = null;
?>
</body>
</html>
2.0Modifying the PDO Select Script
Please modify yourPDO Select PHP script which you have created in the previous session (changes are highlighted in different colour):
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "
<html xmlns="
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Display All Employees</title>
</head>
<body>
<?php
//setting the database connection parameters
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "employeeproject";
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>";
//print out the Update heading
echo "<td>Update?</td>";
//print out the Delete heading
echo "<td>Delete?</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>";
//print out the Update option
echo "<td align='center'>" . "<a href='update_employee.php?id=$result->Emp_No'>X</a>" . "</td>";
//print out the Delete option
echo "<td align='center'>" . "<a href='delete_employee.php?id=$result->Emp_No'>X</a>" . "</td>";
echo "</tr>";
echo("\n");
}
echo "</table>";
//link this PHP file to the PDO Insert script (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>
3.0Creating an Update PDO Query
Here is an example PHP file (update_employee.php) which is used to update an employee’s record.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "
<html xmlns="
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Delete An Employee</title>
</head>
<body>
<?php
// define variables and set to empty values
$employee_no = $employee_firstname = $employee_surname = $employee_grade = "";
//setting the database connection parameters
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "employeeproject";
$employeeid = "12";
//if the POST method is used to submit the update form then the employee id is set to the one
//specified in the Employee No text field in the form
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$employeeid = test_input($_POST["emp_no"]);
}
//if the GET method was used to call the update PHP script from a different page then
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
//check whether the parameter of the employee's id has been specified when the Delete PHP script is called
if(isset($_GET['id'])) {
$employeeid = $_GET["id"];
}
//if the id of the employee has not been specified a default emp_no of 12 is being used
else {
$employeeid = '12';
}
}
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 matching the Employee ID
$selected_employee = $conn->prepare("SELECT * FROM Employee WHERE Emp_No='$employeeid'");
//execute the SELECT statement
$selected_employee->execute();
//prepare a SELECT statement to return the pay grade which the chosen employee is on
$selected_grade = $conn->prepare("SELECT Grade FROM Employee WHERE Emp_No='$employeeid'");
//exectute the query
$selected_grade->execute();
//prepare the SELECT statement to return all possible grades
$grades = $conn->prepare("SELECT * FROM Pay_Structure");
//execute the SELECT statement
$grades->execute();
//save the employee's grade in a variable to be used for displaying the right option
//in the select drop down menu
$employee_grade = $selected_grade->fetchColumn();
//if the form was submitted (using the POST method)
if ($_SERVER["REQUEST_METHOD"] == "POST") {
//collect all the form entries and store them in variables
$employee_no = test_input($_POST["emp_no"]);
$employee_firstname = test_input($_POST["emp_fname"]);
$employee_surname = test_input($_POST["emp_sname"]);
$employee_grade = test_input($_POST["emp_paygrade"]);
//prepare the UPDATE statement (using the modified values that the user has changed to)
$update_sql = "UPDATE employee SET emp_no='$employee_no', emp_fname='$employee_firstname', emp_sname='$employee_surname', grade='$employee_grade' WHERE emp_no=$employee_no";
//execute the UPDATE statement
if ($conn->exec($update_sql)) {
//display confirmation that the record has been updated successfully.
echo "Your record updated successfully.";
echo "<p>";
echo "The following employee record was updated: " .
"(emp_no: <b>$employee_no</b>, emp_fname: <b>$employee_firstname</b>," .
"emp_sname: <b>$employee_surname</b>, grade: <b>$employee_grade)</b>";
echo "</p>";
}
else {
//echo the update statement error which is generated if the statement fails;
echo "Error: " . $update_sql. "<br>" . $conn->error;
}
//close the database connection
$conn = null;
}
}
//deal with any PDO connection execeptions.
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
//the function test_input strips any blanks, special characters,
//etc. and returns back the cleaned data
function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
?>
<h3>Update an Employee</h3>
<form method="post"
action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>"
name="new_employee">
<?php
while($result = $selected_employee->fetch(PDO::FETCH_OBJ))
{
//display the values returned from the SELECT query in the text fields of the form
//Make the employee No readonly (i.e. the user will not be able to change the employee no in the form!)
echo 'Employee No (Readonly) <input name="emp_no" type="text" size="11" maxlength="11" value="' .$result->Emp_No .'" readonly/<br />';
echo 'First Name <input name="emp_fname" type="text" size="35" maxlength="80" value="' .$result->Emp_Fname . '" /<br />';
echo 'Surname <input name="emp_sname" type="text" size="35" maxlength="80" value="' .$result->Emp_Sname . '"/> <br />';
echo 'Grade ';
echo '<select name="emp_paygrade">';
//display all the available values for grades in the drop down menu
while($result2 = $grades->fetch(PDO::FETCH_OBJ))
{
echo '<option value="'.$result2->Grade .'"';
//if the Grade number matches the grade number of the specific chosen employee
if ($result2->Grade == $employee_grade)
{
//then select it !
echo ' selected ';
}
echo '>'. $result2->Grade .'</option>';
}
echo '</select>';
}
?>
<p</p>
<input type="reset" name="reset" />
<input type="submit" name="submit" />
</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>";
?>
</body>
</html
4.0Modifying the PDO Insert Script
You can make the following modification to the PDO Insert Script which will ensure that the Employee_No is automatically generated. The changes are highlighted in red.
try {
$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 (which returns the next available employee number
$ne = $conn->prepare('SELECT (MAX(Emp_No)+1) AS New_Emp_No FROM employee');
//the prepared SELECT statement is executed
$ne->execute();
/// output has been omitted
<h3>Enter a new Employee</h3>
<form method="post"
action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>"
name="new_employee">
Employee No
<?php
//fetch the executed SELECT query results
$row = $ne->fetch(PDO::FETCH_ASSOC);
//use a variable to store the value of the new employee number
$new_employee_number = $row['New_Emp_No'];
//output the next available employee in the input form field
echo "<input name=\"emp_no\" type=\"text\" size=\"11\" maxlength=\"11\" value =\"$new_employee_number\" readonly\"/<br />";
?>
First Name <input name="emp_fname" type="text" size="35" maxlength="80" /<br />
TheSQL statement above is as follows:
SELECT (MAX(Emp_No)+1) AS New_Emp_No FROM employee
The query will find the maximum number of all employee numbers and adds one to the returned number to ensure that the next available number is automatically inserted in the Emp_No form field.
The following part of the PHP code ensures that the next available employee number is automatically inserted in the Emp_No form field.
//output the next available employee in the input form field
echo "<input name=\"emp_no\" type=\"text\" size=\"11\" maxlength=\"11\" value =\"$new_employee_number\" readonly\"/<br />";
The readonly attribute of the <input> tag ensures that the form field is not editable, which guarantees that the number cannot be changed by the user.
5.0Closing the connection to the MySQL database
The connection to the database would need to be closed by using the following statement:
$conn =null;
1
PDO Delete and Update PHP Scripts v2 Ivailo Chakarov