Skip navigation

Beginning PHP, Apache, MySQL Web Development

Chapter 6 Code - Letting the User Edit the Database

Try It Out 1

index.php - Try It Out 1

<?php
   $link = mysql_connect("localhost", "root", "")
      or die("Could not connect: " . mysql_error());
   mysql_select_db('chapter6', $link) or die ( mysql_error());
?>
<html>
<head>
   <TITLE>Movie database</TITLE>
</head>
<body>
   <table border=0 width="600" cellspacing=1 cellpadding=3 bgcolor="#353535" align="center">
         <td bgcolor="#ffffff" colspan=2 align="center">
            Movies <a href="movie.php?action=add&id=">[ADD]</a>
         </td>
<?php
   $moviesql = "SELECT
                *
             FROM
               `movie`
            ";
   $result = mysql_query($moviesql)
      or die("Invalid query: " . mysql_error());
   while( $row = mysql_fetch_array($result, MYSQL_ASSOC) ){
?>
      <tr>
         <td bgcolor="#ffffff" width="50%">
            <?php echo $row['movie_name']?>
         </td>
         <td bgcolor="#ffffff" width="50%" align="right">
            <a href="movie.php?action=edit&id=<?php echo $row['movie_id']?>">[EDIT]</a>
            <a href="delete.php?type=movie&id=<?php echo $row['movie_id']?>">[DELETE]</a>
         </td>
      </tr>
<?php
   }
?>
         <td bgcolor="#ffffff" colspan=2 align="center">
            People <a href="people.php?action=add&id=">[ADD]</a>
         </td>
<?php
   $moviesql = "SELECT
                *
             FROM
               `people`
            ";
   $result = mysql_query($moviesql)
      or die("Invalid query: " . mysql_error());
   while( $row = mysql_fetch_array($result, MYSQL_ASSOC) ){
?>
      <tr>
         <td bgcolor="#ffffff" width="50%">
            <?php echo $row['people_fullname']?>
         </td>
         <td bgcolor="#ffffff" width="50%" align="right">
            <a href="people.php?action=edit&id=<?php echo $row['people_id']?>">[EDIT]</a>
            <a href="delete.php?type=people&id=<?php echo $row['people_id']?>">[DELETE]</a>
         </td>
      </tr>
<?php
   }
?>
   </table>
</body>
</html>

Try It Out 3

movie.php - Try It Out 3

<?php
   $link = mysql_connect("localhost", "root", "")
      or die("Could not connect: " . mysql_error());
   mysql_select_db('chapter6', $link) or die ( mysql_error());
   $peoplesql = "SELECT
               *
             FROM
               `people`
            ";
   $result = mysql_query($peoplesql)
      or die("Invalid query: " . mysql_error());
   while( $row = mysql_fetch_array( $result , MYSQL_ASSOC )){
      $people[ $row['people_id'] ] = $row['people_fullname'];
   }
?>
<html>
<head>
   <TITLE>Add movie</TITLE>
</head>
<body>
<FORM action="commit.php?action=add&type=movie" method="post">
   <table border=0 width="750" cellspacing=1 cellpadding=3 bgcolor="#353535" align="center">
      <tr>
         <td bgcolor="#ffffff" width="30%">
            Movie Name
         </td>
         <td bgcolor="#ffffff" width="70%">
            <input type="text" name="movie_name">
         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff">
            Movie Type
         </td>
         <td bgcolor="#ffffff">
         <SELECT id="game" name="movie_type" style="width:150px">
<?php
         $sql = "SELECT
                  `movietype_id`,
                  `movietype_label`
               FROM
                  `movietype`
               ORDER BY
                  `movietype_label`
               ";
         $result = mysql_query($sql)
            or die("<font color=\"#FF0000\">Query Error</FONT>".mysql_error());
         while ( $row = mysql_fetch_array($result) ){
            echo '<OPTION 
value="'.$row['movietype_id'].'">'.$row['movietype_label'].'</OPTION>'."\r\n";
         }
?>
         </SELECT>

         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff">
            Movie Year
         </td>
         <td bgcolor="#ffffff">
            <SELECT name="movie_year">
               <option value="" SELECTED>Select a year...</option>
