Laravel Soft Delete – Create a Trash Can using PHP and MySQL

Laravel soft delete allows you to enable some models that will not be permanently deleted by just calling the delete() method. Instead, you need to call the forceDelete() method in order to permanently delete the data.

The deleted models will not be fetched from Eloquent or Query Builder queries. Instead, you need to call a separate function in order to fetch the records along with deleted ones, we will get on that later in this article. Laravel soft delete has several advantages:

  • First, it works like your PC’s recycle bin. If you accidentally deleted from record from your admin panel, it will not be deleted permanently at once.
  • Second, you can display all deleted data on a separate page.
  • You have a backup of your deleted data, so in the future, if you need some old data, you can always get it from recycle bin or trash can.
  • Can display all data to the user and highlight the data that is deleted.
  • Always be able to restore the accidentally deleted records.

Let’s get started

So let’s get started by creating a simple Laravel soft delete module. For the sake of this article, we will be implementing a trash can in our user’s model.

1. Creating a column in the user’s table

First, you need to run the following command to create a migration for soft deleting:

php artisan make:migration add_column_soft_delete_in_users_table

Then you need to open your migration file and add the following line in the Schema::table function inside up() method:

$table->softDeletes();

Then you need to run the migration using the following command:

php artisan migrate

Open your phpMyAdmin and you will see a new column at the end of the user’s table named deleted_at. Its default value will be NULL and it will have a current date & time value once you call the delete() method.

2. Laravel Soft Delete Eloquent Model

Open your user model usually in App\Models\User.php and update it as the following:

use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use HasFactory, Notifiable, SoftDeletes;

    ///////////
}

After that, whenever you call the delete() method on any user object, you will see that deleted_at the field will be updated to the current date & time of that user.

3. Display all Users with Pagination

Let’s say that you have an admin panel. We will be displaying all users (non-deleted) on one page. So first create a route for this in your routes/web.php file:

use App\Http\Controllers\UserController;

Route::group([
    "prefix" => "users"
], function () {
    Route::get("/", [UserController::class, "index"]);

    // [other routes goes here]
});

We have created a group because we will be adding more routes to it. For example, route to delete a user, route to display all deleted users, route to restore a user, and route to permanently delete a user. Run the following command if you do not have the UserController:

php artisan make:controller UserController

Now, create an index() method in your UserController class. Following is what your UserController should look like:

use App\Models\User;

public function index()
{
    $users = User::orderBy("id", "desc")->paginate();
    $trashed = User::onlyTrashed()->count();

    return view("users/index", [
        "users" => $users,
        "trashed" => $trashed
    ]);
}

Create a folder named users and inside this folder, create a file in resources/views/users/index.blade.php. This file should have the following code:

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/css/bootstrap.min.css" />
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/js/bootstrap.min.js"></script>

<a href="{{ url('/users/trashed') }}">
    Trash ({{ $trashed }})
</a>

