Dipping into a Database

Toes in the water photo by Godsgirl_madi on Pixabay

Lately my programming has been Javascript-centric, without even a PHP session backing it up, but I do have the desire from time to time for a database behind the game – for Minefield, in particular, there is a database-driven high score list. In that case, and for other games, the PHP functions/DB are accessed exclusively with ajax, and I thought I’d go through a simple boilerplate setup for that, plus some troubleshooting tips.

There’s a collection of code in the high-scores folder of my Utilities repo on GitHub, and you can also play Minefield to see that particular instantiation.


The code is split out into the following files:

  • db.php: holds credentials, separated off so they can be locked down and .gitignored
  • functions.php: has functions, separated from ajax.php for clarity
  • ajax.php: endpoint for ajax requests; includes db.php and functions.php; consists otherwise entirely of if(isset($_POST['param']){function call} statements
  • index.php: where the game happens; calls style.css and game.js
  • game.js: the JS that makes the ajax calls and puts the output into the page appropriately

There isn’t a game here, so game.js mocks one with win/lose buttons; the win button will generate a random integer between 1 and 100 as your score for the high score list. In either case, game.js calls ajax.php with the parameter get_scores=true. It walks through the scores in order, formatting them with HTML, and if its “isWin” argument is true, it will insert an input field between the last better or equal score and first worse score than the current one (the flag newScoreFlag ensures this only happens once). If “isWin” is true and there are no scores yet, the input field will be the only entry in the list.

If there is an input field, a submit handler is attached to it which calls functions.php with the parameters initials and score set to the appropriate values. This inserts a new entry into the table and also calls a function that cleans the table by removing all but the best 20 scores (to keep the DB queries quick; I made it 20 although only 10 are shown to avoid any race conditions with simultaneous submissions by different players).

To set up the game initially, fill in your database credentials in db.php (database name, username, password) and your desired high score table name in ajax.php, then visit <your-game-location>/includes/ajax.php?install=true to create the table. Once you’ve installed the table you can add an .htaccess file in /includes/ that forbids all traffic to those files, if you wish.

After that you’ll need to click “win” a few times to get the beginnings of a high score table; you’ll be able to see it inserting the input field into the appropriate slot for a golf/rummy/minesweeper score ordering.

More Details

When the game ends, whether as a win or a loss, the JS function displayScores makes an ajax call to ajax.php to request the scores. That file calls get_scores in functions.php, which echoes out a JSON-encoded array of initials and scores. Back to the JavaScript, displayScores decides based on its argument whether to look for a spot to insert an initials input, and otherwise formats the JSON it received into a table.

Whether there is an initials input or not, the “OK” button in the high score list activates submitScore, which will simply close the list if there is no initials input or that input is empty. Otherwise it validates and sanitizes the input and sends it again via ajax to ajax.php. This time set_score is called in functions.php, which re-sanitizes the input and puts it in the table. Right after set_score, clean_db runs, which selects the best 20 scores from the database and erases all others.

I was about as careful as I know how to be with this code – the table creation code will not do anything if a table by the given name already exists; initials inputs are cleansed of angle brackets and non-ascii characters both in the JS and in the PHP; angle brackets and ampersands are escaped via a custom function before the JS displays the scores; errors are written to log files; and all database interaction is done via prepared statements to avoid SQL injection. Once you have gotten it up and running correctly you can remove the line in db.php that sets PDO:ATTR_ERRMODE for less-verbose errors even in your logs, if desired. You could even put db.php in some non-web-accessible directory of your server and change the include line of ajax.php to point to the right place.

If you want to avoid off-color initials sequences, though, you’re on your own.

Troubleshooting PHP and MySQL

I tangled with a quirk of MySQL in one of these projects – instead of making a user specifically associated with a database, I made one separately and gave it permissions for the database afterward. The default for a new user was a host wildcard, %, meaning the user has permission to connect to the database from anywhere. However, there were two anonymous users already set up (no username), one for % and one specifically for localhost. MySQL has wildcard matching for user authentication, and if you are connecting from localhost, the order in which the users are checked for matching is: named@localhost, anonymous@localhost, named@%, anonymous@% (there is more than you ever wanted to know about MySQL Users and Logins on the SQL Server Blog). If the user is named@%, but connecting from localhost, the anonymous@localhost user matches first – and that user in my system had no permissions for any DB at all (I assume those two anonymous users with zero access exist for security reasons). It took me an overly long time to figure out what the problem was but creating a user @localhost instead of @% was the solution.

When I went back to Minefield for this post, to add a “scores loading” message for when the ajax response takes a noticeably long time, I had a different problem – I’d renamed the database table for the version available online but hadn’t created that table locally. This was causing my $db->prepare() command to return false, which made the $stmt->execute() command into nonsense. [A typo in the MySQL statement would have had the same effect.]

With the user-authentication problem I diagnosed via the front end, but for this issue (which presented itself simply as a 500 error) I decided to learn how to use the error logs. It’s simple with MAMP: the logs themselves are in /Applications/MAMP/logs, and php_error.log was the relevant one for this diagnosis. The easiest way to check your errors is tail php_error.log in the Terminal (once you’re in the logs directory); that will give the last 10 lines. If you need more, you can add them with -n #, and in that case you may want to pipe the output through less so you can page forward and backward with f and b:
tail -n 25 php_error.log | less

The error log may at first not include all the error messaging you need to diagnose your problem. That is where the line $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); in db.php comes in, along with error_log($errorInfo[2]); and error_log($e->getMessage()); in each function of functions.php. This will cause your function errors to be written to php_error.log for your perusal. Once I had that set up, it told me explicitly that the table didn’t exist, whereas previously the log just gave a PHP fatal error on the execute command.

Toes in the water photo by Godsgirl_madi on Pixabay.

Leave a Reply

Your email address will not be published. Required fields are marked *