<?php
for ($year=date("Y"); $year >= 1970 ;$year--){
?>
               <option value="<?php echo $year?>"><?php echo $year?></option>
<?php
}
?>
            </SELECT>
         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff">
            Lead Actor
         </td>
         <td bgcolor="#ffffff">
            <SELECT name="movie_leadactor">
               <option value="" SELECTED>Select an actor...</option>
<?php
foreach( $people as $people_id => $people_fullname ){
?>
               <option value="<?php echo $people_id?>" ><?php echo $people_fullname?></option>
<?php
}
?>
            </SELECT>
         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff">
            Director
         </td>
         <td bgcolor="#ffffff">
            <SELECT name="movie_director">
               <option value="" SELECTED>Select a director...</option>
<?php
foreach( $people as $people_id => $people_fullname ){
?>
               <option value="<?php echo $people_id?>" ><?php echo $people_fullname?></option>
<?php
}
?>
            </SELECT>
         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff" colspan=2 align="center">
            <INPUT type="SUBMIT" name="SUBMIT" value="Add">
         </td>
      </tr>
   </table>
</FORM>
</body>
</html>

commit.php - Try It Out 3

<?php
// COMMIT ADD
   $link = mysql_connect("localhost", "root", "")
      or die("Could not connect: " . mysql_error());
   mysql_select_db('chapter6', $link) or die ( mysql_error());
   switch( $_GET['action'] ){
      case "add":
         switch( $_GET['type'] ){
            case "movie":
               $sql = "INSERT INTO
                        `movie`
                        ( `movie_name` ,
                        `movie_year` ,
                        `movie_type` ,
                        `movie_leadactor` ,
                        `movie_director` )
                     VALUES
                        ( '".$_POST['movie_name']."' ,
                        '".$_POST['movie_year']."' ,
                        '".$_POST['movie_type']."' ,
                        '".$_POST['movie_leadactor']."' ,
                        '".$_POST['movie_director']."' )
                     ";
               break;
         }
         break;
   }
   if ( isset( $sql ) && !empty( $sql )){
      echo "<!--".$sql."-->";
      $result = mysql_query( $sql )
         or die("Invalid query: " . mysql_error());
?>
      <p align="center" style="color:#FF0000">
         Done. <a href="index.php">Index</a>
      </p>
<?php
   }
?>

Try It Out 5

delete.php - Try It Out 5

<?php
   $link = mysql_connect("localhost", "root", "")
      or die("Could not connect: " . mysql_error());
   mysql_select_db('chapter6', $link) or die ( mysql_error());
// DELETE SCRIPT
   if ( !isset( $_GET['do'] ) || $_GET['do'] != 1 ){
?>
      <p align="center" style="color:#FF0000">
         Are you sure you want to delete this <?php echo $_GET['type']?>?<br/>
         <a href="<?php echo $_SERVER['REQUEST_URI']?>&do=1">yes</a> or <a 
href="index.php">Index</a>
      </p>
<?php
   } else {
      if ( $_GET['type'] == "people" ){
         // delete references to people from the movie table
         // delete reference to lead actor
         $actor = "UPDATE
                  `movie`
               SET
                  `movie_leadactor` = '0'
               WHERE
                  `movie_leadactor` = '".$_GET['id']."'
               ";
         $result = mysql_query( $actor )
            or die("Invalid query: " . mysql_error());
         // delete reference to director
         $director = "UPDATE
                  `movie`
               SET
                  `movie_director` = '0'
               WHERE
                  `movie_director` = '".$_GET['id']."'
               ";
         $result = mysql_query( $director )
            or die("Invalid query: " . mysql_error());
      }
      // generate SQL
      $sql = "DELETE FROM
               `".$_GET['type']."`
            WHERE
               `".$_GET['type']."_id` = '".$_GET['id']."'
            LIMIT 1";
      // echo SQL for debug purpose
      echo "<!--".$sql."-->";
      $result = mysql_query( $sql )
         or die("Invalid query: " . mysql_error());
?>
      <p align="center" style="color:#FF0000">
         Your <?php echo $_GET['type']?> has been deleted. <a href="index.php">Index</a>
      </p>
<?php
   }
?>

Try It Out 6

movie.php - Try It Out 6

