PHP and MySQL

Intro

PHP and the Sakila database – a very short introduction to:

  • Create
  • Read
  • Update
  • Delete

Foreign key
Via the video below you can learn how to:

  • Design a database
  • Define foreign keys in PhpMyAdmin
  • Use the design view
  • Export the complete database structure as pdf, svf or dia.
  • NB: the reltional tables should use the InnoDB-engine.

Export report in e.g. pdf:

  1. In design view click “Import / export coordinatesfor relational schema”
  2. > Operations
  3. Click the link “Edit or export relational schema”
  4. Choose your design details, such as A4, portrait, etc. – and save the file.
  5. So an appendix on your database structure is ready.

Views
When you’re satisfied with some query you can save the query as a view.

PHP and MySQL

Here are some samples from the Sakila sample database:

Exercise 1

  1. Use the files above. Modify the connect file, and enter your own password and user details.
  2. Use another table than actors, and create relevant SQL – create, read, update, modify.
  3. Create SQL that will combine more data from several tables. Use JOINs.

Exercise 2

The update file has not been made yet – however the recipe is:

  1. Loop out a table of actors. Use the delete.php file as a model. Call your file something like modify.php or similar.
  2. Modify the output, so that the names etc. will appear in a form text input field.
  3. Hack a modifyAction file. Use the UPDATE keyword in SQL. And use the deleteAction.php file as inspiration.

Advertizing and Databases

Creative advertizers use databases in many ways. Here are some samples:

 

A Note on Security

For the sake of code clarity I have omited security issues, such as:

 

Leave a Reply