Building a Sudoku web app (part 1, server side)

Introduction

A PHP application which uses a MySQL database to store sudoku puzzles and their solutions. Puzzles are retrieved from a Kaggle CSV data file. The application randomly selects a puzzle and provides it via its API. Then it responds to request for user number selection as correct or wrong and when completed, if the puzzle is solved or there are errors.
In part 2 the JavaScript front end is implemented.

Note: These experiments are part of the CodingSummer21 activities and their goal is not to provide a perfect solution, but some solution simple to understand so that students can fork and improve.

GitHub: github.com/codingsummer21/sudoku-back

Populating the database

Data are retrieved from Kaggle 1 million Sudoku games, then a PHP script is used to load the data into a MySQL database. The puzzles table has the following structure:

CREATE TABLE `puzzles` (
  `id` int(11) NOT NULL,
  `puzzle` varchar(81) NOT NULL,
  `solution` varchar(81) NOT NULL,
  `times_selected` int(11) NOT NULL DEFAULT '0',
  `times_solved` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `puzzles`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `puzzles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

where id is an auto-increment primary key, necessary in order to send to the client, so that the client will be able to ask the API if user played correctly and when puzzle is completed if it is correct or not.

Script populate.php reads the CSV file and inserts the data in the database, if the database is empty.

API

Script get_sudoku.php generates a random number, finds the puzzle with this ID and returns as:

{id:'23',  
puzzle:'140060800085010040907400250030070400209000307008900060000740010601305090700002600', 
times_selected:0, 
times_solved:0}

Script check.php expects JSON formatted POST data containing: the id of the puzzle, the row, col, and value user has played, and checks if this value is correct. It provides the following feedback:

{result:'correct'}

if value for this row-col is correct, 'wrong' otherwise.

Script solved.php expects JSON formatted POST data containing: the id of the puzzle and a string with the solution, and then checks if the solution for this id is correct or not. Like the previous case it provides:

{result:'correct'}

or 'wrong' depending on the correctness of the solution.

Use this project

Clone or Fork/Clone the GitHub repository.
Create the database and run the puzzles.sql script to create the table.
Get the CSV file with 1 million sudokus from Kaggle and run populate.php script to put the data in the database. (Warning: This will take some time).
Test the available API options using Postman, then connect the front-end app (available in the second part of this project)

Ideas for improvement:

Add functionality for times-selected and times-solved tracking.
Improve populate performance.
Replace the puzzles stored in the database with an algorithm generating Sudoku puzzles
Once the algorithm is implemented, add the ability for difficulty level to the generated puzzle

gprok