LAMP - Part 1 - Prepare MySQL

My notes on how to create a LAMP form using PDO and MySQL:

Part 1: Prepare mysql

This is part 1 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form


Create the database:

mysql -u root -p
create database myforms;
show databases;
use myforms;


Create the table:

This will create a table "Form1" with three fields: ID, LastName, and FirstName:

CREATE TABLE Form1 (
ID INT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255) NOT NULL,
PRIMARY KEY (ID)
);



Populate the table with sample data:

INSERT INTO Form1 VALUES (1, 'Flintstone', 'Fred');
INSERT INTO Form1 VALUES (DEFAULT, 'Flintstone', 'Wilma');
INSERT INTO Form1 VALUES (DEFAULT, 'Rubble', 'Barney');
INSERT INTO Form1 VALUES (DEFAULT, 'Rubble', 'Betty');

SELECT * FROM Form1;



Create users:

We will be creating two users -- one for read operations and one for write operations.  (This is for demo purposes.  In my production application, I don't have the need for users to read from the database.  For tinkering purposes, I will document it here.)

CREATE USER 'testreader'@'%' IDENTIFIED BY 'myStrongPassword';
CREATE USER 'testwriter'@'%' IDENTIFIED BY 'myOtherStrongPassword';

SELECT User FROM mysql.user;



Grant privileges to the users:

GRANT INSERT ON myforms.* TO 'testwriter'@'%';
GRANT SELECT ON myforms.* TO 'testreader'@'%';


Test user access:

mysql -u testwriter -p
use myforms;
show tables;
INSERT INTO Form1 VALUES (DEFAULT, 'Flintstone', 'Pebbles');
SELECT * FROM Form1;

(The SELECT statement will be denied for the testwriter account)

mysql -u testreader -p
use myforms;
show tables;
INSERT INTO Form1 VALUES (DEFAULT, 'Rubble', 'Bambam');
SELECT * FROM Form1;

(The INSERT statement will be denied for the testreader account)