Skip navigation

Beginning PHP, Apache, MySQL Web Development

Chapter 3 Code - Using PHP with MySQL

Try It Out 1

createmovie.php - Try It Out 1

<?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", "root", "mysqlpass") or
     die ("Hey loser, check your server connection.");

//create the main database
mysql_create_db("wiley")
     or die(mysql_error());

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

//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)
) TYPE=MyISAM AUTO_INCREMENT=4 ";

$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)
) TYPE=MyISAM AUTO_INCREMENT=9" ;

$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)
) TYPE=MyISAM AUTO_INCREMENT=7";

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

echo "Movie Database successfully created!";

?>

moviedata.php - Try It Out 1

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

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

//insert data into "movie" table
$insert="INSERT INTO movie (movie_id, movie_name, movie_type, movie_year,
     movie_leadactor, movie_director)
      VALUES (1, 'Bruce Almighty', 5, 2003, 1, 2),
     (2, 'Office Space', 5, 1999, 5, 6),
     (3, 'Grand Canyon', 2, 1991, 4, 3)";
$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
$people="INSERT INTO people
         (people_id, people_fullname, people_isactor, people_isdirector)
      VALUES (1, 'Jim Carrey', 1, 0),
     (2, 'Tom Shadyac', 0, 1),
     (3, 'Lawrence Kasdan', 0, 1),
     (4, 'Kevin Kline', 1, 0),
     (5, 'Ron Livingston', 1, 0),
     (6, 'Mike Judge', 0, 1)";
$results=mysql_query($people)
     or die(mysql_error());

echo "Data inserted successfully!";
?>

Try It Out 2

select.php - Try It Out 2

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

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

$query="SELECT movie_name, movie_type
     FROM movie
     WHERE movie_year>1990
     ORDER BY movie_type";
$results=mysql_query($query)
     or die(mysql_error());

while ($rows=mysql_fetch_array($results)) {
     extract($rows);
     echo $movie_name;
     echo " - ";
     echo $movie_type;
     echo "<br>";
}

?>

Try It Out 3

select.php - Try It Out 3

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

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

$query="SELECT movie_name, movie_type
     FROM movie
     WHERE movie_year>1990
     ORDER BY movie_type";
$results=mysql_query($query)
     or die(mysql_error());

while ($rows=mysql_fetch_assoc($results)) {
     foreach($rows as $val1) {
     echo $val1;
     echo " ";
     }
echo "<br>";
}
?>

select2.php - Try It Out 3

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

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

$query="SELECT *
     FROM movie
     WHERE movie_year>1990
     ORDER BY movie_type";
$results=mysql_query($query)
     or die(mysql_error());
echo "<table border='1'>\n";
while ($rows=mysql_fetch_assoc($results)) {
echo "<tr>\n";
     foreach($rows as $value) {
     echo "<td>\n";
     echo $value;
     echo "</td>\n";
     }
echo "</tr><br>\n";
}
echo "</table>\n";
?>

Try It Out 4

select2.php - Try It Out 4

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

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

$query="SELECT movie.movie_name, movietype.movietype_label
     FROM movie, movietype
     WHERE movie.movie_type = movietype.movietype_id
          AND movie.movie_year>1990
     ORDER BY movie_type";
$results=mysql_query($query)
     or die(mysql_error());
echo "<table border='1'>\n";
while ($rows=mysql_fetch_assoc($results)) {
echo "<tr>\n";
     foreach($rows as $value) {
     echo "<td>\n";
     echo $value;
     echo "</td>\n";
     }
echo "</tr><br>\n";
}
echo "</table>\n";
?>

Try It Out 5

select2.php - Try It Out 5

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

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

$query="SELECT movie_name, movietype_label
     FROM movie
     LEFT JOIN movietype
     ON movie_type = movietype_id
     WHERE movie.movie_year>1990
     ORDER BY movie_type";
$results=mysql_query($query)
     or die(mysql_error());
echo "<table border='1'>\n";
while ($rows=mysql_fetch_assoc($results)) {
echo "<tr>\n";
     foreach($rows as $value) {
     echo "<td>\n";
     echo $value;
     echo "</td>\n";
     }
echo "</tr><br>\n";
}
echo "</table>\n";
?>