My Programming Tricks

Caveman Blogger

Note: Please run these codes from a web host, either it is a "localhost" or remote host from a web hosting server.

This is an expansion of my earlier post with sending data from only one MySQL table. For multiple table data channeling to browser from MySQL, a query string is used to differentiate the AJAX requirement. You can also create another PHP script to access data from other tables but it seems quite unnecessary as it can be easily achieved by sending different query strings to the AJAX PHP script.

Here we go again:

USE `testDB`;
DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
`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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test2`;

CREATE TABLE `test2` (
`f` tinyint(4) DEFAULT NULL,
`g` tinyint(4) DEFAULT NULL,
`h` tinyint(4) DEFAULT NULL,
`i` tinyint(4) DEFAULT NULL,
`j` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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);
insert into `test2`(`f`,`g`,`h`,`i`,`j`) values (21,22,23,24,25),(26,27,28,29,30),(31,32,33,34,35),(36,37,38,39,40);

A new table 'test2' is added to the previous table setup (refer to my earlier post to check out the difference).

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

<?php
//I name this file ajax.php
error_reporting(0);
$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");
}
mysql_select_db($db_name);

if ($_GET['tbl'] == '1') {
  $query = mysql_query("SELECT * from test");
}
else if ($_GET['tbl'] == '2') {
  $query = mysql_query("SELECT * from test2");
}

$all = array();

$d = 0;
while ($query_result = mysql_fetch_assoc($query)) {
  if ($_GET['tbl'] == '1') {
   $all[$d] = array("a"=>$query_result['a'],"b"=>$query_result['b'],"c"=>$query_result['c'],"d"=>$query_result['d'],"e"=>$query_result['e']);
  }
  else if ($_GET['tbl'] == '2') {
   $all[$d] = array("f"=>$query_result['f'],"g"=>$query_result['g'],"h"=>$query_result['h'],"i"=>$query_result['i'],"j"=>$query_result['j']);
  }
  $d++;
}

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

?>

Finally here are the Javascript codes:

<!DOCTYPE html>
<html>

<script>

var data;

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);

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

function loadJSON2(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].f+" "+data[k].g+" "+data[k].h+" "+data[k].i+" "+data[k].j);

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

loadJSON('ajax.php?tbl=1'); // This result will be the same as in the earlier post
loadJSON2('ajax.php?tbl=2'); // tell ajax.php that $_GET['tbl'] = 2;

</script>

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

</html>

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

You can also combine loadJSON with loadJSON2 by adding one more parameter to the function. Anyway, to make explanation less confusing, I added another JSON loading function to it.

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

Caveat: The data from the second table may prompt up before the first table. If you really want to make sure the first table come first, you will need to use the timer to delay the loadJASON2 function a bit.

Thanks for viewing!

My Programming Tricks

Labels

Tuesday, December 29, 2015

Sending Multiple MySQL Table Data from PHP to Javascript via AJAX

Wednesday, October 5, 2011

About AJAX, JQUERY, JSON, Microsoft AJAX