JavaScript: Client-Server with JSON, PHP & MySQL

JavaScript See & Try the Code

Introduction

Learn to implement two JSON client-servers with JavaScript, PHP and MySQL. This page includes explanation, source code and examples. The examples display information about animals in South Dakota, in response to one tap of a button, a menu selection, or you can see the entire table of animals at South Dakota Animals.

We'll start with a very simple PHP server that returns data in JSON format, for just one animal, when you tap a button. Next you can tap a button or select animals from a menu to see information returned from a MySQL database.

Learn two JSON techniques developers can use to send and retrieve data from a server. The first technique hard codes JSON values for return to the client. The second technique obtains JSON values from a MySQL table, to return to the client.

MySQL

For this code to work you'll need PHP enabled and a MySQL table named AnimalsSD. The table uses four columns called, name, region, image, and imglnk, of type varchar 256. Plus one column population, of type integer. I also added a unique index for each column, although that's not necessary.

However you can modify the code and use it for any suitable table. I was very happy to see how easy it is to return JSON to JavaScript from a MySQL database.

The following screen shot's from phpMyAdmin, of the AnimalsSD table.

JSON Client-Server Examples

Tap a button or select a menu item, to see data from a server. These examples require an active PHP Web server to execute. You may copy the files to your Web server if necessary.

Tap: Requests Bison Information from a JSON Server.

Tap: Requests Pronghorn Information from MySQL and JSON.

Select an Animal from the Menu

Pronghorn Bison Prairie Dog Mountain Lion

The response should be, Unable to connect to server.. This example works when you include your database connection information in a private file. The listing below includes database connection files. Substitute digits for n and your user name, database name and password, for the following fields.

Private File for Database Connection

<?php
$hostname = "nnn.nnn.nn.nn";
$username = "your_name";
$dbname = "your_database";
$password="your_password";
?>

<div 
id="jGet" 
class="box aq"  
onclick="getSingle()">
Tap: Requests Bison Information from a JSON Server.
</div>

<div id="jSend" 
class="box peach" 
onclick="getAnimalInfo('Pronghorn')">
Tap: Requests Pronghorn Information from MySQL and JSON.
</div>

<h4>
Select an Animal from the Menu
</h4>

<select 
class="blk w100" 
id="json" 
onchange="getAnimal('json');">

<option 
value='Pronghorn'          
onclick="getAnimal('json');">
Pronghorn
</option>

<option 
value='Bison'              
onclick="getAnimal('json');">
Bison
</option>

<option 
value='Prairie Dog'        
onclick="getAnimal('json');">
Prairie Dog
</option>

<option 
value='Mountain Lion'      
onclick="getAnimal('json');">
Mountain Lion
</option>

</select>

<img 
id="ePicture" 
class="ex" 
src="thumbnails/ss-graphics-here.png" 
title="Graphics Display Here"
>

var ePicture = null;

/**
* Obtain the IMG to
* display all images.
*/
function getPictureElement(){
if(ePicture == null){
ePicture = document.getElementById('ePicture');
}
}

/**
* Just call a single response
* simple PHP server, json-server.php.
* See json-server.php below.
*/
function getSingle(){
getPictureElement();

// Create a download request.
const xmlhttp = new XMLHttpRequest();

// When the download completes:
xmlhttp.onload = function() {

// Convert server text to JSON:
const animalObj = JSON.parse(this.responseText);

// Obtain HTML element for
// information display:
let jGet = document.getElementById('jGet');

// Display the name, region and
// population for one animal:
jGet.innerHTML =  "The number of "+animalObj.name;
jGet.innerHTML += " near "+animalObj.region;
jGet.innerHTML += " is approximately "+animalObj.pop+".";

// Display the animal's photo.
ePicture.src = animalObj.image;
}

// Send the request to json-server.php:
xmlhttp.open("GET", "json-server.php");
xmlhttp.send();
}


/**
* Process send request for 
* information about one animal.
* Display received information.
* @param a: string name of animal.
*/
function getAnimalInfo(a){

// Obtain IMG element:
getPictureElement();

// Convert animal name
// to JSON format:
const dbParam = JSON.stringify(a);

// Prepare a download request:
const xmlhttp = new XMLHttpRequest();

// After download process
// the received data:
xmlhttp.onload = function() {

// Convert received data to JSON format:
const animalObj = JSON.parse(this.responseText);

// Obtain HTML element for text display:
let jSend = document.getElementById('jSend');

// Display the name, region and
// population for one animal:
let name = animalObj.name;
if(name == "Prairie Dog"){
jSend.innerHTML =  "The number of Prairie Dogs";
jSend.innerHTML += " living on "+animalObj.region;
}

else if (name == "Mountain Lion"){
jSend.innerHTML =  "The number of Mountain Lions";
jSend.innerHTML += " near "+animalObj.region;
}

else{
jSend.innerHTML =  "The number of "+animalObj.name;
jSend.innerHTML += " near "+animalObj.region;
}

let pop = animalObj.pop;
if (pop != 0){
jSend.innerHTML += " is approximately "+pop+".";
}
else{
jSend.innerHTML += " is unknown.";
}

// Display the image:
ePicture.src = "assets/"+animalObj.image;
}

// Send the request to
// animals-json-sd.php
// Prepend URL with the animal name:
// animals-json-sd.php?"+a
xmlhttp.open("GET", "animals-json-sd.php?"+a);
xmlhttp.send();
}

