SQL with PDO

PHP, PDO and MySQL Hello world

This tutorial takes you through building a web page to show details from database tables.

You will need to have access to a machine with the LAMP stack - Napier students can use inf08104 or socweb8.napier.ac.uk

Exploring the world database with scott

• Connect to the database scott on inf08104
• Run and SQL SELECT statement using PDO
• Output each row in a while loop

{{#ev:youtube|https://youtu.be/xbzX7OKwKKs}}

Connect to your own database

• Find out your password
• Download a database http://sqlzoo.net/world.sql
• Import your database into MySQL
• Change something about the world
• Update your php to look at your own copy of the world database

{{#ev:youtube|https://youtu.be/SVKMKjnJlys}}

Look at one movie from the movie database

• Download the movie database from http://sqlzoo.net/moviesm.sql
• Find the id of "Toy Story"
• Write a PHP query to look up that movie and show details

{{#ev:youtube|https://youtu.be/xANc6rZ_l6M}}

Use a parameter in your movie query

• Change toystory.php so it takes a CGI variable
• Visit the page with the CGI variable set
• Make up an index page with links to the best movies ever

{{#ev:youtube|https://youtu.be/l5F1D8YdbFI}}

Show more details of each movie

• Examine the tables movie, casting and actor
• Use a JOIN to include the director
• Write a second query to get the cast list

{{#ev:youtube|https://youtu.be/zsNL2ihVSRo}}