There are many things you can do with AJAX to improve the user experience, for example, Add, edit or delete the record and update the layout without reloading the page.
With only PHP you can easily delete records but require to submit the page or send value by URL and according to it delete a record.
This works and removes the record but it reloads the page every time.
You can do this with jQuery AJAX where you need to pass the record id from AJAX which needs to delete.
In the example, I am creating the HTML table which shows the list of records with a delete button. When the button gets clicked then remove the record and also remove the HTML table row with fadeOut() effect.
Contents
1. Table structure
I am using posts
table in the example.
CREATE TABLE `posts` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `title` varchar(100) NOT NULL, `content` text NOT NULL, `link` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Configuration
Create a config.php
file for the database connection.
Completed Code
<?php $host = "localhost"; /* Host name */ $user = "root"; /* User */ $password = ""; /* Password */ $dbname = "tutorial"; /* Database name */ $con = mysqli_connect($host, $user, $password,$dbname); // Check connection if (!$con) { die("Connection failed: " . mysqli_connect_error()); }
3. HTML
List records from posts
table in the <table >
.
Here, I add the <span class='delete'>
for deleting the record and store delete id in data-id
variable.
Completed Code
<div class='container'> <table border='1' > <tr style='background: whitesmoke;'> <th>S.no</th> <th>Title</th> <th>Operation</th> </tr> <?php $query = "SELECT * FROM posts"; $result = mysqli_query($con,$query); $count = 1; while($row = mysqli_fetch_array($result) ){ $id = $row['id']; $title = $row['title']; $link = $row['link']; ?> <tr> <td align='center'><?= $count; ?></td> <td><a href='<?= $link; ?>'><?= $title; ?></a></td> <td align='center'> <span class='delete' data-id='<?= $id; ?>'>Delete</span> </td> </tr> <?php $count++; } ?> </table> </div>
4. PHP
Create a remove.php
file.
Read POST id.
Check if a record exists or not in the posts
table. If exists then execute the Delete query on the id and return 1 otherwise return 0.
Completed Code
<?php include "config.php"; $id = 0; if(isset($_POST['id'])){ $id = mysqli_real_escape_string($con,$_POST['id']); } if($id > 0){ // Check record exists $checkRecord = mysqli_query($con,"SELECT * FROM posts WHERE id=".$id); $totalrows = mysqli_num_rows($checkRecord); if($totalrows > 0){ // Delete record $query = "DELETE FROM posts WHERE id=".$id; mysqli_query($con,$query); echo 1; exit; }else{ echo 0; exit; } } echo 0; exit;
5. jQuery
Define click
event on delete
class.
Read delete id from data-id
attribute and assign it in deleteid
variable.
Send AJAX post request to 'remove.php'
file. Pass deleteid
as data.
On AJAX successfully callback check response.
If response == 1
then remove a row using remove()
and use fadeOut()
effect to add some delay.
If response != 1
then alert('Invalid ID.');
.
Completed Code
$(document).ready(function(){ // Delete $('.delete').click(function(){ var el = this; // Delete id var deleteid = $(this).data('id'); var confirmalert = confirm("Are you sure?"); if (confirmalert == true) { // AJAX Request $.ajax({ url: 'remove.php', type: 'POST', data: { id:deleteid }, success: function(response){ if(response == 1){ // Remove row from HTML Table $(el).closest('tr').css('background','tomato'); $(el).closest('tr').fadeOut(800,function(){ $(this).remove(); }); }else{ alert('Invalid ID.'); } } }); } }); });
6. Demo
7. Conclusion
In this post, I explained how you can use AJAX to delete existing records from MySQL Table. I used it with HTML table in the demonstration you can also use it with <div>, <ul>, etc. elements.
If you found this tutorial helpful then don't forget to share.
Thanks for creating this post. And, not making it too long 🙂 I don’t write jquery often enough to not usually need a reference to see how the syntax should go. This is a great solution for exactly what I needed to do. It’s nice to find something relevant outside of Stack Overflow.
Thanks.
I can’t make … Delete clickable. Can you please explain how you make it possible?
Hi Liam,
Here, is the short explain –
On the delete button, I use the posts table “id” value to set id attribute e.g. del_1, del_2,etc.
On the button click split the id by “_” to get to delete record id (deleteid = splitid[1]) and pass in the $.ajax request. In PHP file execute DELETE query on the POST id.
thanks a lot.
Glad it was helpful.
Hey, I can delete the row from html table but not from mysql DB. What do i do ?
Hi Rishikesh,
If you are properly removing a row from HTML table means your AJAX request working fine.
Check your AJAX php file.
I have the same issue. Remove.php is set up properly and I copied your ajax code exactly but i’m not getting the $id variable through post and it isn’t removing the row from my database. Can you help?
Can you mail me your files and the database at makitweb@gmail.com so I can check it and help you to resolve the issue.
hi me iam also facing the same problem
Thanks for this tutorial. If I want to delete from multiple tables, will the other delete queries go to remove.php? And how, please?
Yes, all delete queries will be defined in remove.php file.
Thanks for this amazing tutorial.
I would like to clear one thing though, while deleting works perfectly, I was wondering if we could make the Sl no change as well instantly. It takes a refresh to update Sl no.
To update the numbering of S.no after removing record you can either send AJAX request to fetch all records and update the table or loop on the table row using jQuery to update the S.no column value.
I tried incorporating this code to my project but It is not goin well for me. I am new to Jquery, that’s probably the reason why
Can you share me your code at makitweb@gmail.com so I can check?
i cant click on delete button under operations..
Hi Anjali,
Can you share me your code at makitweb@gmail.com so I can check?
Thank you so much! This is awesome!!! You are so very generous, and I appreciate it.
You’re welcome.
Hello sir plz send me the code of this in my gmail id ‘himanisengar0@gmail.com’
Download link is available in the tutorial from where you can download.
How insert alert before delete?
Hi Suwena,
You can add your confirmation alert before sending AJAX request on Delete click.
Excellent Script . keep it up
Thanks.
I didn’t understand how much files to create, and the codes for which files are.. can you explain it please?
Hi Mariam,
You need to create 2 files – index.php and remove.php. In index.php file write HTML and jQuery code and in remove.php write PHP code.
Hi Yogesh! Great job showing and explaining how to delete a record.
Wondering if you can help? I keep getting an Invalid ID alert.
My code is here https://docs.google.com/document/d/1bDGbeh4o6PQ_cGLLll0BGevjez_-q4KjffiQwiEEanw/edit?usp=sharing
Hi Steve,
Have you checked the SELECT query in deleteRecord.php file? Is it correct?
I have the same issue. I have emailed my code
You can add if(response != null) instead of if(response == 1).
Hope will answer and solve your project
Yogesh Singh : I have renamed “remove.php” and then all failled. ¿Do you explain me please?
Thank!
Hi Luis,
Have you also updated the file name in jQuery AJAX script?
Yes Yogesh, i have renamed the script.js. file in “Ajax request url:’removedp.php’ “.
it was called before ‘removed.php’.
What else to do?
Thank!
Sorry,
it was called before ‘remove.php’.
Now the name is ‘removep’
What else to do?
Thank!
Hi Luis,
If you renamed the ‘remove.php’ then only need to update the $.ajax url: ‘removep.php’ in jQuery script.
Yogesh: Could there be another reference to the remove file?
Yogesh: A line I have added and now it works.
I share it with you.
// AJAX Request
$.ajax({
url: ‘removeproducto.php’,
cache: false, <– This is a line.
type: 'POST',
best regards
what i have to edit in ajax code if i have sno instead of id in my database
Hi Priyanshu,
You need to update the HTML and PHP script –
In HTML –
In PHP –
Working like it should, great post!!
this is what I was looking for last 2 days , Working perfectly like it should. Thanks man , big up for your script.
You’re welcome.
Big Thanks Man.
This has been giving headache for 3 days now.
I’m glad you found it useful.
Not working for me… ajax request fails. and throws the invalid id error. anu suggestions?
Works perfect. Thanks. Exactly what i wanted.