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)