My Programming Tricks

Caveman Blogger

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

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

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

$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']);
  $d++;
}

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

?>

Finally here are the Javascript codes:

<!DOCTYPE html>
<html>

<script>

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 {
    console.error(xhr);
   }
  }
};
xhr.open("GET", path, true);
xhr.send();
}

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

</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.

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!

My Programming Tricks

Labels

Friday, December 25, 2015

Sending MySQL Data from PHP to Javascript via AJAX

No comments:

Post a Comment