<html><body>
<?php
$host = "undcemmysql.mysql.database.azure.com";
// Initialize MySQLi.
$conn = mysqli_init( );
// Create an SSL connection.
mysqli_ssl_set( $conn, NULL, NULL, "DigiCertGlobalRootCA.crt.pem", NULL, NULL );
// Open a new connection to the MySQL server.
mysqli_real_connect( $conn, $host, "user.id", "passwd", "schema", 3306 );
$action = Drop Create & populate
Select ( $course = "" )
|
if ( $action == "Drop" ) {
// Drop tables Enrollments and Courses.
mysqli_query( $conn, "DROP TABLE Enrollments, Courses" );
echo "Tables Enrollments and Courses dropped";
}
|
elseif ( $action == "Create & populate" ) {
// Create and populate table Courses.
$query = "CREATE TABLE Courses (
number CHAR( 10 ) PRIMARY KEY,
name VARCHAR( 16 ) ) ENGINE=InnoDB";
mysqli_query( $conn, $query );
$query = "INSERT INTO Courses VALUES
( '457', 'E-Commerce' ), ( '260', 'Web Programming' )";
mysqli_query( $conn, $query );
echo "Table Courses created and populated";
// Create and populate table Enrollments.
$query = "CREATE TABLE Enrollments (
name VARCHAR( 32 ),
course CHAR( 10 ),
PRIMARY KEY ( name, course ),
FOREIGN KEY ( course ) REFERENCES Courses ( number ) )
ENGINE=InnoDB";
mysqli_query( $conn, $query );
$query = "INSERT INTO Enrollments VALUES ('Bart','260'),
('Bart','457')";
mysqli_query( $conn, $query );
$query = "INSERT INTO Enrollments SELECT 'Ben', number FROM Courses
WHERE name = 'E-Commerce'";
mysqli_query( $conn, $query );
echo "Table Enrollments created and populated";
}
|
elseif ( $action == "Select" ) {
$query = "SELECT E.name student, C.name course
FROM Enrollments E, Courses C
where E.course like '%$course%' and E.course = C.number";
$result = mysqli_query( $conn, $query );
$row = mysqli_fetch_assoc( $result );
if ( mysqli_num_rows( $result ) > 0 )
do
echo $row['student'] . " 🠞 " . $row['course'];
while( $row = mysqli_fetch_assoc( $result ) );
else
echo "Error selecting enrollments: " . mysqli_connect_error( );
}
mysqli_close( $conn );
?>
</body></html>
|
|