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