This article shows how to create a CRUD (Create, Read, Update, Delete) application in PHP & MySQL using PHP Data Objects (PDO). PDO is a PHP extension that provides an interface for accessing databases in PHP.
PDO is portable and powerful. There are many good features of PDO. The best one is that it’s cross-database compatible. You don’t need to change your query function if you switch database server for your project. PDO extension supports different databases like MS SQL, MySQL, Oracle, PostgreSQL, SQLite, etc.
The other benefit of PDO is error handling feature using exceptions. PDO functions can be written using try/catch block. The exceptions are logged in a log file and a user friendly error message is displayed on screen.
PDO also supports prepared statements and stored procedures. The major benefit of prepared statements is that you just need to prepare a query once and then it can be run multiple times with the same or different parameters. The other benefit is that prepared statements are quoted automatically by the driver. Hence, preventing SQL injection attack to the system/application.
In the past, I had written an article on creating a Simple CRUD System using PHP & MySQL. That article/tutorial uses MySQLi extension driver for accessing the database. In this article, we will be creating the same system but using PDO extension driver to access database.
Here is a step-by-step guide on creating a CRUD system using PHP & MySQL with PDO:
First of all, we will create a new MySQL database. Let us name the database as ‘test‘.
create database `test`;
Then, we will create a new table in database ‘test’. Let us name the table as ‘users‘.
use `test`;
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
);
Now, we will create a config.php file which contains database connection code. This code connects to the MySQL database. This file is included in all PHP pages where a database connection is necessary.
config.php
In below code, the database hostname is localhost where username=root and password=root. The database test has been selected.
<?php
$databaseHost = 'localhost';
$databaseName = 'test';
$databaseUsername = 'root';
$databasePassword = 'root';
try {
// http://php.net/manual/en/pdo.connections.php
$dbConn = new PDO("mysql:host={$databaseHost};dbname={$databaseName}", $databaseUsername, $databasePassword);
$dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting Error Mode as Exception
// More on setAttribute: http://php.net/manual/en/pdo.setattribute.php
} catch(PDOException $e) {
echo $e->getMessage();
}
?>
To add data to the database, we need an HTML form.
add.html
<html>
<head>
<title>Add Data</title>
</head>
<body>
<a href="index.php">Home</a>
<br/><br/>
<form action="add.php" method="post" name="form1">
<table width="25%" border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>Age</td>
<td><input type="text" name="age"></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="email"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="Submit" value="Add"></td>
</tr>
</table>
</form>
</body>
</html>
Form action on add.html is add.php. It means that the submitted form data will go to add.php. In add.php, we do a simple validation of checking if the entered name, email & age are empty or not. If they are all filled then the data will be inserted into database table.
add.php
<html>
<head>
<title>Add Data</title>
</head>
<body>
<?php
//including the database connection file
include_once("config.php");
if(isset($_POST['Submit'])) {
$name = $_POST['name'];
$age = $_POST['age'];
$email = $_POST['email'];
// checking empty fields
if(empty($name) || empty($age) || empty($email)) {
if(empty($name)) {
echo "<font color='red'>Name field is empty.</font><br/>";
}
if(empty($age)) {
echo "<font color='red'>Age field is empty.</font><br/>";
}
if(empty($email)) {
echo "<font color='red'>Email field is empty.</font><br/>";
}
//link to the previous page
echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
} else {
// if all the fields are filled (not empty)
//insert data to database
$sql = "INSERT INTO users(name, age, email) VALUES(:name, :age, :email)";
$query = $dbConn->prepare($sql);
$query->bindparam(':name', $name);
$query->bindparam(':age', $age);
$query->bindparam(':email', $email);
$query->execute();
// Alternative to above bindparam and execute
// $query->execute(array(':name' => $name, ':email' => $email, ':age' => $age));
//display success message
echo "<font color='green'>Data added successfully.";
echo "<br/><a href='index.php'>View Result</a>";
}
}
?>
</body>
</html>
Data from database is fetched and displayed in index.php file. This is our homepage. This file also contains a link to add data. On every row of displayed data, there is also a link to edit and delete data. Below is a sample image of our homepage:
index.php
<?php
//including the database connection file
include_once("config.php");
//fetching data in descending order (lastest entry first)
$result = $dbConn->query("SELECT * FROM users ORDER BY id DESC");
?>
<html>
<head>
<title>Homepage</title>
</head>
<body>
<a href="add.html">Add New Data</a><br/><br/>
<table width='80%' border=0>
<tr bgcolor='#CCCCCC'>
<td>Name</td>
<td>Age</td>
<td>Email</td>
<td>Update</td>
</tr>
<?php
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
echo "<tr>";
echo "<td>".$row['name']."</td>";
echo "<td>".$row['age']."</td>";
echo "<td>".$row['email']."</td>";
echo "<td><a href=\"edit.php?id=$row[id]\">Edit</a> | <a href=\"delete.php?id=$row[id]\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>";
}
?>
</table>
</body>
</html>
Each row of data can be edited separately. Row ID is passed in the URL of edit.php. ID uniquely identifies the data entry.
While adding data, we had two files: add.html and add.php. While editing data, I have kept the entire thing in a single edit.php file. Edit form in HTML and database update code in PHP are present in the same file.
In the code below, at first a single row entry of data is fetched based on the id. The fetched data is displayed in the edit form. When user edits the data and submits the form, then some simple validation is done for empty data. When everything is correct, then that particular entry of data is updated in database.
edit.php
<?php
// including the database connection file
include_once("config.php");
if(isset($_POST['update']))
{
$id = $_POST['id'];
$name=$_POST['name'];
$age=$_POST['age'];
$email=$_POST['email'];
// checking empty fields
if(empty($name) || empty($age) || empty($email)) {
if(empty($name)) {
echo "<font color='red'>Name field is empty.</font><br/>";
}
if(empty($age)) {
echo "<font color='red'>Age field is empty.</font><br/>";
}
if(empty($email)) {
echo "<font color='red'>Email field is empty.</font><br/>";
}
} else {
//updating the table
$sql = "UPDATE users SET name=:name, age=:age, email=:email WHERE id=:id";
$query = $dbConn->prepare($sql);
$query->bindparam(':id', $id);
$query->bindparam(':name', $name);
$query->bindparam(':age', $age);
$query->bindparam(':email', $email);
$query->execute();
// Alternative to above bindparam and execute
// $query->execute(array(':id' => $id, ':name' => $name, ':email' => $email, ':age' => $age));
//redirectig to the display page. In our case, it is index.php
header("Location: index.php");
}
}
?>
<?php
//getting id from url
$id = $_GET['id'];
//selecting data associated with this particular id
$sql = "SELECT * FROM users WHERE id=:id";
$query = $dbConn->prepare($sql);
$query->execute(array(':id' => $id));
while($row = $query->fetch(PDO::FETCH_ASSOC))
{
$name = $row['name'];
$age = $row['age'];
$email = $row['email'];
}
?>
<html>
<head>
<title>Edit Data</title>
</head>
<body>
<a href="index.php">Home</a>
<br/><br/>
<form name="form1" method="post" action="edit.php">
<table border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name" value="<?php echo $name;?>"></td>
</tr>
<tr>
<td>Age</td>
<td><input type="text" name="age" value="<?php echo $age;?>"></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="email" value="<?php echo $email;?>"></td>
</tr>
<tr>
<td><input type="hidden" name="id" value=<?php echo $_GET['id'];?>></td>
<td><input type="submit" name="update" value="Update"></td>
</tr>
</table>
</form>
</body>
</html>
Each row of data can be deleted separately. Row ID is passed in the URL of delete.php. ID uniquely identifies the data entry. After deletion, the user is redirected to homepage (index.php).
delete.php
<?php
//including the database connection file
include("config.php");
//getting id of the data from url
$id = $_GET['id'];
//deleting the row from table
$sql = "DELETE FROM users WHERE id=:id";
$query = $dbConn->prepare($sql);
$query->execute(array(':id' => $id));
//redirecting to the display page (index.php in our case)
header("Location:index.php");
?>
Download Full Source Code: Simple PHP-MySQL CRUD (Add, Edit, Delete, View) using PDO
Hope this helps. Thanks.