Trouble entering data via PHP

[Swerve1000]

Hi,

 

I'm doing a tutorial from a PHP/MySQL book I bought and am unable to understand 1 piece of the code.

 

This is the first file which creates the database and it's tables:-

 

<?php
//connect to MySQL; note we’ve used our own parameters- you should use
//your own for hostname, user, and password
$connect = mysql_connect("localhost", "bp5am", "bp5ampass") or 
     die ("Hey loser, check your server connection.");

//create the main database if it doesn't already exist
$create = mysql_query("CREATE DATABASE IF NOT EXISTS moviesite")
  or die(mysql_error());

//make sure our recently created database is the active one
mysql_select_db("moviesite");

//create "movie" table
$movie = "CREATE TABLE movie (
  movie_id int(11) NOT NULL auto_increment, 
  movie_name varchar(255) NOT NULL, 
  movie_type tinyint(2) NOT NULL default 0, 
  movie_year int(4) NOT NULL default 0, 
  movie_leadactor int(11) NOT NULL default 0, 
  movie_director int(11) NOT NULL default 0, 
  PRIMARY KEY  (movie_id), 
  KEY movie_type (movie_type,movie_year) 
)";

$results = mysql_query($movie)
  or die (mysql_error());

//create "movietype" table
$movietype = "CREATE TABLE movietype ( 
  movietype_id int(11) NOT NULL auto_increment, 
  movietype_label varchar(100) NOT NULL, 
  PRIMARY KEY  (movietype_id) 
)"; 

$results = mysql_query($movietype)
  or die(mysql_error());

//create "people" table
$people = "CREATE TABLE people ( 
  people_id int(11) NOT NULL auto_increment, 
  people_fullname varchar(255) NOT NULL, 
  people_isactor tinyint(1) NOT NULL default 0, 
  people_isdirector tinyint(1) NOT NULL default 0, 
  PRIMARY KEY  (people_id) 
)"; 

$results = mysql_query($people)
  or die(mysql_error());

echo "Movie Database successfully created!";

?>

 

This the second file, it inserts data into the tables/fields created in the first file:-

 

<?php
//connect to MySQL
$connect = mysql_connect("localhost", "bp5am", "bp5ampass") 
  or die ("Hey loser, check your server connection.");

make sure we're using the right database
mysql_select_db("moviesite");

XXX insert data into "movie" table
XXX $insert = "INSERT INTO movie (movie_id, movie_name, movie_type, " .
XXX         "movie_year, movie_leadactor, movie_director) " .
XXX       "VALUES (1, 'Bruce Almighty', 5, 2003, 1, 2), " .
XXX       "(2, 'Office Space', 5, 1999, 5, 6), " .
XXX       "(3, 'Grand Canyon', 2, 1991, 4, 3)"; 
XXX $results = mysql_query($insert)
  or die(mysql_error());

//insert data into "movietype" table
$type = "INSERT INTO movietype (movietype_id, movietype_label) " .
        "VALUES (1,'Sci Fi'), " .
        "(2, 'Drama'), " .
        "(3, 'Adventure'), " .
        "(4, 'War'), " .
        "(5, 'Comedy'), " .
        "(6, 'Horror'), " .
        "(7, 'Action'), " .
        "(8, 'Kids')" ;
$results = mysql_query($type)
  or die(mysql_error());

//insert data into "people" table
XXX $people = "INSERT INTO people (people_id, people_fullname, " .
XXX         "people_isactor, people_isdirector) " .
XXX          "VALUES (1, 'Jim Carrey', 1, 0), " .
XXX         "(2, 'Tom Shadyac', 0, 1), " .
XXX          "(3, 'Lawrence Kasdan', 0, 1), " .
XXX          "(4, 'Kevin Kline', 1, 0), " .
XXX          "(5, 'Ron Livingston', 1, 0), " .
XXX         "(6, 'Mike Judge', 0, 1)";
$results = mysql_query($people)
  or die(mysql_error());

echo "Data inserted successfully!";
?>

 

My question is just about the 2 sections on the second piece of code where I have placed 'XXX' infront of the lines I am unable to understand.

 

How do the numbers placed in those 'XXX' lines relate to the database fields? I am unable to grasp any connection between the numbers and how they work with the database.

 

If anyone can help me, I would be extremely grateful.

 

 

[AndyB]

In the movie tables, the numbers are references to table ids in other tables so the script can 'look up' related data.  Exactly how a relational database would work.

 

In the people table, the 1 and 0 refer to whether the person named is an actor and/or is a director, i.e. they're just flags.

[the_oliver]

each bracket containing the numbers is an entrie:

 

(1, 'Bruce Almighty', 5, 2003, 1, 2) would translate to:

movie_id =1 , movie_name = Bruce Almighty, movie_type = 5, movie_year = 2003, movie_leadactor = 1 , movie_director = 2

 

and (2, 'Office Space', 5, 1999, 5, 6) would be entered as

movie_id =2 , movie_name = Office Space, movie_type = 5, movie_year = 1999, movie_leadactor = 5 , movie_director = 6

 

And that is got from the  

(movie_id, movie_name, movie_type,movie_year,movie_leadactor, movie_director) " .

 

So it says: value in the () [afer "VALUE" bit of you query] goes into the movie_id coloum.  Seccond value in the () [seperated by a commer] goes into the moive_name coloumb etc....

 

Make sence???

 

 

[Swerve1000]

Wow, speedy reply! thanks.

 

So ALL numbers have nothing to do with just this section of the code (below) which just describes the movie types?

$type = "INSERT INTO movietype (movietype_id, movietype_label) " .
        "VALUES (1,'Sci Fi'), " .
        "(2, 'Drama'), " .
        "(3, 'Adventure'), " .
        "(4, 'War'), " .
        "(5, 'Comedy'), " .
        "(6, 'Horror'), " .
        "(7, 'Action'), " .
        "(8, 'Kids')" ;

 

 

Thanks to both of you for such great replies

 

[the_oliver]

Sort of!  on the first part you asked about with all the XXX's you will notice you are inserting into a table called movie.

 

INSERT INTO movie

 

On the seccond movietype

INSERT INTO movietype

 

So this is as AndyB says.  you reed from the first (movie) that Bruce Almity has movie type 5.  Then you look at the second table (movietype) to ask 'what is movie type five?'  and it tells you that type 5 = Comedy.

Is a way of refrecing data between two tables.  Gets very  usefull as things get larger and more complex!