<html><body>
<?php
$username = "user_id";
$database = "user_id";
$password = "p-word";
$host = "undcemmysql.mysql.database.azure.com";
// Initializing MySQLi
$conn = mysqli_init( );
// Creating an SSL connection
mysqli_ssl_set( $conn, NULL, NULL, "DigiCertGlobalRootCA.crt.pem", NULL, NULL );
// Opening a new connection to the MySQL server
mysqli_real_connect( $conn, $host, $username, $password, $database, 3306 );
// Connect to the database.
if ( $conn->connect_errno )
die( 'Failed to connect to MySQL: ' . $conn->connect_error );
$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'];
}
// Close the database connection.
$conn->close( );
?>
</body></html>
|
|