Friday, December 25, 2015

Sending MySQL Data from PHP to Javascript via AJAX

This is done through AJAX. It should be well supported by most browsers including IE. Everything is quite straight forward. First you need to set up a dummy MySQL database to test out the following javascripts. Here is the SQL to set the database up:

USE `testDB`;

 `a` tinyint(4) DEFAULT NULL,
 `b` tinyint(4) DEFAULT NULL,
 `c` tinyint(4) DEFAULT NULL,
 `d` tinyint(4) DEFAULT NULL,
 `e` tinyint(4) DEFAULT NULL

insert into `test`(`a`,`b`,`c`,`d`,`e`) values (1,2,3,4,5),(6,7,8,9,10),(11,12,13,14,15),(16,17,18,19,20);

The above SQL uses a database called 'testDB' with a table named 'test'. The 'test' table came with 5 columns namely 'a', 'b', 'c', 'd' and 'e'. Four rows of data are inserted with number incremented by one starting from 1. The first row contains 1, 2, 3, 4 and 5. The second row starts from 6 until 10 and so forth.

Here are the PHP codes for AJAX (or act as a JSON data generator):

 //I name this file ajax.php
 $db_name = "testDB";
 $db_server_name = "localhost";
 $db_usr_name = "xxxx"; // Please replace xxxx with your MySQL username
 $db_pw = "xxxxxxxx"; // Please replace xxxxxxxx with the password that came with it

 $dblink = mysql_connect($db_server_name, $db_usr_name, $db_pw);

 if (!$dblink) {
  array_push($error, "Failed to Connect to Database");

 $query = mysql_query("SELECT * from test");

 $all = array();

 $d = 0;
 while ($query_result = mysql_fetch_assoc($query)) {
  $all[$d] = array("a"=>$query_result['a'],"b"=>$query_result['b'],"c"=>$query_result['c'],"d"=>$query_result['d'],"e"=>$query_result['e']);

 $encoded = json_encode($all);
 header('Content-type: application/json');


Finally here are the Javascript codes:

<!DOCTYPE html>


var data;
var saveArr1 = new Array();
var saveArr2 = new Array();

function loadJSON(path) {

 var xhr = new XMLHttpRequest();
 xhr.onreadystatechange = function() {
  if (xhr.readyState === 4) {
   if (xhr.status === 200) {
    try {
     data = JSON.parse(xhr.responseText);

     for (var k in data) {

      alert(data[k].a+" "+data[k].b+" "+data[k].c+" "+data[k].d+" "+data[k].e);

      // saveArr1.push(k); // if you want to save the array key to an array called saveArr1
      // saveArr2.push(data[k].b); // if you want to save the array data column 'b' to an array called saveArr2

     // alert(data.length); // if you want to check the total length

    catch(e) {
     alert("Data Error. Please contact the administrator.");
   else {
 };"GET", path, true);

loadJSON('ajax.php?set=1'); // '?set=1' is optional and your PHP codes will receive this parameter as $_GET['set'] in PHP


The data from MySQL will prompt out as alerts, row by row.


Please note that the DOCTYPE declaration is important for IE browsers as it puts it into standards mode for AJAX to work.

After running the codes, you will get four alerts with the first time being '1 2 3 4 5' followed by '6 7 8 9 10' and so forth. Here is a screen shot of the first prompt running in Chrome:

Please let me know if you encounter any problem with these codes. I will be happy to answer any questions that you may have. Enjoy!

No comments:

Post a Comment