Ajax Add & Delete MySQL records using jQuery & PHP
If you are bit familiar with basics of jQuery JavaScript library, next thing you want to do is learn jQuery Ajax to add and delete records from MySQL database table. In this basic tutorial we are going to do the same, we will focus on learning jQuery to send POST data to a PHP file and respond back with results or errors. Without having to reload the page, the Ajax technique is a very neat way to add or delete / database records, also it looks very cool.
In this tutorial, I have created three files index.php, response.php and config.php. Front page (index.php) is the only file where user directly interacts with your application. response.php runs behind the scene, it has no visual contact with user and responds obliquely.
Let’s start by creating a MySQL table called add_delete_record. You can run SQL query below in your phpMyAdmin to create the table for you automatically.
CREATE TABLE IF NOT EXISTS `add_delete_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` text NOT NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1 ;Configuration
Configuration file connects to specified database using MySQL credentials provided, the file is required in index.php and response.php. Edit config.php file to replace variables with your own MySQL username, pass and database name.
<?php
########## MySql details (Replace with yours) #############
$username = "xxxx"; //mysql username
$password = "xxxx"; //mysql password
$hostname = "localhost"; //hostname
$databasename = 'testing'; //databasename
//connect to database
$mysqli = new mysqli($hostname, $username, $password, $databasename);
?>Front Page
We’ll be using jQuery $.ajax method to make Ajax calls to response.php, if you examine jQuery code below, you can see that there are two click methods are used to make Ajax calls; .click() and .on().
The .click() and .on() method work same way, both get triggered when mouse button is pressed and released, the difference is that the .on() also attaches an event handler to the elements, for example: a button element loaded via Ajax request does not work with .click method, to make it work we must use .on() method to attach element to event handler. (For Older jQuery versions .live or .delegate() must be used instead). You can find additional documentation here.
$(document).ready(function() {
//##### send add record Ajax request to response.php #########
$("#FormSubmit").click(function (e) {
e.preventDefault();
if($("#contentText").val()==='')
{
alert("Please enter some text!");
return false;
}
$("#FormSubmit").hide(); //hide submit button
$("#LoadingImage").show(); //show loading image
var myData = 'content_txt='+ $("#contentText").val(); //build a post data structure
jQuery.ajax({
type: "POST", // HTTP method POST or GET
url: "response.php", //Where to make Ajax calls
dataType:"text", // Data type, HTML, json etc.
data:myData, //Form variables
success:function(response){
$("#responds").append(response);
$("#contentText").val(''); //empty text field on successful
$("#FormSubmit").show(); //show submit button
$("#LoadingImage").hide(); //hide loading image
},
error:function (xhr, ajaxOptions, thrownError){
$("#FormSubmit").show(); //show submit button
$("#LoadingImage").hide(); //hide loading image
alert(thrownError);
}
});
});
//##### Send delete Ajax request to response.php #########
$("body").on("click", "#responds .del_button", function(e) {
e.preventDefault();
var clickedID = this.id.split('-'); //Split ID string (Split works as PHP explode)
var DbNumberID = clickedID[1]; //and get number from array
var myData = 'recordToDelete='+ DbNumberID; //build a post data structure
$('#item_'+DbNumberID).addClass( "sel" ); //change background of this element by adding class
$(this).hide(); //hide currently clicked delete button
jQuery.ajax({
type: "POST", // HTTP method POST or GET
url: "response.php", //Where to make Ajax calls
dataType:"text", // Data type, HTML, json etc.
data:myData, //Form variables
success:function(response){
//on success, hide element user wants to delete.
$('#item_'+DbNumberID).fadeOut();
},
error:function (xhr, ajaxOptions, thrownError){
//On error, we alert user
alert(thrownError);
}
});
});
});Here’s how we list the initial records from the database using PHP.
<?php
//include db configuration file
include_once("config.php");
//MySQLi query
$results = $mysqli->query("SELECT id,content FROM add_delete_record");
//get all records from add_delete_record table
while($row = $results->fetch_assoc())
{
echo '
<li id="item_'.$row["id"].'">';
echo '
<div class="del_wrapper"><a href="#" class="del_button" id="del-'.$row["id"].'">';
echo '<img src="images/icon_del.gif" border="0" />';
echo '</a></div>
';
echo $row["content"].'</li>
';
}
//close db connection
$mysqli->close();
?>As you can see we have some HTML input fields after the PHP code, which we will use to collect user input values which will be sent to the server. The loading image is set to “display:none“, it will remain hidden until user clicks submit button.
<div class="form_style">
<textarea name="content_txt" id="contentText" cols="45" rows="5"></textarea>
<button id="FormSubmit">Add record</button>
<img src="images/loading.gif" id="LoadingImage" style="display:none" />
</div>
Returning the Data
The response.php returns results according to the different Ajax request users make. response.php connects to database, add/delete records and return success / error messages. Since all these processing takes place on the server, we do not need any jQuery codes here.
In response.php, the type of request is determined by the POST variables sent by index.php. If content_txt POST variable is set, PHP code adds a new record and returns the result, if it is a delete request, it deletes selected record from the database.
In case of errors, we will just output PHP header 500 errors like this : header(‘HTTP/1.1 500 Error Message!’);. When we send this error message, Ajax response handler in index.php uses the HTTP status code to check if there was an error and alerts user with the message.
<?php
//include db configuration file
include_once("config.php");
if(isset($_POST["content_txt"]) && strlen($_POST["content_txt"])>0)
{ //check $_POST["content_txt"] is not empty
//sanitize post value, PHP filter FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH Strip tags, encode special characters.
$contentToSave = filter_var($_POST["content_txt"],FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);
// Insert sanitize string in record
$insert_row = $mysqli->query("INSERT INTO add_delete_record(content) VALUES('".$contentToSave."')");
if($insert_row)
{
//Record was successfully inserted, respond result back to index page
$my_id = $mysqli->insert_id; //Get ID of last inserted row from MySQL
echo '
<li id="item_'.$my_id.'">';
echo '
<div class="del_wrapper"><a href="#" class="del_button" id="del-'.$my_id.'">';
echo '<img src="images/icon_del.gif" border="0" />';
echo '</a></div>
';
echo $contentToSave.'</li>
';
$mysqli->close(); //close db connection
}else{
//header('HTTP/1.1 500 '.mysql_error()); //display sql errors.. must not output sql errors in live mode.
header('HTTP/1.1 500 Looks like mysql error, could not insert record!');
exit();
}
}
elseif(isset($_POST["recordToDelete"]) && strlen($_POST["recordToDelete"])>0 && is_numeric($_POST["recordToDelete"]))
{ //do we have a delete request? $_POST["recordToDelete"]
//sanitize post value, PHP filter FILTER_SANITIZE_NUMBER_INT removes all characters except digits, plus and minus sign.
$idToDelete = filter_var($_POST["recordToDelete"],FILTER_SANITIZE_NUMBER_INT);
//try deleting record using the record ID we received from POST
$delete_row = $mysqli->query("DELETE FROM add_delete_record WHERE id=".$idToDelete);
if(!$delete_row)
{
//If mysql delete query was unsuccessful, output error
header('HTTP/1.1 500 Could not delete record!');
exit();
}
$mysqli->close(); //close db connection
}
else
{
//Output error
header('HTTP/1.1 500 Error occurred, Could not process request!');
exit();
}
?>
