12.7. Ajax, MYSQL sliders example

12.7 Ajax and MySQL - loading slider data to the client

I will use sliders to illustrate the operation of Ajax client <> server data transfer
and MySQL (MariaDB) database access.

At piWebCAT startup, this process extracts 16 fields for 27 sliders in MySQL table sliders

and places the data in a data array variable on the client.
Javascript code on the client can thereafter use this data array to:

Set slider captions, ranges, and the presentation of the numeric slider value.
Control communication with the server.

Note that the captions are only needed for those sliders that do not have an associated button.
(If there is a button to the left, then the caption should have been set to 'nocap'.)

In clientinit.js we have the following code to get the slider data from the server:

var SliderArray = []; // set up an empty array to receive the data

function ajaxGetSliderArray()

{

var urlparams = 'rig=' + rig + '&job=' + GET_SLIDERS;

$.ajax({

type: 'POST',

url: 'cat/phpfiles/wcajaxinit.php',

data: urlparams,

dataType: 'text',

cache: false,

async: false,

timeout: 5000,

success: function(data){SliderArray = $.parseJSON(data);},

error: function(data){console.log('error');}

});

}

function ajxGetSliderArray() is one of a number of similar functions called at startup

This is standard Ajax format and is the ONLY code needed on the client to do

the job of getting the slider data into SliderArray.

It is a beautifully simple way of controlling a quite complex task.

Let us use the FTdx101D as example:

First: note that variable urlparams is set to: 'rig=FTdx101D&job=2'

GET_SLIDERS is a numeric constant set to a value of 2 in enums.js

A GET_SLIDERS constant = 2 is similarly defined in wcenums.php on the server.

Ajax will send to the server: url + '?' + data which for this job is:

cat/phpfiles/wcajaxinit.php?rig=FTdx101D&job=2

Transmission options are standard internet POST or GET ... we use POST

We shall return to the other Ajax fields later looking at processing the result.

On the server - selecting radio and job

File: wcajaxinit.php is used to create data arrays from eight database tables.
We are just looking at the sliders table as an example

(or slidersciv for Icom CI-V direct or slidershl for communication via Hamlib)

wcajaxinit.php does this slider job an then dies.

At its start we have to build in two more .php files.

require_once 'wcmysql.php';

require_once 'wcenums.php';

wcmysql.php contains database access info.: $DbServer, $DbUser, $DbPw, $Database

wcenums.php; defines numeric constants. In our example GET_SLIDERS = 2

The code is then:

$an = new ajaxinit; // create an instance of the ajaxinit class

$an->JobList(); // run the JobList() method (function)

class ajaxinit // all functions in this file are in class ajaxinit

{

public function JobList()

{

$job = intval($_POST['job']); // get the job code from the URL

$rig = $_POST['rig']; // get the radio from the URL

switch($job)

{

case GET_BUTTONS:

$this->GetButtonData($rig);

break;

case GET_SLIDERS: // our job is GET_SLIDERS

$this->GetSliderData($rig); // do the job

break;

case GET_LOOKUPS:

.... etc ...

The code above extracts $job and $rig from the calling URL POST data and then directs to
function: $this->GetSliderData($rig);

$this-> because the function is in this ajaxinit class.

$rig is a parameter passed to the function to inform it of which radio to get the sliders for.

See also Inspect element feature of web browsers.

On the server - extract the data from the database

function GetSliderData() extracts selected fields from the database sliders table
for all the sliders for FTdx101D and builds them into a PHP data array.

(or slidersciv for Icom CI-V direct or slidershl for conenction via Hamlib)

I have split the function into two halves for discussion purposes:

public function GetSliderData($rig)

{

global $DbServer; // The global declarations are always needed

global $DbUser; // to bring external variables into PHP functions.

global $DbPw;

global $Database;

global $catcomms;

// choose the correct table - Icom CIV or RS232 ? (catcomms is from a SESSION variable)

if($catcomms == 'CIV') {$table = 'slidersciv';} else {$table = 'sliders';};

// define an empty array to receive the data

$SliderArray = array();

// connect to the database using the data from wcmysql.php

$conn = mysqli_connect($DbServer,$DbUser,$DbPw,$Database);

if (mysqli_connect_errno($conn)){echo "failed to connect to MySQL: "

. mysqli_connect_error(); }

// set up a database query .. in this example

SELECT sliderno, active, caption ..etc ..... FROM sliders WHERE rig = 'FTdx101D';

$sql = "SELECT sliderno, active, caption, code, abx, mode, min, max, def,

mult, divide, offset, units, lookup, decpoint ";

$sql .= "FROM " . $table . " WHERE rig = '" . $rig . "';"; // . is concat

// Run the query to place data extraction information in variable: $result

$result = mysqli_query($conn, $sql);

Using $result we can extract the requested database data, one record at a time.
For each retrieved record, the fields are returned as $row[0], $row[1] ..etc

in the order that we made in the requesting query.

$i = 0;

while($row = mysqli_fetch_array($result)) // keep repeating until false

{

$SliderArray [$i++] = // $i++ means use the value of $i and then

array( // increment it ready for the next record

'sliderno' => $row[0], // sliderno was the first field in our query

'active' => $row[1], // active was the second field in our query

'caption' => $row[2],

'code' => $row[3],

'abx' => $row[4],

'min' => $row[5],

'max' => $row[6],

'def' => $row[7],

'mult' => $row[8],

'divide' => $row[9],

'offset' => $row[10],

'units' => $row[11],

'lookup' => $row[12],

'decpoint' => $row[13],

'aval' => 0, // used to store VFOA value on VFO switching

'bval' => 0, // used to store VFOA value on VFO switching

'xval' => 0 // unused

);

}; // go back no for the next record

mysqli_close($conn);

echo json_encode($SliderArray);

}

The outcome of the above code is a PHP array with elements:

$SliderArray[0]['sliderno'] // the 15 fields of the first slider

$SliderArray[0]['active']

etc...

$SliderArray[1]['sliderno'] // the 15 fields of the second slider

$SliderArray[1]['active']

etc ..

Now the magic bit..

json_encode($SliderArray); generates a copy of the whole slider array in JSON format

echo json_encode($SliderArray); generates the JSON code and sends it back to the client.

Then, in client javascript, the received JSON data, is built into an identical javascript array

by simply using:

SliderArray = $.parseJSON(data);

Thus, single line of PHP code on the server encodes and sends the whole sliders array to the client

and then a single line of code on the client generates a matching javascript array.

Data returned to the client

The JSON encoded data is transferred as a very long character string as below:

[{"sliderno":"1";"active":"Y";"code:"AMCO";..etc....},{"sliderno":"2";. etc

The originating Ajax code is restated below:

var SliderArray = []; // set up an empty array to receive the data

// get slider data array from server