/**
* Respond to JavaScript
* selection of one menu item.
* @param n: Menu ID.
*
* Obtain the value selected.
* Call getAnimalInfo(s)
* displayed above.
*/
function getAnimal(n) { 
var s = document.getElementById(n).value; 
getAnimalInfo(s); 
}

PHP

PHP for Aqua Box

This first PHP file, named json-server.php, simply sends information about American Bison, to the client. Tap the aqua box, above, to see the results.

Create an object with fields for name, population, region and a path to the image file. Convert the data to JSON format with json_encode(...). Echo displays the end result, which is also the data received by the client.

See the echoed information at json-server.php.

<?php
$animalObj->name = "American Bison";
$animalObj->pop = 1500;
$animalObj->region = "Custer State Park, South Dakota";
$animalObj->image ="assets/bison.jpg";
$animalJSON = json_encode($animalObj);
echo $animalJSON;
?>

PHP for Peach Colored Box & Menu

When you tap the peach colored box or select a menu item, JavaScript sends a request for the selected animal to the PHP file named animals-json-sd.php.

The peach colored box sends a request for Pronghorn only. The menu sends a request for the item you select. They both send the request to the same PHP file animals-json-sd.php, displayed below.

The function getSQL() opens a connection to the database for MySQL table AnimalsSD. Most of the work's accomplished within the file functions.php, covered below.

<?php
include ('functions.php');
getPutData();
getSQL("AnimalsSD");
?>

PHP to display HTML Table

The following PHP listing displays an HTML table from a MySQL table named AnimalsSD. See the table at South Dakota Animals.

Most of the work's accomplished within the file functions.php, covered below.

<?php
include ('functions.php');
showHeader("South Dakota Animals");
getPutData();
getSQL("AnimalsSD");
showFooter();
?>

Functions PHP

File functions.php accomplishes one of two tasks, when executed.

• Return Information for one animal in JSON format
• Return Information for all animals in an HTML table format

File functions.php accesses a MySQL database to obtain information about one or all animals in the AnimalsSD table. If the URL's appended with an animal name, then just return JSON data for one animal. Otherwise display up to ten rows of the MySQL table, AnimalsSD. See the entire table of South Dakota Animals.

<?php
// Data for connection
// to database.
// You'll need your
// own file with values for:
// $hostname  = "nnn.nnn.nn.nn";
// $username  = "your_name";
// $dbname    = "your_database";
// $password  = "your_password";
include ('encrypted.php');

// Maximum number of rows
// to show in a table.
define("MAXROWS", 10);
$bShowTable = true;
$animalName = null;


/**
 * Render a Web page's header.
 * Readers probably want to modify
 * this code for their own Web site's header.
 
 * @param string $sTitle: Web page title.
 */
function showHeader($sTitle){
 echo' <!DOCTYPE html>';
 echo' <html lang="en-US"><head><title>' . $sTitle . '</title>';
 echo' <meta name="description" content="Score board for the ' . $sTitle . '. Shows the top ten scores."/>';
 echo' <meta http-equiv="content-type" content="text/html; charset=utf-8" />';
 echo' <meta name="viewport" content="width=device-width, initial-scale=1">';
 echo'<style> ';
 echo'main{width:90%; margin:0px auto 0px auto;}';
 echo'table{color:black; table-layout:fixed; width:100%; border-collapse:collapse; background-color:#4ef9ff; }';
 echo'th{text-align:left; font-weight:bold; color:white; background-color:#3b4afd; }';
 echo'td{text-align:left;}';
 echo'tr:nth-child(even){background-color:#4ef9ff;}';
 echo'tr:nth-child(odd) {background-color: #758dff;}';
 echo'td img{width:20%;}';

 echo'@media (max-width: 640px){';
 echo'th,tr,td{font-size:100%;}';
 echo'tr,td{font-weight:normal;}';
 echo'td img{width:20%;} td{word-wrap: break-word;}';
 echo'}';
 echo'@media (orientation: portrait) { ';
 echo'tr{font-size:80%;}';
 echo'}';
 echo'</style>';
 echo '</head>';
 echo' <body>';
 echo '<main>';
 echo'<h1>'. $sTitle .'</h1>';
}


