MySQL-driven Web Site Construction (Cont.)
  1. Calling a PHP Script with Embedded SQL
    2.  The following PHP program shows how to access the your-db database and the books table and list the book titles, each of which contains a substring $_POST['title'] entered from the Web. If the substring $_POST['title'] is empty, then list all titles. Everything printed from PHP is displayed on the browser.

    ☂A PEM file is a text-based file that contains a certificate, a private key, and any associated certificates. The file DigiCertGlobalRootCA.crt.pem can be found from here.

    ~/public_html/course/515/3/ListTitles.php 
    <html><body>
<?php

if ( $_POST['act'] == "List the titles" ) {

  /************************************************************

  This program shows how to list the book titles from the
    books table.

  To use this program, you need to create a table
    books by using the following command:

  mysql> create table books (
      ->   title   char(128),
      ->   ISBN    char(20) primary key,
      ->   price   real );
  Query OK, 0 rows affected (0.07 sec)

  *************************************************************/

  $host     = "undcemmysql.mysql.database.azure.com";
  $username = "your_id";
  $database = "your_db";
  $password = "your-pw";
  
  // 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 title from books where ";
  $query  = $query . "title like '%" . $_POST['title'] . "%'";

  // Execute the query.
  $result = $conn->query( $query );
  $row    = $result->fetch_assoc( );
  if ( $result->num_rows > 0 )
    // Print the results row by row.
    do
      echo $row['title'];
    while( $row = $result->fetch_assoc( ) );

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

elseif ( $_POST["act"] == "Help" ) {
  echo  "No help at this moment.";
}
else {
  echo  "No such option: " . $_POST["act"];
}

?>
</body></html>

  2. Testing the Exercise Thoroughly
    3.