mysql & php: part 1: mysql - · pdf fileneck-deep in crud with mysql & php part 1:...
TRANSCRIPT
Neck-Deep in CRUD with MySQL & PHP Part 1: MySQL
Andrew Bullen – ISL Brian Smith – RAILS
LAMP stack
• Linux – operating system • Apache – web server • MySQL – database management system • PHP – programming language
(The “P” could also be PERL or Python.)
XAMPP
• X – cross-platform • Apache – web server • MySQL – database management system • PHP – programming language • PERL – also included!
Download from apachefriends.org
MySQL
• “My sequel” (also “My ess cue ell”) • Very widely used • SQL = Structured Query Language • SQL has commands and syntax to define and
manipulate data
CRUD
• Create – add new data • Read – retrieve existing data • Update – change existing data • Delete – get rid of existing data
Database
• Database has tables • Table has:
– Columns = fields – Rows = records
• Database has relationships between tables – One-to-one – One-to-many – Many-to-many
Relationships
Hey, let’s build a database!
Create pet_library database
Create genres table
Define columns for genres table
Create pets table
Define columns for pets table
Note: petGenre column needs to be INDEXED.
Add pets -> genres relationship
Set petGenre as a foreign key
Create: Time to add data!
Note: Values of string fields need to be enclosed in single quotes. Do not enclose values of numeric fields in quotes.
Yay!
Let’s add some pets!
Read: Queries to list all pets
SELECT * FROM pets SELECT * FROM pets ORDER BY petName SELECT petName, petDescription FROM pets
Queries to list some pets
SELECT * FROM pets WHERE petName = ‘Spot’ SELECT * FROM pets WHERE petName LIKE ‘S%’ SELECT * FROM pets WHERE petID <> 2
Query with JOIN to include genre
SELECT p.petName, g.genre, p.petDescription FROM pets AS p JOIN genres AS g ON p.petGenre = g.genreID ;
Query for pets and petless genres
SELECT p.petName, g.genre, p.petDescription FROM pets AS p RIGHT JOIN genres AS g ON p.petGenre = g.genreID ;
Update: Wuffles is very old
R.I.P. Wuffles
Try some more UPDATEs
Some suggestions … • Dr. Moreau has changed one of the cats into a
ferret. • Change the name of a pet. • Bring Wuffles back to life! • Budgie? We say “parakeet” here in the USA!
Delete: Weed dogs from the collection
Everyone have at least one dog? Try this: DELETE FROM genres WHERE genreID = 2 (You should get an error.) Now do: DELETE FROM pets WHERE petGenre = 2 And then try again: DELETE FROM genres WHERE genreID = 2
Be careful with DELETE
• If you don’t include a good WHERE condition, you may delete rows that you needed to keep.
Some other dangerous MySQL commands: • TRUNCATE TABLE – deletes all data from table,
but keeps table structure • DROP TABLE – totally deletes table • DROP DATABASE – Guess what that does?
Make regular backups!
Database user accounts
• XAMPP’s default is no password for root user • In the real world:
– Set a strong password for root. Instructions at http://www.scriptarticle.com/2012/07/04/how-to-change-or-reset-xampp-mysql-root-password/
– Add a new user with access only for your database, and connect your website code to the database as that user.
Adding user for pet_library
Allow only necessary permissions
Help and tutorials:
• http://dev.mysql.com/doc/ • http://stackoverflow.com/ • www.w3schools.com/sql/