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 ⇒ PHP ⇒ HTML
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="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>
~/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.