PHP PDO prepared statement – CRUD Operation
PHP PDO (PHP Data Objects) is basically an abstraction to database access, which means it is not limited to only MySQL or any other database system. If you are building a website in MySQL and suddenly you are required to change the database from MySQL to PostgreSQL, if you are using PDO at backend then only minor changes needs to be done in order to switch database. However, in other cases, you might have to re-write all the queries again in order to switch database.
PDO also provides a very neat way to write prepared statements which helps to prevent SQL injection in your database. In this tutorial, we will be performing completed CRUD (Create, Read, Update, Delete) operation using PHP PDO & MySQL. For sending data from forms, we will using AJAX so you also learn to fetch data from database without having to refresh the page.
Create data using PHP PDO prepared statement
First we will create a form from where we will get the input from user, and when user click the submit button, it will send an AJAX request with all form data. At the server end, server will first connect with our sample database called classicmodels using PDO. Then we will get all fields attached in AJAX request, prepare a query to prevent from SQL injection. Finally execute the query to add a new row in database.
You need to create a new file named save-data.php which will store the record in database. As this is an insert operation, so we might need the newly inserted ID on client side. So we can get the newly inserted ID by calling the $conn->lastInsertId() method from PDO connection object.
index.php
<form onsubmit="return addData();">
<p>
<input id="firstName" placeholder="Enter first name">
</p>
<input type="submit">
</form>
<script>
function addData() {
var firstName = document.getElementById("firstName").value;
var ajax = new XMLHttpRequest();
ajax.open("POST", "save-data.php", true);
ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
ajax.send("firstName=" + firstName);
ajax.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200) {
console.log(this.responseText);
}
};
return false;
}
</script>
save-data.php
<?php
// Connecting with database
$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");
// SQL query string
$sql = "INSERT INTO employees (firstName) VALUES(:firstName)";
// Preparing the statement
$result = $conn->prepare($sql);
// Actually executing the query in database
$result->execute(array(
":firstName" => $_POST["firstName"]
));
// Sending inserted ID back to AJAX
echo $conn->lastInsertId();
?>
Remember that you should not write variables directly in SQL query string. You just have to mark the places where you wanted to put variables like :firstName. This will tell that we are going to map this with our variable later. Then in execute() function, you have to pass an array and map each string with corresponding variable.
Read data using PHP PDO prepared statement
Now that we have saved the data in database, now we need to display it to the user. First we will create table tag where we will display all records from database in tabular form. Then we will call an AJAX to request the server to pull records from database and return them as JSON. When data is returned from server, we will convert the JSON back to Javascript objects and display that in table rows and columns.
index.php
<table>
<tbody id="data"></tbody>
</table>
<script>
var ajax = new XMLHttpRequest();
ajax.open("GET", "get-data.php", true);
ajax.send();
ajax.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200) {
var data = JSON.parse(this.responseText);
var html = "";
for (var a = 0; a < data.length; a++) {
var id = data[a].employeeNumber;
html += "<tr>";
html += "<td>" + data[a].firstName + "</td>";
html += "</tr>";
}
document.getElementById("data").innerHTML += html;
}
};
</script>
On server side, we will connect with database using PDO same way as we did for insert operation. When you are working on projects, make sure to use only one PDO object. You can do that by either using singleton objects or sending persistent attribute in PDO constructor after password field as below.
Using singleton
if ($conn == null)
{
$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");
}
Connect with database, execute the query to get all records from database. In this query, we are not using any variable input which is received from user, so no need to use prepared statement. But if you are required to use user input variable in query, you should follow the same method as we did for insert operation.
get-data.php
<?php
$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "", array(
PDO::ATTR_PERSISTENT => true
));
$sql = "SELECT * FROM employees";
$result = $conn->query($sql);
echo json_encode($result->fetchAll());
?>
Adding persistent attribute in PHP PDO constructor will make your website faster by caching the connection. So the next time some script request for database connection, it will not create a new connection again but will return the cached connection. This will result in removing the overhead on database for making multiple connection on same database. Persistent connections are not closed at the end of script but will remain open for other scripts too.
Update data using PDO
Updating data consists of 3 parts.
- Create an edit button which when clicked will redirect to new page.
- Get all data of selected record from database and auto-populate that in form.
- Update the data in database when that form submits.
In your read operation AJAX where you are displaying data in table, add the below line inside the loop along with other <td> tags. This will redirect to edit page along with ID of selected record. That should be the unique ID in your table. We were already getting this ID variable in read operation.
index.php
html += "<td><a href='edit.php?id=" + id + "'>Edit</a></td>";
The code used for update operation is almost similar to the one we used for insert operation. First we will get the record from database using ID which we will be receiving from URL. User might temper that URL so we must bind that in prepared statement to prevent SQL injection.
The form will be similar to insert operation’s form, except we add a new hidden field for sending ID of selected record. It will also be appended in AJAX request. The rest will remains same.
edit.php
<?php
$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");
$id = $_GET["id"];
$sql = "SELECT * FROM employees WHERE employeeNumber = :id";
$result = $conn->prepare($sql);
$result->execute(array(
":id" => $id
));
$data = $result->fetch();
?>
<form onsubmit="return editData();">
<input type="hidden" id="id" value="<?= $id; ?>">
<input id="firstName" placeholder="Enter first name" value="<?= $data["firstName"]; ?>">
<input type="submit">
</form>
<script>
function editData() {
var firstName = document.getElementById("firstName").value;
var id = document.getElementById("id").value;
var ajax = new XMLHttpRequest();
ajax.open("POST", "edit-data.php", true);
ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
ajax.send("firstName=" + firstName + "&id=" + id);
ajax.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200) {
console.log(this.responseText);
}
};
return false;
}
</script>
Delete data using PDO
Deleting data consists of 2 parts.
- Create a delete button which when clicked will send an AJAX request.
- Server will run the delete query on selected record’s unique ID.
In your read operation AJAX where you are displaying data in table, add the below line inside the loop along with other <td> tags. This will call our Javascript function along with ID of selected record. That should be the unique ID in your table. We were already getting this ID variable in read operation.
A new function deleteData(id) will be created which will accept the ID of selected record and pass that ID with the AJAX request to the server.
index.php
html += "<td><button onclick='deleteData(\"" + id + "\")'>Delete</button></td>";
function deleteData(id) {
if (confirm("Are you sure ?")) {
var ajax = new XMLHttpRequest();
ajax.open("POST", "delete-data.php", true);
ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
ajax.send("id=" + id);
ajax.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200) {
console.log(this.responseText);
}
};
}
}
Delete operation will be almost similar to update operation. You just create a prepared statement and map the ID which we are receiving from AJAX request and execute the query.
delete-data.php
<?php
$conn = new PDO("mysql:host=localhost;dbname=classicmodels", "root", "");
$sql = "DELETE FROM employees WHERE employeeNumber = :id";
$result = $conn->prepare($sql);
$result->execute(array(
":id" => $_POST["id"]
));
echo "Done";
?>
If you want to delete the <tr> tag from <table> when the delete button is pressed, you need to give a custom data ID attribute to TR tag and call the remove() function to remove the TR node.
// In read operation AJAX loop
html += "<tr data-id='" + id + "'>";
// In deleteData() function
document.querySelector("tr[data-id='" + id + "']").remove();
So that’s the completed CRUD operation using PHP PDO & MySQL and AJAX. If you face any problem, feel free to ask in the comment’s section below.
[wpdm_package id=’203′]
