Slide 7.6: PHP MySQL select (cont.)

PHP MySQL Select (Cont.)

 <html><body>
 <?php
  $servername = "undcsmysql.mysql.database.azure.com";
  $username   = "user.id@undcsmysql";
  $password   = "password";
  $dbname     = "user_id";         # your database or schema
  // Connect to the database.
  $conn       = new mysqli( $servername, $username, $password, $dbname );

  $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";
    if ( $result = $conn->query( $sql ) )
      while ( $row = $result->fetch_assoc( ) )
        echo $row['student'] . ", " . $row['course'];
  }
  $conn->close( );
 ?>
 </body></html>