Networking, Programming and Graphics - Tutorials
ONLINEHOWTO.net Tutorials Category

MySQL PHP AJAX Query

Type: Code Networking, Programming and Graphics - Tutorials
Networking, Programming and Graphics - Tutorials
Level: Intermediate Networking, Programming and Graphics - Tutorials Networking, Programming and Graphics - Tutorials 
Networking, Programming and Graphics - Tutorials
Date: 2008-Aug-03
Networking, Programming and Graphics - Tutorials
Visited: 31559 times
Networking, Programming and Graphics - Tutorials
Rating: Networking, Programming and Graphics - Tutorials
Networking, Programming and Graphics - Tutorials
Published: Ivory Morhuld

We will continue with our AJAX Tutorials and make small AJAX Application using AJAX PHP MySQL Query to edit data in MySQL Database.

Lets say we have some MySQL table with employs and we have to list their names in drop-down <select> menu and when we select someone from the list, personal data will be displayed in table where we can edit the data.
First we have to make HTML page where the drop-down list will be generated from MySQL Database by PHP server side and also we have to place some <div></div> tags where will display employs data.

Lets give name 'index.php' for this file.
<html>
<head>
</head>
<body>
<select>
    <option>Select employ</option>
    <?php
    mysql_connect
('localhost','user','pass');
    
mysql_select_db('employ');
    
$query="select id, name from employ order by name asc";
    
$result=mysql_query($query);
    while(list(
$id$name)=mysql_fetch_row($result)) {
        echo 
"<option value=\"".$id."\">".$name."</option>";
    }
    
?>
</select>
<div></div>
</body>
</html>
Now some explanations. Between <select onchange="display_data(this.value);"> and </select> we generate entire list with employs names by PHP MySQL query.

Now we have to put some handler which can handle change event on <select> drop down list and to call function display_data() with parameter this.value - selected value from drop down list on change event. this function return personal details for this employ and this data later will be formatted in table placed in <div id="employ_data"></div> tags.

Now lets update our index.php file with included AJAX function - display_data().
<html>
<head>
</head>
<body>
<select onchange="display_data(this.value);">
    <option>Select employ</option>
    <?php
    mysql_connect
('localhost','user','pass');
    
mysql_select_db('employ');
    
$query="select id, name from employ order by name asc";
    
$result=mysql_query($query);
    while(list(
$id$name)=mysql_fetch_row($result)) {
        echo 
"<option value=\"".$id."\">".$name."</option>";
    }
    
?>
</select>
<div id="employ_data"></div>
</body>
</html>
Like you see display_data() AJAX function uses already described method - XMLHttpRequest and server side PHP script for getting the employs data. In this PHP script we have using MySQL Query to return the data to AJAX script.

Next, when we successfully receive the data from PHP server side script we have to place this data formated in table in <div id="employ_data"></div> tags.

Now lets update our index.php file including the new display_data() AJAX function which now will place returned data PHP script in right place:
<html>
<head>
<script language="JavaScript" type="text/javascript">
function display_data(id) { 
    xmlhttp=GetXmlHttpObject();
    if (xmlhttp==null) {
        alert ("Your browser does not support AJAX!");
        return;
    } 
    var url="ajax.php";
    url=url+"?employ_id="+id;
    xmlhttp.onreadystatechange=function() {
        if (xmlhttp.readyState==4 || xmlhttp.readyState=="complete") {
            document.getElementById('employ_data').innerHTML=xmlhttp.responseText;
        }
    }
    xmlhttp.open("GET",url,true);
    xmlhttp.send(null);
}
function GetXmlHttpObject() {
    var xmlhttp=null;
    try {
        // Firefox, Opera 8.0+, Safari
        xmlhttp=new XMLHttpRequest();
    }
    catch (e) {
        // Internet Explorer
        try {
            xmlhttp=new ActiveXObject("Msxml2.XMLHTTP");
        }
        catch (e) {
            xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
        }
    }
    return xmlhttp;
}
</script>
</head>
<body>
<select onchange="display_data(this.value);">
    <option>Select employ</option>
    <?php
    mysql_connect
('localhost','user','pass');
    
mysql_select_db('employ');
    
$query="select id, name from employ order by name asc";
    
$result=mysql_query($query);
    while(list(
$id$name)=mysql_fetch_row($result)) {
        echo 
"<option value=\"".$id."\">".$name."</option>";
    }
    
