Are you Ajax newbie? This post explains how to remove record from database tables using Ajax and PHP.
My friend William asked to me to write a post for "beginners" about how to use prototype.js framework an PHP to delete a record from a database with Ajax. This is a simple overview with basic features you can use and improve in your web projects.
Tutorial's structure includes three files:
- index.php (with a list of record you want to delete)
- delete.php (PHP code to delete records into your database)
- prototype.js (to enable Ajax)
Download prototype.js and create a new page (index.php). Add this line of code in the <head> tag on index.php to include prototype framework:
<script type=""text/javascript" src="prototype.js"></script>
Step 2: HTML code
Image to have the following code in index.php:
<ul>
<li>Jack Bauer <a href="#" onClick="deleteUser(1)">delete</a></li>
<li>Gregory House <a href="#" onClick="deleteUser(2)"> delete</a></li>
<li>Fox Mulder <a href="#" onClick="deleteUser(1)">delete</a></li>
</ul>
<li>Jack Bauer <a href="#" onClick="deleteUser(1)">delete</a></li>
<li>Gregory House <a href="#" onClick="deleteUser(2)"> delete</a></li>
<li>Fox Mulder <a href="#" onClick="deleteUser(1)">delete</a></li>
</ul>
...a simple list with names and a link to delete the user from the DB. Each link call a JavaScript function deleteUser() with argument the ID (primary key) of the user you want to delete. Database table USER has some attributes and a primary key (id_user_pk). You can generate dinamically this list using a for example a code like this:
<?php
/* Database connection */
include('config.php');
$getUser_sql = 'SELECT * FROM USER';
$getUser = mysql_query($getUser_sql);
?>
<ul>
<?php while ($row = mysql_fetch_array($getUser)) {?>
<li> <?php echo $row['name']; ?> <a href="#" onClick="deleteUser(<?php echo $row['id_user_pk']; ?>)">delete</a></li>
} ?>
<>
/* Database connection */
include('config.php');
$getUser_sql = 'SELECT * FROM USER';
$getUser = mysql_query($getUser_sql);
?>
<ul>
<?php while ($row = mysql_fetch_array($getUser)) {?>
<li> <?php echo $row['name']; ?> <a href="#" onClick="deleteUser(<?php echo $row['id_user_pk']; ?>)">delete</a></li>
} ?>
<>
Take a look here to see an explanation of connection parameters to a MySQL database in config.php.
Step 3: delete.php
Create a new page (delete.php). This page contains a query PHP to delete the record passed in argument from javascript function deleteUser(id):
<?php
/* Database connection */
include('config.php');
if(isset($_POST['user_id'])){
$userID = $_POST['user_id'];
$sql = 'DELETE FROM USER where id_user_pk ="'.$userID.'"';
mysql_query($sql);
} else { echo '0'; }
?>
/* Database connection */
include('config.php');
if(isset($_POST['user_id'])){
$userID = $_POST['user_id'];
$sql = 'DELETE FROM USER where id_user_pk ="'.$userID.'"';
mysql_query($sql);
} else { echo '0'; }
?>
Step 4: Javascript function deleteUser(id)
This function pass with the method POST to the page delete.php the id of the user you want to delete from the table. Add the following lines of code below the code in step 2:
<script type="text/javascript">
function deleteUser(id){
new Ajax.Request('delete.php', {
parameters: $('idUser'+id).serialize(true),
});
}
</script>
function deleteUser(id){
new Ajax.Request('delete.php', {
parameters: $('idUser'+id).serialize(true),
});
}
</script>
It's all. I hope it's clear :)
I am 
It is not working in my case
i want to pass the name and not not the id
anonymous: if you want to pass a string and not a number use: deleteuser("username") instead of deleteUser(id) in javascript function.
In any case, for this kind of operation (delete record) is better use an ID (primary key) and not a name. In fact if you have two user with the same name when you pass the name in the javascript function it will remove both! So I suggest you to use primary key to do this actions!
can u show how to add and edit data... and it will reflect the page accordingly
Hi Antonio,
Nice script!
Can you paste the Sql example ?
Just for extra guide :)
Tnx
Sorry - how does $('idUser'+id), in the Ajax.Request ctor, get translated into the 'user_id' request parameter that delete.php is looking for?
Its not working for me either. I get this error:
$("idUser" + id) has no properties
I've followed the tutorial as you said it.
Any ideas? Thanks :)
hi, even it didnt work with me. bit confused with the $('idUser'+id) and the user_id in delete.php. am struck here can anyone please help me
The JS input should be secured for MySQL injection ! either with intval() or mysql_real_escape_string()
Thank-you for the tutorial! I was able to get it working with the following javascript code:
function deleteUser(id){
new Ajax.Request('delete.php', {
parameters: $('idUser'+id).serialize(true),
});
}
function deleteUser(id){
var pars = 'user_id=' + id ;
new Ajax.Updater( 'answers-area', 'delete.php', { method: 'post', parameters: pars } );
}
How can i refresh the table listing records after deleting data.