Saturday, April 6, 2013

Using Ajax to communicate MySQL database in server



In JavaScript, it is difficult to communicate MySQL in server. Below is the example to use
  Ajax to communicate MySQL database in server.
Suppose we have a button, when users click the button, JavaScript function  getEnrollmentData() is called and student enrollment list in MySQL database is displayed.
<input type="button" onclick="getEnrollmentData();" name="get_enroll_data" id="get_enroll_data" value="Select the modules in which this user is enrolled" class="input-button" />
In JavaScript (jQuery library is needed), first we  defined a AJAX event
var enrollData_Ajax = new Ajax();
If AJAX request is successful, we call  function selectModules
Event.attachListener(enrollData_Ajax, 'ajaxSuccess', selectModules);
If AJAX request fails, we call  function selectModules
 Event.attachListener(enrollData_Ajax, 'ajaxFailure', showError);
Now we can use Ajax in  getEnrollmentData()
function getEnrollmentData(){
   enrollData_Ajax.request('/_lib/xml_user_enroll_data.php?user_id='+document.getElementById('user').value);
}

Here we communicate to MySQL database in Server using xml_user_enroll_data.php and get data in XML format.
xml_user_enroll_data.php
<?php
   header('Expires: 0');
   header('Pragma: no-cache');
   header('Cache-Control: no-cache, must-revalidate, post-check=0, pre-check=0');
   validate_cesei_access(STATUS_INSTRUCTOR);
   header('Content-Type: application/xml');
   echo('<?xml version="1.0" encoding="UTF-8"?>');

$user_id = intval($_GET['user_id']);
$module_id = intval($_GET['module_id']);
if($user_id){
   $sql = "SELECT course_id AS ed FROM edu_course_enrollment WHERE member_id = $user_id";
}else{
   $sql = "SELECT member_id AS ed  FROM edu_course_enrollment WHERE course_id = $module_id ";
}
$result = mysql_query($sql,$db);
echo('<e_data>');
echo('<enrollment>0</enrollment>'); //make sure its always an array
while($row = mysql_fetch_assoc($result)){
   echo('<enrollment>'.$row['ed'].'</enrollment>');
}
echo('</e_data>');
?>

If ajax request is successful, we call  function selectModules, which gets XML data from AJAX
    userData = new Object();
     userData = XMLObject.toJS(enrollData_Ajax.getXML()).e_data;


No comments:

Post a Comment