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 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() to connect to php.
• foo.php is run 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.phpConnect.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 fish.phpConnector.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);
        }
    },

    getGameData: async function () {
        try {
            const theCommands = {"c": "gameData", "gameCode": fish.state.gameCode};
            const iData = await fish.phpConnector.sendCommand(theCommands);
            return iData;
        }

        catch (msg) {
            console.log('get game data error: ' + msg);
        }
    },

In this example, suppose some other routine needs game data from the DB. It calls await fish.phpConnector.getGameData(). 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?

php to mySQL

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

Establishing credentials

For access to the mySQL database, you need a database handle, and in order to get that, you need credentials: the name of the database, a username, a password, and the URL of the mySQL server. The latter is simple: it's always localhost: we're running mySQL on the same server as we're running php. The rest are tougher. For security reasons, we don't want to put them, in cleartext, in a publicly-accessible file. So we store them above the level of the web server root.

For our plugin called foo, there might be a file called root/cred/fooCred.php that looks like this:

<?php

$credentials = array(
    "local" => array(        //  http://localhost:8888/foo/foo.php
        "dbname" => "foo",
        "host" => "localhost",
        "user" => "fooUser",
        "pass" => "foo42"
    ),
    "xyz" => array(         //  http://codap.xyz/projects/foo/foo.php
        "dbname" => "codapxyz_foo",
        "host" => "localhost",
        "user" => "codapxyz_foo",
        "pass" => "foo.wombat42"
    ),
    "eeps" => array(         //  https://www.eeps.com/foo/foo.php
        "dbname" => "denofinq_foo",
        "host" => "localhost",
        "user" => "denofinq_foo",
        "pass" => "foo42^&%"
    )
);

?>

As you can see, we store the credentials for a number of possible sites in this associative array. And the keys to the array ate the same as the whence variable.

Constructing and executing queries