<?php
/************************************************************************
* *
* This program lists the students’ courses by using the *
* following 3 tables: *
* *
* CREATE TABLE student1 ( *
* sid INT AUTO_INCREMENT PRIMARY KEY, *
* name VARCHAR(64) NOT NULL, *
* email VARCHAR(32) NOT NULL ); *
* *
* CREATE TABLE course1 ( *
* cid CHAR(12) PRIMARY KEY, *
* title VARCHAR(64) NOT NULL, *
* room VARCHAR(16) NOT NULL, *
* credit INTEGER NOT NULL ); *
* *
* CREATE TABLE take1 ( *
* sid INT NOT NULL, *
* cid CHAR(12) NOT NULL, *
* semester VARCHAR(12) NOT NULL, *
* PRIMARY KEY ( sid, cid ), *
* FOREIGN KEY ( sid ) REFERENCES student1( sid ) ON DELETE CASCADE, *
* FOREIGN KEY ( cid ) REFERENCES course1( cid ) ON DELETE CASCADE ) *
* ENGINE=InnoDB; *
* *
* -- Below is an SQL example for listing courses: *
* *
* SELECT s.name, c.title FROM student1 s, course1 c, take1 t WHERE *
* ( s.sid=t.sid AND t.cid=c.cid ) AND *
* ( (UPPER(s.name) LIKE '%POKE%') OR (UPPER(s.name) LIKE '%BOB%') ) *
* GROUP BY s.name, c.title *
* *
************************************************************************/
if ( $_POST['act'] == "List courses" ) {
header( "Content-type:text/html; charset=UTF-8" );
echo ( "<html><body>" );
$username = "your_id";
$database = "your_db";
$password = "your-pw";
$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 );
// Compose the query.
$query = "SELECT s.name, c.title FROM ";
$query .= "student1 s, course1 c, take1 t WHERE ";
$query .= "( s.sid=t.sid AND t.cid=c.cid ) ";
$token = strtok( $_POST['student'], " " );
if ( $token !== false ) {
$query .= " AND ( ";
while ( $token !== false ) {
$query .= "(UPPER(s.name) " . "LIKE '%" . strtoupper($token) . "%') ";
$token = strtok( " " );
if ( $token !== false ) $query .= " OR ";
}
$query .= " ) ";
}
$query .= "GROUP BY s.name, c.title";
echo( $query );
// Execute the query.
$result = $conn->query( $query );
// Print the results row by row.
if ( $result->num_rows > 0 )
while ( $row = $result->fetch_assoc( ) )
echo $row['name'] . " 🠞 " . $row['title'];
// Close the database connection.
$conn->close( );
echo( "</body></html>" );
}
elseif ( $_POST["ct"] == "Help" ) {
header( "Content-type:text/html; charset=UTF-8" );
system( "cat Help.html" );
}
else {
header( "Content-type:text/plain; charset=UTF-8" );
if ( $_POST["ct"] == "HTML source" )
system( "cat html.txt" );
elseif ( $_POST["ct"] == "PHP source" )
system( "cat ListCourses.php" );
elseif ( $_POST["ct"] == "SQL source" )
system( "cat ListCourses.sql" );
else
echo "No such option: $_POST[act]";
}
?>
|