Performing MySQL queries with the help of AJAX @ Aleix Cortadellas

Performing MySQL queries with the help of AJAX

Posted on April 6th, 2009 Aleix 4 comments

Back to AJAX! This tutorial is a reply to a question posted in Experts Exchange, and therefore it has a very specific scope. However, taking into consideration it may prove to be useful to many, I decide to share it with you all.

What this tutorial explains is how to perform asynchronously MySQL actions with AJAX, using GET variables and an external PHP page. It may sound super-complex, but it is not. As usual, I provide the full code of the script and a working demo which you can see here.

You will need to create three files, the name of which appears in bold (index.html, boo.php, ajax.js). Use the code provided in each of them to build a working version of the script.

index.html
```
<html>
<head>
<script src="ajax.js"></script>
</head>
<body>

Operation:
</br>
<input type="button" name="Submit" value="INSERT" onclick="setContent('INSERT','1');">&nbsp;
<input type="button" name="Submit" value="DELETE" onclick="setContent('DELETE','1');">

_____________________
<div name="response" id="response"></div>
</body>
</html>
```
This is an easy part. I will comment a few things though…
First of all, in the example we use input buttons to trigger the function. Notice however, that this is done using onclick event. What this means is:
1. You can change the input button for a normal text link or whatever other element which accepts JavaScript events.
2. You can change the event which triggers the function, as long as the event is allowed for the type of element it is applied to. So for instance, if instead of a input button you had a text field, you might use onchange, onkeyup, etc. as trigger.
Secondly, I must talk about the function itself. See an example:

