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;


   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:


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        



      case GET_BUTTONS:



      case GET_SLIDERS:          // our job is GET_SLIDERS

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


      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                        


   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[1]['sliderno']    // the 15 fields of the second slider


              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    

function ajaxGetSliderArray()


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


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



On a successful transfer, the jQuery code is: SliderArray = $.parseJSON(data);

Other data arrays

We build seven other arrays at startup:

  ButtonArray,  LookupArray, TimingArray, MeterArray, MeterCalArray, RadiosArray and MyRigArray.

These remain on the client for the duration of the session.
They are used at start up for setting button captions and colours.
They are  used at startup, VFO change, band change for data formatting (eg: mult, divide, decpoint)