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.

  1. Create an edit button which when clicked will redirect to new page.
  2. Get all data of selected record from database and auto-populate that in form.
  3. 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.

  1. Create a delete button which when clicked will send an AJAX request.
  2. 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′]

Live CRUD with AJAX – PHP

By live CRUD with AJAX, we mean that you will be able to Create, Read, Update, Delete data without having to refresh the page. This tutorial does not have any dependency on jQuery, you can implement it without having to include jQuery in it. We will be using a sample database named classicmodels and it will be included in the source files below. It has multiple tables but we will be using a table named employees to perform all 4 operations.

Live CRUD with AJAX – PHP

Let’s get started:

Create

We will be creating a simple form to input all fields and a button to insert a new row in database. Be default, the browser redirects you to the action attribute of form but we will be using onsubmit event to prevent form from redirecting. And thus, we can call our javascript function to save data via AJAX.

<form method="POST" onsubmit="return doInsert(this);">
    <input name="first_name" placeholder="First name">
    <input name="last_name" placeholder="Last name">
		
    <input type="submit" value="Insert">
</form>

<script>
function doInsert(form) {
    var firstName = form.first_name.value;
    var lastName = form.last_name.value;
		
    var ajax = new XMLHttpRequest();
    ajax.open("POST", "Http.php", true);
    ajax.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');

    ajax.onreadystatechange = function () {
        if (this.readyState == 4 && this.status == 200)
            alert(this.responseText);
    };

    ajax.send("first_name=" + firstName + "&last_name=" + lastName + "&do_insert=1");
    return false;
}
</script>

Http.php

This file will be responsible for serving all 4 operations. We will be creating a database connection and a separate block for each request. Simple !

<?php

$connection = mysqli_connect("localhost", "root", "", "classicmodels");

if (isset($_POST["do_insert"]))
{
    $first_name = $_POST["first_name"];
    $last_name = $_POST["last_name"];
	
    $sql = "INSERT INTO `employees`(`lastName`, `firstName`) VALUES ('$last_name', '$first_name')";
    mysqli_query($connection, $sql);

    echo "Record has been inserted successfully.";
    exit();
}

Read

Display data in tabular form requires a table with unique ID to tbody tag.

<table>
    <thead>
        <tr>
	    <th>First Name</th>
	    <th>Last Name</th>
	</tr>
    </thead>

    <tbody id="data"></tbody>
</table>

<script>
    function getData() {
	var ajax = new XMLHttpRequest();
	ajax.open("GET", "Http.php?view_all=1", 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++) {
		    html += "<tr>";
			html += "<td>" + data[a].firstName + "</td>";
			html += "<td>" + data[a].lastName + "</td>";
		    html += "</tr>";
		}
				
	        document.getElementById("data").innerHTML = html;
	    }
	};
    }

    getData();
</script>

Http.php

There are no parameters in the AJAX request so for the sake of simplicity, in this file we will just be fetching all records from database. Paste the following code right below INSERT block done in previous step in Http.php:

if (isset($_GET["view_all"]))
{
    $sql = "SELECT * FROM employees";
    $result = mysqli_query($connection, $sql);

    $data = array();
    while ($row = mysqli_fetch_object($result))
	    array_push($data, $row);

    echo json_encode($data);
    exit();
}

Delete

Removing a row from database is the simplest part of this tutorial, just enter an ID, sends an AJAX request and execute the DELETE command.

<form method="POST" onsubmit="return doDelete(this);">
    <input name="employee_id" placeholder="Employee ID">
    <input type="submit" value="Delete">
</form>

<script>
    function doDelete(form) {
        var employeeID = form.employee_id.value;

        var ajax = new XMLHttpRequest();
        ajax.open("POST", "Http.php", true);
        ajax.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');

        ajax.onreadystatechange = function () {
            if (this.readyState == 4 && this.status == 200)
                alert(this.responseText);
        };

        ajax.send("employee_id=" + employeeID + "&do_delete=1");
        return false;
    }
</script>

Http.php

We will be using simple DELETE query to remove a row from database. Paste the following code right below VIEW ALL block done in previous step in Http.php:

if (isset($_POST["do_delete"]))
{
    $employee_id = $_POST["employee_id"];

    $sql = "DELETE FROM employees WHERE employeeNumber = '" . $employee_id . "'";
    mysqli_query($connection, $sql);

    echo "Record has been deleted";
    exit();
}

Update (a)

Updating the data would require 2 forms, 1 to get data of record you want to update, and 2nd to actually update the data in database. First we are going to get the record which needs to be updated:

<form method="POST" onsubmit="return getData(this);">
    <input name="employee_id" placeholder="Employee ID">
    <input type="submit" value="Search">
</form>

<script>
function getData(form) {
    var employeeID = form.employee_id.value;

    var ajax = new XMLHttpRequest();
    ajax.open("POST", "Http.php", true);
    ajax.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');

    ajax.onreadystatechange = function () {
        if (this.readyState == 4 && this.status == 200) {
            var data = JSON.parse(this.responseText);
            var form = document.getElementById("form-update");

            /* Show data in 2nd form */
            form.first_name.value = data.firstName;
            form.last_name.value = data.lastName;
            form.employee_id.value = employeeID;

            form.style.display = "";
        }
    };

    ajax.send("employee_id=" + employeeID + "&get_data=1");
    return false;
}
</script>

Http.php

This query will be almost same as for VIEW ALL function. Paste the following code right below DELETE block done in previous step in Http.php:

if (isset($_POST["get_data"]))
{
    $employee_id = $_POST["employee_id"];

    $sql = "SELECT * FROM employees WHERE employeeNumber = '" . $employee_id . "'";
    $result = mysqli_query($connection, $sql);
    $row = mysqli_fetch_object($result);

    echo json_encode($row);
    exit();
}

Update (b)

In this step, we will actually be updating the record in database. This step is almost similar to the INSERT section.

<form method="POST" onsubmit="return doUpdate(this);" id="form-update" style="display: none;">
    <input type="hidden" name="employee_id">
    <input name="first_name" placeholder="First name">
    <input name="last_name" placeholder="Last name">
    <input type="submit" value="Update">
</form>

<script>
function doUpdate(form) {
    var firstName = form.first_name.value;
    var lastName = form.last_name.value;
    var employeeID = form.employee_id.value;

    var ajax = new XMLHttpRequest();
    ajax.open("POST", "Http.php", true);
    ajax.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');

    ajax.onreadystatechange = function () {
        if (this.readyState == 4 && this.status == 200)
            alert(this.responseText);
    };

    ajax.send("first_name=" + firstName + "&last_name=" + lastName + "&employee_id=" + employeeID + "&do_update=1");

    return false;
}
</script>

Http.php

Paste the following code right below GET DATA block done in previous step in Http.php:

if (isset($_POST["do_update"]))
{
    $first_name = $_POST["first_name"];
    $last_name = $_POST["last_name"];
    $employee_id = $_POST["employee_id"];

    $sql = "UPDATE `employees` SET `lastName` = '$last_name', `firstName` = '$first_name' WHERE employeeNumber = '" . $employee_id . "'";
    mysqli_query($connection, $sql);

    echo "Record has been updated successfully.";
    exit();
}

That’s how you can implement a complete live CRUD operation with AJAX, PHP and MySQL. If you want to get more advanced. You can check our tutorial on doing a CRUD operation in Vue JS.

[wpdm_package id=’146′]