setContent(’DELETE’,'1′)

The variables in a JavaScript function are separated by commas. In index.html, what we write within the brackets are the values of the variables. The names of the variables are given in ajax.js (we will see this later on). So here we have that the first variable’s value (in our example, action to perform) is DELETE and the second variable’s value (in our example, id of the MySQL row) is 1.

You can assign those variables statically when coding (like in this example), or dynamically with the help of PHP; but this is out of the scope of this tutorial.

Thirdly, you may or you may not want to show a response to the user (e.g. success confirmation). In the example, we show the response as innerHTML of the DIV with id=”response”. You may decide not to show any response at all; in such case I suggest you add a CSS style property to that DIV to make it invisible:

<div name=”response” id=”response” style=”visibility: hidden;”>

Now let’s take the next step.

boo.php
```
<?php

// Import GET variables

$operation = $_GET['operation'];
$elementid = $_GET['elementid'];

// Take action conditionally

if($operation == 'INSERT'){

// PHP MySQL query for INSERT
// (write here the code)

} elseif($operation == 'DELETE'){

// PHP MySQL query for DELETE
// (write here the code)

}

echo 'Server response:';
echo "$operation id $elementid done!";

?>
```
This is the external PHP page, which contains the PHP code to perform the MySQL operations. You will need some PHP knowledge to complete it, as I did not write the MySQL queries in the code provided: write those which suit your needs. You will find a good tutorial on MySQL queries with PHP here.

You may rename it to something more adequate, but remember changing the name too in ajax.js, where the variables can be customised at the very beginning (see later on…).

The first part is for retrieving the GET variables sent from index.html. Remember to import all the variables you need to use in the PHP script, or they won’t be set.

The second part is a set of PHP conditions. In our example, they take different actions depending on the value of the ‘action to perform’ variable (e.g. INSERT or DELETE).

The third part is what will be returned to the user once the script is run, and will appear as innerHTML of the “response” DIV. Content is returned, as the name says, as HTML. If you don’t return any response at all, logically the DIV won’t have any content either. Whether you want the script to return a response or not, and what kind of response, depends on your preferences…

ajax.js
```
// Customise those settings

var divid = "response";
var url = "boo.php";

////////////////////////////////
//
// Setting DIV innerHTML
//
////////////////////////////////

function setContent(operation,elementid){

// The XMLHttpRequest object

var xmlHttp;
try{
xmlHttp=new XMLHttpRequest(); // Firefox, Opera 8.0+, Safari
}
catch (e){
try{
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP"); // Internet Explorer
}
catch (e){
try{
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e){
alert("Your browser does not support AJAX.");
return false;
}
}
}

// Timestamp for preventing IE caching the GET request

fetch_unix_timestamp = function()
{
return parseInt(new Date().getTime().toString().substring(0, 10))
}

var timestamp = fetch_unix_timestamp();
var nocacheurl = url+"?t="+timestamp;

// The code...

xmlHttp.onreadystatechange=function(){
if(xmlHttp.readyState!=4){

// Working
document.getElementById(divid).innerHTML='Processing...';
}
if(xmlHttp.readyState==4){

// Finished. Return response...
document.getElementById(divid).innerHTML=xmlHttp.responseText;
}
}
xmlHttp.open("GET",nocacheurl+"&operation="+operation+"&elementid="+elementid,true);
xmlHttp.send(null);
}
```
This is the tricky part, as it involves JavaScript and the mighty AJAX function. I will not extend myself explaining each line of code here; if you’ve read my other AJAX tutorials you should already be familiarised with it and have an idea of how it works. If you didn’t read them yet, then I suggest you do, as you will extend your knowledge in AJAX as well as possibly find other scripts you migh find useful.

I’m going to comment what is specific of this tutorial:
1. function setContent(operation,elementid): here we are naming the function (setContent) and naming its variables (operation, elementid). Remember in index.html I mentioned the variable values are separated by commas? When you call a JavaScript function, the first given value is assigned to the first variable named, and so on. So for setContent(’DELETE’,'1′), value of operation variable is DELETE and value of elementid variable is 1. You may add as many variables as you want, separating them with commas, and of course assigning values to them wherever you write the code to call them in index.html.
2. xmlHttp.open(”GET”,nocacheurl+”&operation=”+operation+”&elementid=”+elementid,true): you will know all the details of this line here. I will just explain a portion of it, namely the nocacheurl+”&operation=”+operation+”&elementid=”+elementid part. This is the URL of the external PHP file we’re sending the GET variables to, and which will run the script and return results (if any). nocacheurl is the URL of the external PHP page, and then some GET variables are appended to the URL. If you want to pass extra variables to the PHP script, then you need to append them here. It’s a bit difficult to explain for people who have no idea of JavaScript or GET variables, but I hope if you do have so knowledge, you know what I mean.
And that was it. You can see again the usual working demo here (for security reasons, no MySQL actions are performed, it uses the PHP code as detailed above).

I know it might be a bit complicated to customise it if you’re new to all this, but believe me, it is not as difficult as it seems and in the end it is very intuitive. Just be patient, debug a lot and learn from mistakes. This is the way pros make it to the top.

For further assistance, doubts, etc. write a comment below and I’ll be glad to help.

Happy Easter to you all, and stay tuned

Update: if you’re looking for an AJAX “Add to favourites” script using this method, click here.

AJAX, Computers & programming, HTML, JavaScript, MySQL, PHP ajax, asynchronous, database, delete, div, insert, mysql, onclick, php
4 responses to “Performing MySQL queries with the help of AJAX”
1. Steve May 20th, 2009 at 12:31
  
  Do you know how you would delete more than one item without having to refresh?
  The code works, but it only brings up the server message in the first DIV, which might leave some confusion to the user.
  
  Thanks (it’s probably me being thick!)
2. Aleix May 20th, 2009 at 12:38
  
  Hello,
  
  What do you mean deleting more than one item without refreshing? You mean with a single click? If so you could use checkboxes and then a button to sumbit via JS each checkbox id to the PHP script.
  
  If it’s not this what you mean, please specify a bit more…
  
  About the DIV message: you can place the DIV anywhere in the page for the user to see it better. You could also modify the code and use a JS alert() instead.
  
  Regards
3. deepali July 4th, 2009 at 11:53
  
  Hello,
  
  I was looking for something similar to your module. I wanted to build-up user’s favorite/unfavorite blogs with AJAX like flikrs.com has. There we can add or delete users favorite picture on that same link.
  
  could you please guide me or show me some link so with the help of that i can build up this module with the help of php/mysql.
  
  Thnaks in advance
4. Aleix July 4th, 2009 at 12:33
  
  Hello deepali,
  
  Probably this is of your interest as it addresses the same question:
  http://www.experts-exchange.com/Programming/Languages/Scripting/AJAX/Q_24297030.html
  
  I think you can make a temporary free account to see the content, which is for members only.
  
  ~~My next post will cover this topic too.~~
  
  Update: check this post:
  http://www.aleixcortadellas.com/main/2009/07/05/650/
  
  Regards,
Leave a reply

Name (required)

Mail (will not be published) (required)

Website

Proudly hosting this blog at WHB

SPECIAL OFFER
Use coupon code GONECRAZY at WHB and get 75% off when you buy a Budget or Reseller hosting package!

M	T	W	T	F	S	S
« Mar				May »
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

Performing MySQL queries with the help of AJAX

index.html

boo.php

ajax.js

4 responses to “Performing MySQL queries with the help of AJAX”

Leave a reply

Calendar

Archives

Recent Articles

Links

Categories

Meta