$_POST[student], a list of names entered from the Web.
If the $_POST[student] is empty, then list all titles.
Note that everything printed from PHP is displayed on the browser.
|
|
<?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 = "user.id@undcsmysql";
$password = "password";
$database = "schema";
$host = "undcsmysql.mysql.database.azure.com";
// Connect to the database.
$conn = new mysqli( $host, $username, $password, $database );
if ( $conn->connect_error )
die( 'Could not connect: ' . $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["act"] == "Help" ) {
header( "Content-type:text/html; charset=UTF-8" );
system( "cat Help.html" );
}
else {
header( "Content-type:text/plain; charset=UTF-8" );
if ( $_POST["act"] == "HTML source" )
system( "cat html.txt" );
elseif ( $_POST["act"] == "PHP source" )
system( "cat ListCourses_php.txt" );
elseif ( $_POST["act"] == "SQL source" )
system( "cat ListCourses_sql.txt" );
else
echo "No such option: $_POST[act]";
}
?>
|
mysqli, which allows you to access MySQL database servers,
strtok, which splits string one by one,
strtoupper, which converts all characters to uppercase,
query, which performs query against a database, and
fetch_assoc, which fetchs a result row as an associative array.
|
Criminal: Your money or your life … Benny: [Pause.] Criminal: Look, bud. I said your money or your life. Benny: I’m thinking it over. — Jack Benny |