Slide 4.16: PHP MySQL select (cont.)

PHP MySQL Select (Cont.)

 <html><body>
 <?php
  $host = "undcsmysql.mysql.database.azure.com";
  $conn = new mysqli( $host, "userid@undcsmysql", "passwd", "schema" );
  $action =  Drop    Create & populate 
             Select ( $course = "" )

  if ( $action == "Drop" ) {
    // Drop tables Enrollments and Courses.
    $conn->query( "DROP TABLE  Enrollments, Courses" );
    echo "Tables Enrollments and Courses dropped";
  }

  elseif ( $action == "Create & populate" ) {
    // Create and populate table Courses.
    $sql = "CREATE TABLE  Courses (
              number CHAR( 10 )  PRIMARY KEY,
              name   VARCHAR( 16 ) ) ENGINE=InnoDB";
    $conn->query( $sql );

    $sql = "INSERT INTO  Courses  VALUES
      ( '457', 'E-Commerce' ), ( '260', 'Web Programming' )";
    $conn->query( $sql );
    echo "Table Courses created and populated";

    // Create and populate table Enrollments.
    $sql = "CREATE TABLE  Enrollments (
              name    VARCHAR( 32 ),
              course  CHAR( 10 ),
              PRIMARY KEY ( name, course ),
              FOREIGN KEY ( course ) REFERENCES  Courses ( number ) )
                ENGINE=InnoDB";
    $conn->query( $sql );

    $sql = "INSERT INTO Enrollments VALUES ('Bart','260'),
            ('Bart','457')";
    $conn->query( $sql );
    $sql = "INSERT INTO Enrollments SELECT 'Ben', number FROM Courses
              WHERE  name = 'E-Commerce'";
    $conn->query( $sql );
    echo "Table Enrollments created and populated";
  }

  elseif ( $action == "Select" ) {
    $sql = "SELECT  E.name student,  C.name course
      FROM   Enrollments E, Courses C
      where  E.course  like  '%$course%' and E.course = C.number";
    $result = $conn->query( $sql );
    if ( $result->num_rows > 0 )
      while( $row = $result->fetch_assoc( ) )
        echo $row['student'] . ", " . $row['course'];
  }
  $conn->close( );
 ?>
 </body></html>