Slide 4.17: HTML ⇒ Perl ⇒ PHP ⇒ SQL

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:

Drop the table Reports if requested.

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) );

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="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

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>";

 $username = "your-id@undcsmysql";
 $password = "your-pw";
 $database = "your-db";
 $host     = "undcsmysql.mysql.database.azure.com";
 $conn     = new mysqli( $host, $username, $password, $database );

 if ( $conn->connect_error )
   die( 'Could not connect: ' . $conn->connect_error );

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

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