<table class="table table-bordered">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
        </tr>
    </thead>

    <tbody>
        @foreach ($users as $user)
            <tr>
                <td>{{ $user->id }}</td>
                <td>{{ $user->name }}</td>
                <td>{{ $user->email }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

{{ $users->appends(request()->except("page"))->links() }}

Refresh the page now and you will see a list of all users. Now we need to create a button to delete a user.

4. Delete a User

To delete a user, we will first a heading in our <thead> tag:

<th>Actions</th>

Then in <tbody>, we need to create a form which when submit will first ask for confirmation. If confirmed, then it will mark the user as deleted.

<td>
	<form method="POST" action="{{ url('/users/delete') }}" onsubmit="return confirm('Are you sure you want to delete this user ?');">
		{{ csrf_field() }}
		<input type="hidden" name="id" value="{{ $user->id }}" required>
		<button type="submit" class="btn btn-danger">
			Delete
		</button>
	</form>
</td>

Refresh the page and you will see a delete button along with each user. Then create a route on your web.php file:

Route::post("/delete", [UserController::class, "destroy"]);

The above lines go in the [other routes goes here] section of web.php. Then create a method in your UserController to mark the user as deleted:

public function destroy()
{
    $user = User::find(request()->id);
    if ($user == null)
    {
        abort(404);
    }

    $user->delete();
    return redirect()->back();
}

You can delete the user in one line too, like this: User::destroy(request()->id); but fetching the user first has some advantages:

  • You can perform any other sub-functions before deleting a user.
  • Or check if the user exists or not, and display the proper messages accordingly.

Try deleting a user now, you will see that will no longer be displayed in your Bootstrap table. But you can still see the record in your database using phpMyAdmin. However, you will notice that the deleted_at the field is updated and now has the UTC date and time value when the delete operation was performed.

5. Show All Deleted Users

At 3rd step, we create an anchor tag that displays all the trashed user’s counts. Now is the time to create a route for it. To create a route on your routes/web file.

Route::get("/trashed", [UserController::class, "trashed_users"]);

Then create the following method in your UserController:

public function trashed_users()
{
    $trashed = User::onlyTrashed()->orderBy("id", "desc")->paginate();

    return view("users/trashed", [
        "trashed" => $trashed
    ]);
}

After that, create a file resources/views/users/trashed.blade.php. It will have the following code:

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/css/bootstrap.min.css" />
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/js/bootstrap.min.js"></script>

<table class="table table-bordered">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Deleted at</th>
            <th>Actions</th>
        </tr>
    </thead>

    <tbody>
        @foreach ($trashed as $user)
            <tr>
                <td>{{ $user->id }}</td>
                <td>{{ $user->name }}</td>
                <td>{{ $user->email }}</td>
                <td>{{ $user->deleted_at }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

{{ $trashed->appends(request()->except("page"))->links() }}

Refresh the page now and you will see all deleted users in a table. You will also be able to view the date and time when they were deleted.

6. Restore the Deleted Records

First, create another <td> tag at the end of <tbody> tag. In this table cell, we will create a button that will ask for confirmation. And when confirmed, will restore the user.

<td>
    <form method="POST"
        action="{{ url('/users/restore') }}"
        onsubmit="return confirm('Are you sure you want to restore this user ?');">

        {{ csrf_field() }}

        <input type="hidden" name="id" value="{{ $user->id }}" required />
        <button type="submit" class="btn btn-success">
            Restore
        </button>
    </form>
</td>

Then create a route in your web/routes.php that will handle this request.

Route::post("/restore", [UserController::class, "do_restore"]);

After that, create the following method in your UserController:

public function do_restore()
{
    $user = User::withTrashed()->find(request()->id);
    if ($user == null)
    {
        abort(404);
    }

    $user->restore();
    return redirect()->back();
}

Refresh the page now and you will see a “Restore” button along with each delete user. On clicking that, you will be asked for confirmation. If confirmed, you will no longer see that record in the trash can. But you will start seeing that record in all users table.

If you open your database using phpMyAdmin, you will see that user will again have the deleted_at column value as NULL.

7. Permanently Delete Records

Where you are displaying all soft-deleted records in your trash can, you are already displaying a button to restore the record. Now is the time to display another button that will permanently delete the record from the database. Create a button along with the “Restore” button in your trash can:

<form method="POST"
    action="{{ url('/users/delete-permanently') }}"
    onsubmit="return confirm('Are you sure you want to permanently delete this user ?');">

    {{ csrf_field() }}

    <input type="hidden" name="id" value="{{ $user->id }}" required />
    <button type="submit" class="btn btn-danger">
        Delete
    </button>
</form>

Then, you need to create a route that will handle this request.

Route::post("/delete-permanently", [UserController::class, "delete_permanently"]);

Then you need to create a method in your UserController that will permanently delete that record from the database.

public function delete_permanently()
{
    $user = User::withTrashed()->find(request()->id);
    if ($user == null)
    {
        abort(404);
    }

    $user->forceDelete();
    return redirect()->back();
}

Refresh the page now and click on the delete button from the trash can. You will be asked for confirmation. Once confirmed, you will no longer see that record in the trash can nor in all users table. If you check the database, you will see that, that user’s row has been permanently deleted from the database.

Get our social networking site project developed in Laravel:

Social Networking Site in Laravel – Node JS, Socket IO

So that’s how you can implement a complete Laravel soft delete module in your web application.

[wpdm_package id=’1545′]

Firebase Storage – Upload, Download, and Delete

In this tutorial, we are going to teach you, how you can upload, download and delete files from Firebase Storage. Firebase Storage is a service provided by Google that allows you to save files on their server. The free plan allows you to upload data up to 1 GB. More data can be bought from the Firebase Pricing page.

What you are going to learn:

  1. Upload files on Firebase Storage.
  2. Save the data in Realtime Database.
  3. Fetch files from the Firebase Storage.
  4. Download files from Firebase Storage.
  5. Fix CORS issue if working in localhost.
  6. Install gsutil.
  7. Delete files from Firebase Storage.

Video tutorial:

Upload file

We are going to upload the file to the Firebase storage and save its path in Firebase Realtime Database. It is another service provided by Google to save data in JSON format. To upload the file, we are going to show a form with an input type file that allows the user to select any type of file. We will be using Vue JS to render the HTML to view the data in real-time, for example, newly uploaded files will be displayed automatically and deleted files will be removed from the view without having to refresh the page.

<div id="app">
	<form id="upload-form">
		<input type="file" name="file" required />
		<input type="submit" value="Upload" />
	</form>

    [show all uploaded here]
</div>

<script src="vue.min.js"></script>

This will display a form with an input type file to upload a file. And a submit button which when clicked will submit the form. Vue JS production file can be downloaded from here. Now you need to attach a submit event listener to this form using Javascript.

<script type="module">

	// [initialize firebase here]

	window.addEventListener("load", function () {
		document.getElementById("upload-form").addEventListener("submit", function () {
			event.preventDefault();
			var form = event.target;

			var file = form.file.files[0];
			console.log(file);
			// [upload in storage here]
		});
	});
</script>

If you select the file and hit submit, you will see your selected file object in the browser console. Now you need to do 2 things; initialize firebase, and upload the file. To initialize firebase, you need to create an app at Firebase Console.

  1. Add new project.
  2. Enter name of project.
  3. When project is created, select “web”.
  4. Copy the firebaseConfig variable.

Replace the [initialize firebase here] section with the following:

import { initializeApp } from "https://www.gstatic.com/firebasejs/9.1.0/firebase-app.js";

import { getStorage, ref as stRef, uploadBytes } from "https://www.gstatic.com/firebasejs/9.1.0/firebase-storage.js";

import { getDatabase, ref as dbRef, push, set } from "https://www.gstatic.com/firebasejs/9.1.0/firebase-database.js";

// Your web app's Firebase configuration
const firebaseConfig = {
	apiKey: "",
	authDomain: "",
	projectId: "",
	storageBucket: "",
	messagingSenderId: "",
	appId: ""
};

// Initialize Firebase
const app = initializeApp(firebaseConfig);

// Get a reference to the storage service, which is used to create references in your storage bucket
const storage = getStorage(app);

const database = getDatabase();
const databaseReference = dbRef(database, "files");

Replace the firebaseConfig variable with your firebase configurations. Now replace the [upload in storage here] section with the following:

const storageRef = stRef(storage, "files/" + file.name);

uploadBytes(storageRef, file).then(function (snapshot) {
	var newFileRef = push(databaseReference);

	set(newFileRef, {
		"name": file.name
	});
});

This will upload the file in Firebase Storage in a folder named “files”. You can see it in your project’s dashboard in left menu in “Storage” page. Also, it will create a new element in “files” array in “Realtime Database” page.

Displaying Uploaded Files in Firebase Storage

Now the file is being uploaded in Firebase Storage and it’s path is also being saved in realtime database. Now you need to show all uploaded files. Replace the [show all uploaded here] section with the following:

<table>
	<tr>
		<th>ID</th>
		<th>Name</th>
		<th>File</th>
		<th>Actions</th>
	</tr>

	<tr v-for="file in files">
		<td>{{ file.id }}</td>
		<td>{{ file.name }}</td>
		<td>
			[download link here]
		</td>
		<td>
			[delete button here]
		</td>
	</tr>
</table>

This will create a simple HTML table. Now you need to do the following things:

  1. Initialize Vue JS.
  2. Get all data from realtime database.
  3. Render in HTML table.

You can initialize the Vue JS from the following code:

var vueApp = new Vue({
	el: "#app",
	data: {
		files: []
	},
    // [updated event goes here]
});

Then include “onValue” in your “firebase-database” import. So your firebase database import line will become:

import { getDatabase, ref as dbRef, push, set, onValue } from "https://www.gstatic.com/firebasejs/9.1.0/firebase-database.js";

Note the “onValue” in the import block. Similarly, add the “getDownloadURL” in the import of “firebase-storage”. So your firebase storage import line will become:

import { getStorage, ref as stRef, uploadBytes, getDownloadURL } from "https://www.gstatic.com/firebasejs/9.1.0/firebase-storage.js";

Note the “getDownloadURL” in the import block. Then write the following code in your Javascript:

onValue(databaseReference, function (snapshot) {
	snapshot.forEach(function (childSnapshot) {
		const value = childSnapshot.val();

		const storageRefDownload = stRef(storage, "files/" + value.name);
		getDownloadURL(storageRefDownload).then(function (url) {
			vueApp.files.push({
				"id": childSnapshot.key,
				"name": value.name,
				"url": url
			});
		});
	});
});

You can apply the following CSS styles to make the table look a little nice:

<style>
	table, th, td {
		border: 1px solid black;
		border-collapse: collapse;
	}
	th, td {
		padding: 25px;
	}
</style>

Download File from Firebase Storage

Now you need to download the file. First, you need to replace the [download link here] section with the following:

<a v-bind:href="file.url" v-bind:download="file.name" onclick="downloadFile();">Download</a>

Create the following function in your Javascript:

function downloadFile() {
	// prevent default href action
	event.preventDefault();

	// get URL from href
	var anchor = event.target;
	var url = anchor.getAttribute("href");

	// get blob data
	const xhr = new XMLHttpRequest();
	xhr.responseType = "blob";
	xhr.onload = function (event) {
		const blob = xhr.response;

		// get clickable URL of blob data
		const blogUrl = window.URL.createObjectURL(blob);

		// replace href with new blob value
		anchor.setAttribute("href", blogUrl);

		// remove the onclick listener
		anchor.removeAttribute("onclick");

		// download the file
		anchor.click();

		// free up the memory
		window.URL.revokeObjectURL(blogUrl);
	};
	xhr.open("GET", url);
	xhr.send();
}

// make the function global so it can be accessible from anchor tag onclick
window.downloadFile = downloadFile;

Run the code now, and you will be able to download the file.

Note: If you get a CORS error while working in localhost, then do the following steps:

  1. Download gsutil from here.
  2. Extract the ZIP and paste the folder to a permanent location in your system.
  3. Open the terminal or command prompt in that extracted folder and run the following command in it:
./install.sh
  1. Once installed, run the following command to initialize it:
gcloud init
  1. It will ask for your project name too.
  2. After the project is set and gcloud is initialized, create a file named “cors.json” at the root of your project.
  3. Your “cors.json” must have the following content:
[
	{
		"origin": ["*"],
		"method": ["GET"],
		"maxAgeSeconds": 3600
	}
]
  1. Then run the following command in your terminal:
gsutil cors set cors.json gs://<your-cloud-storage-bucket>
  1. Now try to download the file again, your CORS error should be fixed.

Delete File from Firebase Storage

First, replace the [delete button here] section with the following:

<form class="delete-form">
	<input type="hidden" name="id" v-bind:value="file.id" />
	<input type="hidden" name="name" v-bind:value="file.name" />
	<input type="submit" value="Delete" />
</form>

Then add “deleteObject” in your import of “firebase-storage”. So your import line will look like this:

import { getStorage, ref as stRef, uploadBytes, getDownloadURL, deleteObject } from "https://www.gstatic.com/firebasejs/9.1.0/firebase-storage.js";

Then add the following event in your [updated event goes here] section:

updated: function () {
	var forms = document.querySelectorAll(".delete-form");
    for (var a = 0; a < forms.length; a++) {
    	forms[a].setAttribute("onsubmit", "onDeleteFormSubmit();");
    }
}

This will attach an event listener to every delete form, which will be called when the form is submitted. Now you need to create the following function in your Javascript:

function onDeleteFormSubmit() {
	event.preventDefault();
	var form = event.target;

	const tempDbRef = dbRef(database, "files/" + form.id.value);
	set(tempDbRef, null);

	const deleteStorageReference = stRef(storage, "files/" + form.name.value);
	deleteObject(deleteStorageReference);

	for (var a = 0; a < vueApp.files.length; a++) {
		if (vueApp.files[a].id == form.id.value) {
			vueApp.files.splice(a, 1);
			break;
		}
	}
}
window.onDeleteFormSubmit = onDeleteFormSubmit;

This will first delete the data from the real-time database. Then it will delete the file from Firebase Storage. Finally, it will remove it from the Vue JS array, so it will automatically be removed from the HTML table too.

You can also create a realtime chat in Firebase. Learn how to do it from here.

Now you will be able to upload files to the Firebase Storage and save its path in Realtime Database. And also to download and delete the files from it. If you face any problems facing this tutorial, kindly let us know in the comments section below.

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′]