/**
* If the URL's appended with
* the name of an animal
* then assign $bShowTable = false
* because we don't want to show the table.
* Otherwise show the table.

* Also assign the value that was appended
* to the URL, to
* variable $animalName
* Later just information for
* $animalName is
* returned in JSON format.
*/
function getPutData(){
 global $animalName;
 global $bShowTable;
 
 $animalName = $_SERVER ['QUERY_STRING'];
 $animalName = urldecode($animalName);
 
 if ($animalName == null || strlen($animalName) == 0 || empty($animalName) || ctype_space($animalName)){
  echo '<h2>See All Animals in the Table</h2>';
  $bShowTable = true;
 } 
 else{
  $bShowTable = false;
 }
}

/**
 * Render a Web page's footer.
 * Readers probably want to modify
 * this code for their own Web site's footer.
 */
function showFooter(){
 echo'</main>';
 echo '</body></html>';
}


/**
* Display the entire MySQL table
* to an HTML table.

* @param $result: Array of MySQL rows.
*/
function showTable($result){ 
 global $animalName, $bShowTable;
 if ($bShowTable == true){
  // Display the header:
  echo'<table><tr><th>Name</th><th>Region</th><th>Population</th><th>Image Link</th></tr>';
 }
 
 // Display each row.
 if($result) {
  while($row = mysqli_fetch_array($result)){
    $name = $row['name'];
    $region= $row['region'];
    $pop = $row['population'];
    $imglnk = $row['imglnk'];
    
    // A population of zero
    // indicates no one knows
    // the actual population.
    if ($pop == 0){
     $pop = "Unknown";
    }
    
    // Display each value in one row:
    echo '<tr><td>' . $name. '</td><td>' . $region.'</td><td>'. $pop . '</td>< <td>' . $imglnk . '</td></tr>';
   }
  }
 echo'</table>';
}


/**
 * Obtain just the first row of
 * MySQL data.
 * Convert to an associative array.
 * Assign each cell to a property.
 * Convert the associative array to JSON format.
 */
function showJSON($result){
 if($result){
  //  Convert MySQL to an associative array:
  $row = mysqli_fetch_assoc($result);

  // Copy each cell as a string.
  $animalObj->name   = (string)$row['name'];
  $animalObj->region = (string)$row['region'];
  $animalObj->image  = (string)$row['image'];
  $animalObj->pop    = (string)$row['population'];
  
  // Convert the associative array to JSON format.
  $animalJSON = json_encode($animalObj);
  
  // Echo the JSON object.
  // JavaScript obtains this data,
  // for display in HTML.
  echo $animalJSON;
 }
}


/**
 * Execute a query or exit this program.
 * @param SQL Connection: $con
 * @param String $sQuery: string SQL Query.
 * @return: result of SQL query.
 
 * Null's never returned because this function
 * exits the program if the result's null.
 */
function getSQLQuery($con, $sQuery){

 // rows or an error value:
 $rValue = mysqli_query($con,$sQuery);
 
 if ($rValue == null){
  $sError = mysqli_error($con);
  echo ' error adding or updating: ' . $sError;
  
  // Exit the program
  // die(..) is an alias for exit(...)
  exit('Error: ' . $sError);
 }
 return $rValue;
}


/**
* Connect to the database.
* Select the database.
* Either display the entire table
* in HTML format or one row in JSON format.

* @param $tableName: String name of MySQL table.
*/
function getSQL($tableName){
 global $hostname,$username,$password,$dbname,$bShowTable,$animalName;
 $con = null;
 $sQuery = null;
 $sqlResult = null;
 
 // Connect to the database or exit:
 $con = mysqli_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.');
 
 // Select our database:
 mysqli_select_db($con,$dbname);
 
 if($bShowTable == true){
  $sQuery = "SELECT * FROM " . $tableName;
  
  //Get all the rows from the table.
  $sqlResult =  getSQLQuery($con,$sQuery);  
  showTable($sqlResult);
 }
 
 else{
  $sQuery = "SELECT * FROM ". $tableName . " WHERE name = '{$animalName}'";

  $sqlResult = getSQLQuery($con,$sQuery);
  
  // Display one row
  // in JSON format.
  showJSON($sqlResult);
 }
 
 // Always close the
 // databse connection.
 mysqli_close($con);
}
?>

Summary

You learned to implement two JSON client-servers with JavaScript, PHP and MySQL. This page included explanation, source code and examples. The examples display information about animals in South Dakota, in response to one tap of a button, a menu selection, or you can see the entire table of animals at South Dakota Animals.

We started with a very simple PHP server that returns data in JSON format, for just one animal, when you tap a button. Next you can tap a button or select animals from a menu to see information returned from a MySQL database.

You learned two JSON techniques developers can use to send and retrieve data from a server. The first technique hard codes JSON values for return to the client. The second technique obtains JSON values from a MySQL table, to return to the client.

If for any reason this does not work for you please see JavaScript: Client-Server with JSON, PHP & MySQL, for details and a working example.