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

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])
            }
        }
        theBody.append("whence", fish.whence);      //  here is where the JS tells the PHP which server we're on.

        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.

php to mySQL