?>
</select>
<div id="employ_data"><div>
</body>
</html>
Here is PHP server side script which grab the data from MySQL database. Just copy and paste:
<?php
if (is_numeric($_GET['employ_id'])) {
    
mysql_connect('localhost','user','pass');
    
mysql_select_db('employ');
    
$query="select * from employ where id=$_GET[employ_id]";
    
$result=mysql_query($query);
    
$employ=mysql_fetch_array($result);
    echo 
"<table border=\"1\">
        <tr>
            <td>Name:</td>
            <td>"
.$employ[name]."</td>
        </tr>
        <tr>
            <td>Appoint Date:</td>
            <td>"
.$employ[appoint_date]."</td>
        </tr>
        <tr>
            <td>Country:</td>
            <td>"
.$employ[country]."</td>
        </tr>
        <tr>
            <td>City:</td>
            <td>"
.$employ[city]."</td>
        </tr>
        <tr>
            <td>E-mail:</td>
            <td>"
.$employ[email]."</td>
        </tr>
        <tr>
            <td>Phone:</td>
            <td>"
.$employ[phone]."</td>
        </tr>
    </table>"
;
}
?>
Now let see how our AJAX Application works. Please select some employ from drop-down list below.
Rate this tutorial:                    
Post Comment

    • ( dansieg [ at ] yahoo . com ),

      posted on 2009-Jun-16 | 08:07:47 PM
      This is a useful tutorial
    • ( chris . r . green [ at ] hotmail . com ),

      posted on 2009-Jun-26 | 03:20:11 PM
      I can't get this to work. I have tried another example where I list each option with an associated id and it works. I need to do this. I have a list populated from a mysql table and I need it to populate fields like this example when a name is selected.
    • ( gw_jayz [ at ] yahoo . co . id ),

      posted on 2009-Jul-09 | 01:09:53 AM
      why this script does not work in my project???
      this error --> object expected

    • posted on 2009-Jul-09 | 03:35:46 AM
      Hm. I checked the script and everything is fine. Check the html source for:

      1. Correct syntax of copied JS function .
      2. Correct connection and query to MySQL Data
      3. Correct values in <option value=
    • ( flashtoons [ at ] ymail . com ),

      posted on 2009-Jul-15 | 02:40:06 AM
      It's fantastic and amazing, Thanks for showing this amazing magic of ajax
    • ( fin_avatar [ at ] plasa . com ),

      posted on 2009-Jul-28 | 08:01:31 PM
      ok,,
    • ( x [ at ] x . com ),

      posted on 2009-Aug-12 | 10:43:58 PM
      Thanks for sharing. The function GetXmlHttpObject() is forgotten? Below is its definition:
      function GetXmlHttpObject() {
      var xmlhttp=null;
      try {
      //If the Javascript version is greater than 5.
      xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
      alert ("You are using Microsoft Internet Explorer.");
      return xmlhttp;
      } catch (e) {
      //If not, then use the older active x object.
      try {
      //If we are using Internet Explorer.
      xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
      alert ("You are using Microsoft Internet Explorer");
      return xmlhttp;
      } catch (E) {
      //Else we must be using a non-IE browser.
      //xmlhttp = false;
      //If we are using a non-IE browser, create a javascript instance of the object.
      if (typeof XMLHttpRequest != 'undefined') {
      xmlhttp = new XMLHttpRequest();
      alert ("You are not using Microsoft Internet Explorer");
      return xmlhttp;
      }

      }
      }
      }

    • posted on 2009-Aug-13 | 12:47:13 AM
      Thanks for the note. We will correct this.
    • ( norasykin_mz [ at ] yahoo . com . my ),

      posted on 2009-Sep-14 | 08:24:57 PM
      hi there, where i need to paste the PHP server side script ? Thanks

    • posted on 2009-Sep-15 | 05:31:07 AM
      You have to paste server side PHP script in file named ajax.php. This file have to be placed in the same directory where is located your start php file like shown in the source code.
    • ( vanyrow [ at ] gmail . com ),

      posted on 2009-Sep-28 | 06:27:14 AM
      thank you,,,,,,,,,,,,,,,,,,
    • ( norasykin_mz [ at ] yahoo . com . my ),

      posted on 2009-Oct-01 | 07:28:52 PM
      Do you have an idea how these employ details could be display if there are more than 1 person name John (maybe John Smith, John Doe, John Micheal)? I'm trying to have a drop-down menu that depends on their family name and then will display all records maybe 2, 3 or more with a same family name.

    • posted on 2009-Oct-01 | 11:13:09 PM
      You just have to describe this names (even with same family name) in `name` filed in your database and all other way is the same like here.

      I will add some other persons with same first names to show you.
    • ( norasykin_mz [ at ] yahoo . com . my ),

      posted on 2009-Oct-11 | 09:10:46 PM
      thanks ivory. But could it be 'Adam' or 'Maria' only in the drop-down list? Then the table will show 2 records which are Adam Filshvili & Adam Rodriges with their details. Thanks again.

    • posted on 2009-Oct-12 | 02:30:12 AM
      Yes. You can show only the first name, but if in database have two persons with same first names, how you can distinguish two persons in dropdown menu only by first name?

      But, however you want ot show only the first name, and entire name (first, middle and last) is in one database field and they are separated by space:

      $first_name=substr($name, 0, strpos($name, ' '));
    • ( gytf [ at ] ooo . com ),

      posted on 2009-Oct-22 | 12:59:48 PM
      really nice site

    • posted on 2009-Nov-03 | 04:15:52 AM
      thx :)
    • ( mmkanta . 2008 [ at ] gmail . com ),

      posted on 2009-Nov-04 | 11:17:32 PM
      I have tried to used your code but unable to get the result what you got. I would appreciate you if you explain breifly about the code you have written.

      Any way thanks for your code.
    • ( woz [ at ] gmail . com ),

      posted on 2009-Dec-09 | 07:30:20 PM
      Works like a charm, thanks
    • ( mark_z [ at ] hsw . com ),

      posted on 2009-Dec-09 | 07:31:03 PM
      Works Perfectly!! :)
    • ( tangkay . rommel [ at ] gmail . com ),

      posted on 2010-Feb-10 | 10:11:06 PM
      okay ra man..no problem about its functionality.
    • ( a . izadpanah [ at ] yahoo . com ),

      posted on 2010-Feb-16 | 10:37:21 AM
      tnx alot
    • ( ronit . kintu [ at ] hotmail . com ),

      posted on 2010-Apr-02 | 06:14:17 AM
      The example is good one. if it with Demo than It is more useful Example.

    • posted on 2010-Jun-17 | 12:29:32 AM
      the scripts working but then the table showing the detail is not appear...how??

    • posted on 2010-Jun-23 | 06:39:43 AM
      I don't understand you.
      The demo works fine.
    • ( matt . smith [ at ] segana . co . uk ),

      posted on 2010-Jun-24 | 02:22:32 AM
      This was exactly what I'd been looking for!! Thanks! Was able to easily adapt it to my own requirements.

      Oh and I think Nora wanted to display multiple records at the same time based on the name selected.

      I was able to do this easily by putting the <table> inside a while loop:

      $query="select * from employ where id=$_GET[employ_id]";
      $result=mysql_query($query);
      while ($employ=mysql_fetch_array($result)) {

      echo "<table border=\"1\">
      <tr>
      <td>Name:</td>
      <td>".$employ[name]."</td>
      </tr>
      <tr>
      <td>Appoint Date:</td>
      <td>".$employ[appoint_date]."</td>
      </tr>
      <tr>
      <td>Country:</td>
      <td>".$employ[country]."</td>
      </tr>
      <tr>
      <td>City:</td>
      <td>".$employ[city]."</td>
      </tr>
      <tr>
      <td>E-mail:</td>
      <td>".$employ[email]."</td>
      </tr>
      <tr>
      <td>Phone:</td>
      <td>".$employ[phone]."</td>
      </tr>
      </table>";
      }
    • ( joni . kuku [ at ] yahoo . com ),

      posted on 2010-Jun-27 | 11:30:36 PM
      Hi, the scripts work beautiful, but what if I wanna displaying data drom 3 drop down box/list menu?such as Name, month and city?

      Best Regards,

    • posted on 2010-Jun-28 | 03:08:59 AM
      Hi Jack,

      In your case you have to prepare second "display_data()" function which have to be handled on onchange event when displaying second drop-down.

      This function have to make a GET to another IF in ajax.php server side where needed data for you will be served.
    • ( mailamreet [ at ] gmail . com ),

      posted on 2010-Aug-16 | 11:23:39 PM
      Nice Contents
    • ( gb [ at ] gblom . se ),

      posted on 2010-Sep-01 | 08:07:37 AM
      Works nice except the swedish letters ÅÄÖ will not display OK, What to do?
      I can't figure out what to do. I have put "<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
      " in both pages.
Need a specific tutorial? Do not hesitate and submit a request!
Your e-mail: