Using JSON with PHP, Javascript, and MySQL

This document introduces the use of JSON with PHP and Javascript for transferring data with MySQL

Andrew L. Mackey

Overview

JavaScript Object Notation (JSON) is commonly used with a variety of applications to exchange data between an application or webpage and servers. It is lightweight compared to its XML counterpart. It is a text-based representation of structured data that conforms to the Javascript object syntax. In this document, a way to access data that is stored in a MySQL table from a server equipped with PHP from a client (web, mobile, etc.) using Javascript is presented.

MySQL Table

The first step is to create a table named STUDENTS within MySQL and insert a few records into the table.

CREATE TABLE STUDENTS (
	STUDENT_ID      INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	STUDENT_FNAME   VARCHAR(30),
	STUDENT_LNAME   VARCHAR(30),
	STUDENT_EMAIL   VARCHAR(50),
	STUDENT_MAJOR   VARCHAR(15)
);

INSERT INTO STUDENTS VALUES( DEFAULT, 'Sue', 'Jones', 'sjones@uafs.edu', 'CS' );
INSERT INTO STUDENTS VALUES( DEFAULT, 'Tim', 'Smith', 'tsmith@uafs.edu', 'MATH' );
INSERT INTO STUDENTS VALUES( DEFAULT, 'Jan', 'Lee',   'jlee@uafs.edu',   'CS' );

PHP Source Code

The next step is to write a script in PHP that will query the database, construct an array of records for the output, and encode the array of results into a JSON document. The JSON document is then sent to the requesting client. The page will be saved as json.php.

<?php


try {
	/******************************************
	*  Database Connection Information
	*******************************************/
	$dbhost = "localhost";
	$dbuser = "amusername";
	$dbpass = "supersecretpass";
	$db     = "mydatabase";

	$sql = "SELECT STUDENT_ID, STUDENT_FNAME, STUDENT_LNAME, STUDENT_EMAIL, STUDENT_MAJOR FROM STUDENTS";

	/******************************************
	*  Connect to the database
	*******************************************/
	$con = new mysqli($dbhost, $dbuser, $dbpass, $db);


	/******************************************
	*  Query the database
	*******************************************/
	$stmt = $con->prepare($sql);
	$stmt->execute();
	$rs = $stmt->get_result();



	$output = array();		// this will hold each record as we iterate

	while ( $row = $rs->fetch_assoc() ) {
		$output[] = $row;
	}

	$con->close();	// do not forget to close your connection


	/******************************************
	*  Output the results as JSON document
	*******************************************/
	echo json_encode($output);


} catch(mysqli_sql_exception $ex) {
	echo "Error Code:  " . $ex->getCode() . "\n<br>";
	echo "Exception: " . $ex->getMessage();
	exit();
}

?>

If you point a web browser to the web server running this page, you should see the following output:

[
 {"STUDENT_ID":1,"STUDENT_FNAME":"Sue","STUDENT_LNAME":"Jones","STUDENT_EMAIL":"sjones@uafs.edu","STUDENT_MAJOR":"CS"},
 {"STUDENT_ID":2,"STUDENT_FNAME":"Tim","STUDENT_LNAME":"Smith","STUDENT_EMAIL":"tsmith@uafs.edu","STUDENT_MAJOR":"MATH"},
 {"STUDENT_ID":3,"STUDENT_FNAME":"Jan","STUDENT_LNAME":"Lee","STUDENT_EMAIL":"jlee@uafs.edu","STUDENT_MAJOR":"CS"}
]

HTML/Javascript Source Code

The last step is to create an HTML page with Javascript that will call the remote server and obtain the JSON document asynchronously and inserts them into an existing HTML element.

<html>

	<head>
		<style>
			table, th, td {
				border: 1px solid black;
				border-collapse: collapse;
			}
			th, td {
				padding: 10px;
			}
		</style>

		<script>

			function fetchData() {

				var x = new XMLHttpRequest();
				x.onreadystatechange = function() {
						if( this.status = 200 && this.readyState == 4 ) {
							var results = JSON.parse(this.responseText);


							var mytable = document.getElementById("amtable");
							for ( var i in results ) {
								var row = mytable.insertRow(-1);  //append row to end
								var cell1 = row.insertCell(0);
								var cell2 = row.insertCell(1);
								var cell3 = row.insertCell(2);

								cell1.innerHTML = results[i].STUDENT_ID;
								cell2.innerHTML = results[i].STUDENT_FNAME
												+ " "
													+ results[i].STUDENT_LNAME;

								cell3.innerHTML = results[i].STUDENT_EMAIL;


							}
							alert(output);
						}
				};

				x.open("GET", "json.php");
				x.send();
			}

		</script>

	</head>


	<body>

		<input type="button" value="Get Data from Server" onclick="fetchData()">

		<p> </p>

		<table id="amtable">
			<tr>
				<td>Student ID</td>
				<td>Student Name </td>
				<td>Student Email</td>
			</tr>
		</table>


	</body>


</html>