<?php
   $link = mysql_connect("localhost", "root", "")
      or die("Could not connect: " . mysql_error());
   mysql_select_db('chapter6', $link) or die ( mysql_error());
   $peoplesql = "SELECT
               *
             FROM
               `people`
            ";
   $result = mysql_query($peoplesql)
      or die("Invalid query: " . mysql_error());
   while( $row = mysql_fetch_array( $result , MYSQL_ASSOC )){
      $people[ $row['people_id'] ] = $row['people_fullname'];
   }

   switch( $_GET['action'] ){
      case "edit":
         $moviesql = "SELECT
                     *
                   FROM
                     `movie`
                   WHERE
                      `movie`.`movie_id` = '".$_GET['id']."'
                  ";
         $result = mysql_query($moviesql)
            or die("Invalid query: " . mysql_error());
         $row = mysql_fetch_array( $result , MYSQL_ASSOC );
         $movie_name = $row[ 'movie_name' ];
         $movie_type = $row[ 'movie_type' ];
         $movie_year = $row[ 'movie_year' ];
         $movie_leadactor = $row[ 'movie_leadactor' ];
         $movie_director = $row[ 'movie_director' ];
         break;
      default:
         $movie_name = "";
         $movie_type = "";
         $movie_year = "";
         $movie_leadactor = "";
         $movie_director = "";
         break;
   }
?>
<html>
<head>
   <TITLE><?php echo $_GET['action']?> movie</TITLE>
</head>
<body>
<FORM action="commit.php?action=<?php echo $_GET['action']?>&type=movie&id=<?php 
echo $_GET['id']?>" method="post">
   <table border=0 width="750" cellspacing=1 cellpadding=3 bgcolor="#353535" 
align="center">
      <tr>
         <td bgcolor="#ffffff" width="30%">
            Movie Name
         </td>
         <td bgcolor="#ffffff" width="70%">
            <input type="text" name="movie_name" value="<?php echo $movie_name?>">
         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff">
            Movie Type
         </td>
         <td bgcolor="#ffffff">
         <SELECT id="game" name="movie_type" style="width:150px">
<?php
         $sql = "SELECT
                  `movietype_id`,
                  `movietype_label`
               FROM
                  `movietype`
               ORDER BY
                  `movietype_label`
               ";
         $result = mysql_query($sql)
            or die("<font color=\"#FF0000\">Query Error</FONT>".mysql_error());
         while ( $row = mysql_fetch_array($result) ){
            if ( $row['movietype_id'] == $movie_type){
               $selected = " SELECTED";
            } else {
               $selected = "";
            }
            echo '<OPTION value="'.$row['movietype_id'].'"'.$selected.'>'.$row['movietype_label'].
'</OPTION>'."\r\n";
         }
?>
         </SELECT>

         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff">
            Movie Year
         </td>
         <td bgcolor="#ffffff">
            <SELECT name="movie_year">
               <option value="" SELECTED>Select a year...</option>
<?php
for ($year=date("Y"); $year >= 1970 ;$year--){
            if ( $year == $movie_year){
               $selected = " SELECTED";
            } else {
               $selected = "";
            }
?>
               <option value="<?php echo $year?>"<?php echo $selected?>><?php echo 
$year?></option>
<?
}
?>
            </SELECT>
         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff">
            Lead Actor
         </td>
         <td bgcolor="#ffffff">
            <SELECT name="movie_leadactor">
               <option value="" SELECTED>Select an actor...</option>
<?php
foreach( $people as $people_id => $people_fullname ){
            if ( $people_id == $movie_leadactor){
               $selected = " SELECTED";
            } else {
               $selected = "";
            }
?>
               <option value="<?php echo $people_id?>"<?php echo $selected?>><?php 
echo $people_fullname?></option>
<?php
}
?>
            </SELECT>
         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff">
            Director
         </td>
         <td bgcolor="#ffffff">
            <SELECT name="movie_director">
               <option value="" SELECTED>Select a director...</option>
<?php
foreach( $people as $people_id => $people_fullname ){
            if ( $people_id == $movie_director){
               $selected = " SELECTED";
            } else {
               $selected = "";
            }
?>
               <option value="<?php echo $people_id?>"<?php echo $selected?>><?php 
echo $people_fullname?></option>
<?php
}
?>
            </SELECT>
         </td>
      </tr>
      <tr>
         <td bgcolor="#ffffff" colspan=2 align="center">
            <INPUT type="SUBMIT" name="SUBMIT" value="<?php echo $_GET['action']?>">
         </td>
      </tr>
   </table>
</FORM>
</body>
</html>