phpMySQL

PHP and mySQL

In early versions of these plugins, if there are data that need to be stored outside of the javascript code, we use an external database in mySQL. That requires an intermediary server-side language; we use php. In the future, these might be migrated to Firebase, but that was too hard for Tim.

The basic pattern for implementation is this:

• foo.phpConnect.js or foo.DBconnect.js contains js routines that you call in order to send or receive data from the database. This file prepares Requests and sends them using a sendCommand() method, which uses fetch() (the Fetch API) to connect to php.
• foo.php is accessed by that fetch(); it contains code that assembles queries and runs them, and returns the results of the queries (if any) to the javascript caller (foo.DBconnect.sendCommand()).

More details

This communication is tricky and has changed over the years. We hope we're using the most modern versions.

• For js-to-php, we use the Fetch API, and some associated ideas such as the FormData and Request classes. So basically, we trick teh system into thinking that we are submitting a form and receiving the results.
• For php-to-mySQL, we use PHP Data Objects (PDO), which have replaced the now-deprecated calls that look like mysql_query() and stuff like that. In the PDO model, you use the database credentials to create a database handle (which we typically name $DBH) that is your key to all subsequent interactions with mySQL.

js to php

We're using the Fetch API in javascript. Let's see how that looks on the javascript end of this communication, and then see what that looks like when we're in php.

js to php: the javascript end

Let's see how to use the Fetch API. Here are two routines from acs.DBconnect.js for your perusal:

    sendCommand: async function (iCommands) {
        const theCommand = iCommands.c;

        let theBody = new FormData();
        for (let key in iCommands) {
            if (iCommands.hasOwnProperty(key)) {
                theBody.append(key, iCommands[key])
            }
        }

        //  here is where the JS tells the PHP which server we're on.
        theBody.append("whence", fish.whence);      

        let theRequest = new Request(
            fish.constants.kBaseURL[fish.whence],
            {method: 'POST', body: theBody, headers: new Headers()}
        );

        try {
            const theResult = await fetch(theRequest);   // here (finally) is the fetch!
            if (theResult.ok) {
                const theJSON = await theResult.json();
                return theJSON;
            } else {
                console.error("sendCommand error: " + theResult.statusText);
            }
        }
        catch (msg) {
            console.log('fetch sequence error: ' + msg);
        }
    },

    getCasesFromDB : async function(iAtts) {
        const tSampleSize = document.getElementById("sampleSizeInput").value;

        let tAttNames = [];
        //  iAtts is an array, we need a comma-separated string:
        iAtts.forEach( a => tAttNames.push("`" + a.name + "`" ));   

        try {
            const theCommands = {
               "c": "getCases", 
               "atts": "," + tAttNames.join(','), 
               "n" : tSampleSize
            };
            const iData = await acs.DBconnect.sendCommand(theCommands);
            return iData;
        }

        catch (msg) {
            console.log('getCasesFromDB() error: ' + msg);
        }
    },

In this example, suppose some other routine needs cases from the DB. It calls await acs.DBconenctor.getCasesFromDB(). This is the bottom routine in the example. That function constructs the commands that php will need (they will be $_REQUEST variables on the inside) in the object theCommands. Then it asks sendCommand to send them.

sendCommand, for its part, does a little dance. It:

• translates the commands object into a FormData called theBody;
• adds an extra command, whence, which tells us what system we're on (e.g., "local");
• creates a Request object that includes the commands as well as the URL for the php file (which depends on whence as well);
• finally performs the fetch(), awaits its completion (it's a Promise), and returns theResult;
• extracts the JSON version of theResult and returns that JSON.

js to php: the php end

What happens when this fetch(theRequest) call hits the php? The basics of the communication is that fetch() specifies the URL of the foo.php file. The server runs the file; at the end, an echo statement is the text that gets returned.

Remember the javascript object theCommands? We have access to that in php; the object gets delivered as an associative array called $_REQUEST. So you will see something like this (from acs.php):

$DBH = CODAP_MySQL_connect("localhost", $user, $pass, $dbname);  // get that handle!

//  this is the overall command, the only required part of the POST:
$command = $_REQUEST["c"];     

switch ($command) {
    case 'getCases':
        //   code to assemble query comes here, see below...
        break;

    case 'getAllAttributeInfo':
        //   etc...

So the js theCommand = { c: "getCases" ... } has yielded $command = "getCases" in php. We then use that command in a switch; there is a different section in the code for each command, switched by the theCommand value.

php to mySQL

In PDO, there are two main phases: establishing credentials; and then actually constructing queries and executing them.

Establishing credentials

This issue has its own page: see Configuration.

A correct configuration specifies the various URLs needed as well as the values of the mySQL credentials such as $user, $pass, and $dbname. It also makes a connection to mySQL and defines the vital database handle, $DBH.

Constructing and executing queries

Now let's look again at that switch structure:

$DBH = CODAP_MySQL_connect("localhost", $user, $pass, $dbname); 
 //  this is the overall command, the only required part of the POST 
$command = $_REQUEST["c"];    

switch ($command) {
    case 'getCases':
        $params = array();
        $params["n"] = $_REQUEST["n"];
        $theVariables = $_REQUEST['atts'];  //  includes opening comma

        $query = "SELECT " . $theVariables . " FROM peeps ORDER BY RAND( ) LIMIT :n";

        $out = CODAP_MySQL_getQueryResult($DBH, $query, $params);
        break;

    case 'getAllAttributeInfo':
        //   etc...

As you see, our output $out is returned (synchronously) by CODAP_MySQL_getQueryResult(), which has three arguments:

• the database handle $DBH
• the query, a string that has parameters introduced by colons
• the parameters, which is an associative array whose keys, preceded by colons, can go into the query, where the values will get substituted for them. In our example, the sample size (which was originally a field in theCommands in js) becomes :n and installed in a LIMIT clause in the mySQL query.

The function CODAP_MySQL_getQueryResult() is defined in ../common/TE_DBCommon.php, which was included back up at the top.

Then, that variable $out gets converted to JSON, the echo-ed at the end of the php file:

switch ($command) {
    case 'getCases':
        //  stuff...
        $out = something;
        break;
    case 'getDecoderInfo':
        //  stuff...
        $out = something else;
        break;
}

$jout = json_encode($out);
echo $jout;

and that (if you follow it back and back) becomes iData in javascript, returned in getCasesFromDB(), way up near the top of this page.