Slide 4.f: A PHP example (cont.)

A PHP Example (Cont.)

 <html><body>
 <?php
  $host = "mysql.aero.und.edu";
  $conn = mysql_connect( $host, "wenchen", "passwd" );
  mysql_select_db( "webdb", $conn );

  if ( $table == "Drop" ) {
    // Drop tables Enrollments and Courses.
    mysql_query( "DROP TABLE  Enrollments, Courses", $conn );
    echo "Tables Enrollments and Courses dropped";
  }

  elseif ( $table == "Create" ) {
    // Create and populate table Courses.
    $sql = "CREATE TABLE  Courses (
              number char( 10 )  PRIMARY KEY,
              name   varchar( 64 ) )";
    mysql_query( $sql, $conn );

    $sql = "INSERT INTO  Courses  VALUES
      ( '457', 'E-Commerce' ), ( '532', 'Programming Languages' )";
    mysql_query( $sql, $conn );
    echo "Table Courses created and populated";

    // Create and populate table Enrollments.
    $sql = "CREATE TABLE  Enrollments (
              name    varchar( 64 ),
              course  char( 10 ),
              PRIMARY KEY ( name, course ),
              FOREIGN KEY ( course ) REFERENCES  Courses ( number ) )";
    mysql_query( $sql, $conn );

    $sql = "INSERT INTO Enrollments VALUES ('Bart','532'), ('Bart','457')";
    mysql_query( $sql, $conn );
    $sql = "INSERT INTO Enrollments SELECT 'Ben', number FROM Courses
              WHERE  name = 'Programming Languages'";
    mysql_query( $sql, $conn );
    echo "Table Enrollments created and populated";
  }

  elseif ( $table == "Select" ) {
    $sql = "SELECT  E.name student,  C.name course
      FROM   Enrollments E, Courses C
      where  E.course  like  '%$course%' and E.course = C.number";
    if ( $result = mysql_query( $sql, $conn ) ) {
      while ( $row = mysql_fetch_array( $result ) ) {
        echo $row['student'] . ", " . $row['course'];
    }
  }
  mysql_close( $conn );
 ?>
 </body></html>