HTML ⇒ Perl ⇒ PHP ⇒ SQL

A capable programmer is not limited by specific languages. Use the languages when they are the most appropriate for the need; for example, Perl is good at string processing, PHP is designed for web processing, and SQL is a non-procedural language. This slide shows how to connect the following programs: HTML ⇒ Perl ⇒ PHP ⇒ MySQL. The INSERT INTO statement is used to insert new records into a database table. The following example performs the tasks below:
  1. Drop the table Reports if requested.
  2. Create the table Reports if requested. The SQL command is as follows: 

    CREATE TABLE  Reports (
  reportID  INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY ( reportID ),
  title  VARCHAR(32) );

  3. Insert the title values from the Web and then display the table contents. The strtok function splits a string into smaller strings.
~/public_html/course/515/4/17.html   (HTML)   (text) 
<html>
   ...
 <form method="post" action="http://undcemcs01.und.edu/~wen.chen.hu/cgi-bin/515/4/Connect.pl">
  <input name="choice" type="radio"  value="Drop"> Drop table
  <input name="choice" type="radio"  value="Create" checked="checked"> Create table
  <input name="choice" type="radio"  value="Insert"> Insert
    titles <input name="titles" type="text" size="32" value="PHP MySQL Linux Apache">
  <input name="choice" type="radio"  value="Select"> Selct all
  <input name="choice" type="radio"  value="Delete"> Delete all
  <input name="act"    type="submit" value="Check result">
  <input name="act"    type="submit" value="Help">
  <input type="reset"                value="Reset">
 </form>
   ...
</html>
http://undcemcs01.und.edu/~wen.chen.hu/course/515/4/17.html

    Drop table   Create table   Insert titles

    Selct all   Delete all                    

    Password:                    
~/public_html/cgi-bin/515/4/Connect.pl 
#!/usr/bin/perl
use CGI;
$query  = new CGI;
$act    = $query->param( 'act'    );
$choice = $query->param( 'choice' );
$titles = $query->param( 'titles' );
print ( "Content-type: text/html\n\n" );

if ( $act eq "Check result" ) {
  # Remove leading and trailing spacing.
  $titles =~ s/^\s*(\S*)\s*$/$1/;
  # For security, remove some Unix metacharacters.
  $titles =~ s/;|>|>>|<|\*|\?|\&|\|//g;
  # Compose a PHP command.
  system( "php Connect.php $choice '$titles'" );
}
elsif ( $act eq "Help" ) {
  system( "cat Help.html" );
}
else {
  print( "No such option: <em>$act</em>" );
}
~/public_html/cgi-bin/515/4/Connect.php 
<?php
 echo  "<html>
  <body>
   <table width='100%' height='80%'>
    <tr>
     <td align='center' valign='middle'>
      <h3>";

 $host     = "undcemmysql.mysql.database.azure.com";
 $username = "your_id";
 $password = "your-pw";
 $database = "your_db";
 // 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, $username, $password, $database, 3306 );

 // If fail to connect to the database
 if ( $conn->connect_errno )
   die( 'Failed to connect to MySQL: ' . $conn->connect_error );

 if ( $argv[1] == "Drop" ) {
   $query = "DROP TABLE  Reports";
   echo  "<font color='#3366CC'><code>$query;</code></font>";
   if ( $conn->query( $query ) )
     echo "Table <em>Reports</em> dropped";
   else
     echo "Error dropping table <em>Reports</em>: " . $conn->error;
 }
 elseif ( $argv[1] == "Create" ) {
   // Create table Reports.
   $query = "CREATE TABLE  Reports (
             reportID int NOT NULL AUTO_INCREMENT,
             PRIMARY KEY ( reportID ),
             title  VARCHAR(32) )";
   echo  "<font color='#3366CC'><code>$query;</code></font>";
   if ( $conn->query( $query ) )
     echo "Table <em>Reports</em> created";
   else
     echo "Error creating table <em>Reports</em>: " . $conn->error;
 }
 elseif ( $argv[1] == "Insert" ) {
   $title = strtok( $argv[2], " " );
   while ( $title != false ) {
     $query = "INSERT INTO Reports ( title ) VALUES ( '$title' )";
     echo  "<font color='#3366CC'><code>$query;</code></font>";
     if ( !$conn->query( $query ) ) {
       echo "Error inserting report: " . $conn->error;
       die( "unable to insert a report" );
     }
     $title = strtok( " " );
   }
 }
 elseif ( $argv[1] == "Select" ) {
   $query = "SELECT * FROM  Reports";
   echo  "<font color='#3366CC'><code>$query;</code></font>";
   $result = $conn->query( $query );
   $row    = $result->fetch_assoc( );
   if ( $result->num_rows > 0 )
     do
       echo $row['reportID'] . ", " . $row['title'];
     while( $row = $result->fetch_assoc( ) );
 }
 elseif ( $argv[1] == "Delete" ) {
   $query = "DELETE FROM  Reports";
   echo  "<font color='#3366CC'><code>$query;</code></font>";
   if ( $conn->query( $query ) )
     echo "Table Reports deleted";
   else
     echo "Error deleting table Reports: " . $conn->error;
 }
 $conn->close( );

 echo  "      </h3>
    </td>
   </tr>
  </table>
 </body>
</html>";
?>




      I bought the world’s worst thesaurus yesterday.    
      Not only is it terrible, it’s also terrible.