Questions

1
Questions

• In the PHP script mysqltest.php
• What PHP instructions are used to connect to the
  database?
• Where is the query entered?
• Which code returns the formatted results of the
  query?

2
Construct SQL with Access

• Modify the query that finds the actors who have
  played Romeo so it returns the movie in which the
  actor appeared as Romeo and the movies year of
  release
• Test the query in Access
• Copy the SQL into the PHP script
• Remove the semi-colon from the end

3
Differences in SQL versions

• Copy the file AccessSQL vs MySQL.doc from the Day
  34 course AFS space to your personal space

4
Translating Access SQL

• SELECT Title, Year, Rating, Role, Director,
  StageFirstName, StageLastName
• FROM tbl_movies INNER JOIN
• (tbl_actors INNER JOIN tbl_roles
• ON tbl_actors.ActorID tbl_roles.ActorID)
• ON tbl_movies.MovieID tbl_roles.MovieID
• WHERE ( ((Director)Allen, Woody)
• AND ((StageFirstName)Mia)
• AND ((StageLastName)Farrow) )

5
To MySQL

• SELECT Title, Year, Rating, Role, Director,
  StageFirstName, StageLastName
• FROM tbl_movies, tbl_actors, tbl_roles
• WHERE tbl_actors.ActorIDtbl_roles.ActorID
• AND tbl_movies.MovieIDtbl_roles.MovieID
• AND DirectorAllen, Woody
• AND StageFirstNameMia
• AND StageLastNameFarrow

6
Translating INNER JOINs

• Access SQL
• SELECT
• FROM tbl_movies
• INNER JOIN
• (tbl_actors
• INNER JOIN tbl_roles
• ON tbl_actors.ActorID tbl_roles.ActorID)
• ON tbl_movies.MovieID tbl_roles.MovieID
• WHERE

• MySQL
• SELECT
• FROM tbl_movies, tbl_actors, tbl_roles
• WHERE tbl_actors.ActorID tbl_roles.ActorID
• AND tbl_movies.MovieID tbl_roles.MovieID
• AND

7
Dynamic Web Page Example

• We want to construct a dynamic web page that will
  list the actors and roles in a movie specified by
  the user
• To begin, construct a query in Access that finds
  the actors appearing in Gosford Park
• Test the query in Access
• Copy the SQL for query into PHP script
• Translate for MySQL

8
Using Form Parameters in SQL statement in PHP

• To include the PHP parameter Title in the SQL
  statement use curly braces
• Using the text Titlein your SQL statement will
  substitute the value of title
• Leave the double quotes around the parameter

9
Making the All-Purpose Form

• Make an HTML form which prompts for a movies
  title
• Use the revised PHP script as the action for the
  form
• The parameter name must match the input name of
  the movie title.
• Test with the movie Tootsie

10
Intro to UBT Project

• Work alone or in pairs
• There are a list of suggested topics on todays
  classwork page
• E-mail cse103_at_cse.msu.edu with your first four
  choices
• We will randomly assign topics to groups or
  individuals next day

11
Server-side databases

• It has to have a pre-defined structure so that
  all people who want to construct forms know how
  data are stored in the database.
• However, different people can construct different
  forms provided that they use the structure of the
  database.
• A wide variety of users, many of whom know
  nothing about constructing forms, can query the
  database. Eg. IMdb.

12
Student Records Database Design

• What entities do we need to include
• What are the attributes for each entity?
• What relationships do we need?
• Once the design is finished, it will be frozen.
  No further changes will be accepted.