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>
|
|