Dynamic User Interfaces and Hyperlinks

This slide shows how to dynamically generate user interfaces including a drop-down list, checkboxes, and hyperlinks to a server for processing. If you select some options below and click on the “Submit” button, the browser will perform the following steps: 
11.html ⇒ Select.php ⇒ Checkbox.php ⇒ Hyperlink.php ⇒ course details
to list the course details taken by students. This example uses a many-to-many (N-N) relationsnship in Week 2, which has a student take many courses and a course be taken by many students. Below gives its ER diagram, where

⇓ Transforming (N-N relationship)
course
cid title room credit
CSCI260 .NET and World Wide Web Programming CEC 205 3
DATA520 Databases Upson II 265 3
DATA525 Data Engineering and Mining Harrington 218 3
CSCI513 Advanced Databases CEC 205 3
... ... ... ...
 
student
sid name email
1 Super Mario super.mario@und.edu
2 Poke Mon poke.mon@ndus.edu
110 Digi Mon digi.mon@und.edu
24 Sponge Bob sponge@gmail.com
... ... ...
take
sid cid semester
36 CSCI260 Fall 2022
2 DATA520 Spring 2022
2 CSCI513 Fall 2023
38 CSCI370 Spring 2023
... ... ... 

  <form method="post" action="Select.php"> 
    <select name="courses[ ]" multiple size="3">
     <option value="CSCI260" /> CSCI260
     <option value="DATA520" /> DATA520 
     <option value="DATA525" /> DATA525
     <option value="CSCI513" /> CSCI513
    </select>
    <input type="submit" name="act" value="Submit" />
    <input type="submit" name="act" value="HTML source" />
    <input type="submit" name="act" value="Select PHP source" />
    <input type="submit" name="act" value="Checkbox PHP source" />
    <input type="submit" name="act" value="Hyperlink PHP source" />
    <input type="submit" name="act" value="SQL source" />
    <input type="submit" name="act" value="Help" />
    <input type="reset"             value="Reset" />
  </form>
         

     
http://undcemcs01.und.edu/~wen.chen.hu/course/457/7/Select.php 
<?php

if ( $_POST['act'] == "Submit" ) {
  header( "Content-type:text/html; charset=UTF-8" );

  $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 DISTINCT s.sid, s.name FROM student1 s, take1 t ";
  $query .= "WHERE s.sid=t.sid AND ( ";
  $c      = $_POST['courses'];
  if ( empty( $c ) )  $query .= "cid LIKE '%%' )";
  else {
    for ( $i=0;  $i < count($c);  $i++ )
      if ( $i == 0 )  $query .= "cid='" . trim( $c[$i] );
      else            $query .= "' OR cid='" . trim( $c[$i] );
    $query .= "' )";
  }
  echo( $query );

  echo( "The following students take the above selected courses:" );
  echo( "
" );

  // 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'] );

  echo( "" );
  echo( "
" );

  // Close the database connection.
  $conn->close( );
}

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"] == "Select PHP source" )
    system( "cat Select_php.txt" );
  elseif ( $_POST["act"] == "Checkbox PHP source" )
    system( "cat Checkbox_php.txt" );
  elseif ( $_POST["act"] == "Hyperlink PHP source" )
    system( "cat Hyperlink_php.txt" );
  elseif ( $_POST["act"] == "SQL source" )
    system( "cat Dynamic_sql.txt" );
  else
    echo "No such option: $_POST[act]";
}
?>
http://undcemcs01.und.edu/~wen.chen.hu/course/457/7/Checkbox.php 
<?php

if ( $_POST['act'] == "Submit" ) {
  header( "Content-type:text/html; charset=UTF-8" );

  $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 c.cid, c.title FROM course1 c, take1 t WHERE c.cid=t.cid AND ( ";
  $s     = $_POST['s'];
  if ( empty( $s ) )  $query .= "t.cid LIKE '%%'";
  else {
    for ( $i=0;  $i < count($s);  $i++ )
      if ( $i == 0 )  $query .= "t.sid='" . trim( $s[$i] );
      else            $query .= "' OR t.sid='" . trim( $s[$i] );
    $query .= "' )";
  }
  echo( $query );

  echo( "The following courses are taken by the previously selected students:" );

  // 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['title'] . "";

  // Close the database connection.
  $conn->close( );
}
?>
http://undcemcs01.und.edu/~wen.chen.hu/course/457/7/Hyperlink.php?cid= 
<?php
  header( "Content-type:text/html; charset=UTF-8" );

  $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 * FROM course1 WHERE cid='" . $_GET['cid'] . "'";
  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['cid'];
      echo $row['title'];
      echo $row['room'];
      echo $row['credit'];
    }

  // Close the database connection.
  $conn->close( );
?>