MySql

This demonstration, which accesses our student program data stored in a MySQL database, is derived from code in a stackoverflow page. The screenshot below shows hyperlinked names of programs by the selected programmer together with further information obtained from the database.

MySQL_Demo in Action

MySQL_Demo in Action

The Penguin Tutor page entitled "Creating a LAMP server (web server – Linux Apache Mysql PHP) with the Raspberry Pi" was most useful for setting up a Pi to test the program. You can access the MySQL prompt with the command mysql -p -u root (which will create a request for your MySQL root password). These three commands, issued at the MySQL prompt, create a database with a fully privileged user:

CREATE DATABASE test;
CREATE USER 'student'@'localhost' IDENTIFIED BY 'pp4s';
GRANT ALL ON test.* TO 'student'@'localhost';

The code of the PHP scripts demonstrates how to establish a connection using these identifiers. We ran the script use_textfile.php from the console with the instruction php use_textfile.php to execute SQL statements such as the following in a file named sql.txt:

CREATE TABLE Programmer (ID INT NOT NULL PRIMARY KEY,Forename VARCHAR(15) NOT NULL,Surname VARCHAR(15),YearGroup VARCHAR(3),Age INT NOT NULL CHECK (AGE > 10 AND Age < 20));
CREATE TABLE Programs (Name VARCHAR(20) NOT NULL PRIMARY KEY,URL VARCHAR(30), Description VARCHAR(50), Category VARCHAR(20), ProgrammerID INT NOT NULL,FOREIGN KEY (ProgrammerID) REFERENCES Programmer(ID));
INSERT INTO Programmer VALUES (21, 'Christopher', 'Winward', 'L6', 16);
INSERT INTO Programmer VALUES (9, 'Max', 'Foster', 'L6', 16);
INSERT INTO Programs VALUES ('MrSnugglekins','cat-intro01', 'Cat tries to defend catnip against Spambots', 'Game', 9);
INSERT INTO Programs VALUES ('PascalPokemon','pascal-pokemon-intro', 'Pokemon clone', 'Game', 9);
INSERT INTO Programs VALUES ('SpaceShooter','spaceshooter-intro', 'Space shooter fires at rocks', 'Game', 21);

Some SQL code from our Pascal database tutorial was useful. See on the next pages the code of the Smart Mobile Studio and PHP files.

Programming - a skill for life!

Storage and retrieval of data using local storage, cookies, HTTP and MySQL