I created my first homepage with Netscape Composer during my freshman year at UNC (mostly for my folks in Texas). However, it wasn't long before I began peaking at the HTML underlying some more sophisticated corporate sites--especially the code used to create neat imagemap effects. Then some time later I discovered Server-Side Includes while designing a site that had a menu bar throughout. With SSI (and JavaScript) I could have the server (and the client) generate the HTML for me, so I wouldn't have to update something like a menu in a gazillion different places.
Skip to the present, and I am well aware of the separation between a document's content and its style or structure. This separation is so fundamental that many sites keep all of their content in a database---whether it's legacy data or brand new---and then they have a webserver dynamically generate an html page using a predetermined html template that's filled with the content (from the database) requested by the client (the web-browser).
Many of my installations so far have dealt with separate elements of the web database model. I installed Apache and PHP right off the bat, and just recently I installed MySQL, an open source database package. This was not a coincidence--all along I've been working toward setting up a very rudimentary web database application, and until my last installation, I just hadn't gotten far enough. Therefore, the goal of this pseudo-installation is twofold: one, I want to learn how to make my first web database application because I think it's important, and two, I want to leave behind a simple tutorial of the process--for anyone interested in web-database applications, but not knowing exactly where to start.
As I intend this project to build off of my other installations, I'll only gloss over the installation steps here. For further installation notes, see my other pages:
Get the lastest version of apache, detar, decompress, configure, and make:
# lynx http://httpd.apache.org/dist/apache_1.3.14.tar.gz
# tar zxf apache_1.3.14.tar.gz
# cd apache_1.3.14
# ./configure --enable-module=so
# make
# make install
The "--enable-module=so" option allows for Dynamic Shared Objects, which is how I'll install PHP.
Make sure ncurses-devel is installed. The RPM can be found at http://rpmfind.net.
Complete the following steps to download, configure, and install MySQL:
# lynx http://mysql.venoma.org/Downloads/MySQL-3.22/mysql-3.22.32.tar.gz
# tar zxf mysql-3.22.32.tar.gz
# cd mysql-3.22.32
# groupadd mysql
# useradd -g mysql mysql
# ./configure --prefix=/usr/local/mysql
# make
# make install
# scripts/mysql_install_db
# chown -R mysql.mysql /usr/local/mysql
# /usr/local/mysql/bin/safe_mysqld --user=mysql &
# /usr/local/mysql/bin/mysqladmin -u root password 'type password here'
Edit /etc/ld.so.conf and add the following line to ensure system access to MySQL libraries:
/usr/local/mysql/lib/mysql
Excute the following command to update run-time link bindings:
# ldconfig
For more info on the two steps above, see this message posting. <http://bugs.php.net/?id=5488>
The following line (also in the steps above) starts MySQL running as a daemon. This is important because it allows users and scripts to connect to it and run queries, just like someone connecting to a webserver and requesting HTML pages. Therefore it is also important to put the following line in your /etc/rc.d/rc.local file to ensure that MySQL will be restarted if the computer is rebooted:
/usr/local/mysql/bin/safe_mysqld --user=mysql &
Make sure flex and bison (or yacc) are installed. The RPMs can be found at http://rpmfind.net.
Complete the following steps to download, configure, and install PHP:
# lynx http://www.php.net/distributions/php-4.0.3pl1.tar.gz
# cd php-4.0.3pl1
# ./configure --with-mysql=/usr/local/mysql --with-apxs=/usr/local/apache/bin/apxs
# make
# make install
# cp php.ini-dist /usr/local/lib/php.ini
Make sure the following lines exist, and are uncommented in your /usr/local/apache/conf/httpd.conf file. You only need the second line if you want PHP to parse .html files. Otherwise, remove ".html" from the third line
LoadModule php4_module libexec/libphp4.so
AddType text/html .html
AddType application/x-httpd-php .php .html
The following SQL commands give a taste of what it's like to interact with a MySQL database. All commands can be entered at the "mysql>" prompt. Most are self-explanatory, however, beware of DROP and DELETE--depending on how they are used, you can easily hose your entire database with a misqualified command. For more exhaustive information, see MySQL's Language Reference.
Note: to login into MySQL, use this command:
# /usr/local/mysql/bin/mysql -u root -p
mysql> CREATE DATABASE guestbook;
mysql> USE guestbook;
mysql> CREATE TABLE Comments (
-> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> CommentText TEXT,
-> CommentDate DATE NOT NULL,
-> AuthorName VARCHAR(100),
-> AuthorEmail VARCHAR(100)
-> );
mysql> SHOW TABLES;
mysql> DESCRIBE Comments;
mysql> DROP TABLE <tableName>;
mysql> INSERT INTO Comments SET
-> CommentText = "This webpage is fly, you da man!",
-> CommentDate = "2000-11-15",
-> AuthorName = "Joe Blow",
-> AuthorEmail = "joeblow@whatever.com";
mysql> SELECT * FROM Comments;
mysql> SELECT ID, AuthorName FROM Comments;
mysql> SELECT COUNT(*) FROM Comments;
mysql> SELECT CommentText FROM Comments WHERE CommentText LIKE "%fly%";
mysql> UPDATE Comments SET CommentDate="2000-11-14" WHERE ID=1;
mysql> DELETE FROM Comments WHERE CommentText LIKE "%sucks%";
mysql> DELETE FROM <tableName>;
mysql> ALTER TABLE Comments ADD COLUMN
-> AuthorURL VARCHAR(250);
mysql> ALTER TABLE <tableName> DROP COLUMN <columnName>;
What follows is like a typical introduction to any programming language, cataloging basic statement syntax, operators, control structures etc. Also described are URL Query Strings and HTML form interation, which is particularly important when getting data from a user via HTML. Finally there is an example script that generates a muplication table with HTML table formatting, so that the numbers align correctly.
It is also important to mention that the PHP tags (<?php ?>) can be closed at any time so that large chunks of HTML do not all have to be produced using an echo() or printf() function. The PHP tag can even be closed in the middle of a control structure (while loop, etc) as long as the syntax is complete in a later PHP tag. For more exhaustive detail and information, please see PHP.net's Language Reference.
statement:
echo( "This is a <B>test</B>!" );
statement enclosed in <?php ?> tags:
<?php echo("This is a <B>test</B>!"); ?>
variables: (loosely typed)
$testvar = 3; $testvar = "Three";
comments:
// One line comment
/* Multi-line
comment */
operators:
arithmetic: +, -, *, /, %, ++, --
assignment: =, +=,
comparison: >, <, >=, <=, !=, ==
logical: &&, and, ||, or, !, xor
string: . (period = concanentate), .=
strings:
strings enclosed in double quotes " " are parsed for variables
strings enclosed in single quotes ' ' are not parsed for variables
Control Structures:
if ( $name == "Joe" ) {
echo( "Welcome, oh glorious leader!" );
} else { // else is optional
echo( "Welcome, $name!" );
}
if ($a == 5):
print "a equals 5";
elseif ($a == 6):
print "a equals 6";
else:
print "a is neither 5 nor 6";
endif;
$count = 1;
while ($count <= 10) {
echo( "$count " );
$count++;
}
URL query string:
<A HREF="welcome.php?name=Joe"> Hi, I'm Joe! </A>
-informs PHP that variable "name" equals "Joe"
<A HREF="welcome.php?firstname=Joe&lastname=Blow"> Hi, I'm Joe Blow! </A>
-separate mulitple variables with an ampersand (&)
HTML Form Interaction:
<FORM ACTION="welcome.php" METHOD=GET>
First Name: <INPUT TYPE=TEXT NAME="firstname"><BR>
Last Name: <INPUT TYPE=TEXT NAME="lastname">
<INPUT TYPE=SUBMIT VALUE="GO">
</FORM>
-creates firstname and lastname variables
-METHOD=GET causes variables to be passed as a query string, might be undesirable with textareas in your form.
-Use METHOD=POST to pass variables transparently. However, dynamic pages w/o query string URLs are not bookmarkable.
Example Script:
<HTML>
<HEAD><TITLE>PHP Script: Multiplication Table</TITLE></HEAD>
<BODY>
<TABLE border=1>
<?php
$i=1;
while ($i <= 10) {
echo ( "<TR>" );
$j=1;
while ($j <=10) {
echo ( "<TD ALIGN='right'>" . $i*$j . "</TD>" );
$j++;
}
echo ("<TR>\n");
$i++
}
?>
</TABLE>
</BODY>
</HTML>
Like any other programming language, PHP contains subject-oriented blocks of related functions. As PHP is used often for database access, many of these function groups relate to specific database platforms, including dBase, Informix, MySQL, Oracle 8, etc. For simplicity I've listed below only those functions necessary for a simple MySQL web-database application. More complete reference on PHP's MySQL functions can be found here.
int mysql_connect (string hostname, string username, string password)
Returns a positive MySQL link identifier on success, or an error message on failure. Establishes a connection to a MySQL server.
int mysql_select_db (string database_name, int link_identifier)
Returns true on success, false on error. Sets the current active database on the server that's associated with the specified link identifier.
int mysql_query (string query, int link_identifier)
Sends a query to the currently active database on the server that's associated with the specified link identifier. Note: The query string should not end with a semicolon. Returns TRUE (non-zero) or FALSE to indicate whether or not the query succeeded. A return value of TRUE means that the query was legal and could be executed by the server. For SELECT statements, mysql_query() returns a new result identifier, allowing access to the selected records.
array mysql_fetch_array (int result)
Returns an array that corresponds to the fetched row, or false if there are no more rows. mysql_fetch_array()s called in succession each return the next row of a SELECT query, returning FALSE when there are no rows remaining.
Note: Placing an @ symbol in front of the function name tells the function to fail silently. This allows the user to display a friendlier error message.
What follows is the process necessary to create a webpage guestbook. I kept the functionality as basic as possible in order to highlight the web-database process, rather than the results.
First I've outlined the steps necessary to create and configure a guestbook database in MySQL. What follows then is a heavily commented PHP script that both accepts new guestbook entries, as well as lists the current entries in the database. Additional functionality that could be added later might include a way for the Webpage Designer to remove (DELETE) unfavorable entries, or to modify (UPDATE) entries with vulgarity or spelling errors.
# /usr/local/mysql/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 166 to server version: 3.22.32
Type 'help' for help.
mysql> CREATE DATABASE guestbook;
mysql> USE guestbook;
mysql> CREATE TABLE Comments (
-> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> CommentText TEXT,
-> CommentDate DATE NOT NULL,
-> AuthorName VARCHAR(100),
-> AuthorEmail VARCHAR(100)
-> );
mysql> INSERT INTO Comments SET
-> CommentText = "This webpage is fly, you da man!",
-> CommentDate = "2000-11-15",
-> AuthorName = "Joe Blow",
-> AuthorEmail = "joeblow@whatever.com";
<HTML>
<HEAD>
<TITLE> My HomePage - with a guest book at the bottom </TITLE>
<HEAD>
<BODY>
<H1>This is my fantastic homepage</H1>
I'll be putting more here later. Right now the page is under construction. Please submit your comments below. Thanks.<BR>
--The Web Designer
<?php
// Connect to the database server:
// mysql_connect() will return a number ($databaseLink) to be used in other functions.
// If it returns a FALSE, the if statement will echo an error message, and the
// exit() command with halt the php script
$databaseLink = @mysql_connect("localhost", "root", "password");
if (! $databaseLink) {
echo( "<P>Unable to display guest book at this time.</P>" );
exit(); // Halts the PHP script
}
// Select the guestbook database:
// mysql_select_db() both selects the database "guestbook" and returns a boolean value
// depending on the success of that selection. If it returns FALSE, the if statement
// echoes an error message and halts the php script
if (! @mysql_select_db("guestbook", $databaseLink) ) {
echo( "<P>Unable to locate the database at this time.</P>" );
exit();
}
// Form to give web page a comment:
// The following code is not php, it is straight HTML. However, there is one expection
// in the first line: <?php echo($PHP_SELF); ?>. This is a simple php echo() statement
// embedded in the HTML, and the variable $PHP_SELF is the relative name of the page.
// the text it produces is "/homepage.php". Notice also that the FORM method is POST.
// This means that any data, which be transmitted back to this page itself, will be
// sent transparently to the webserver (and not part of a URL query string).
?>
<FORM ACTION="<?php echo($PHP_SELF); ?>" METHOD=POST>
<P>Type your comment here:<BR>
<TEXTAREA NAME="commenttext" ROWS=2 COLS=60 WRAP></TEXTAREA><BR>
Leave your name:
<INPUT TYPE=TEXT NAME="authorname" SIZE=20><BR>
And email address:
<INPUT TYPE=TEXT NAME="authoremail" SIZE=35><BR>
<INPUT TYPE=SUBMIT NAME="submitcomment" VALUE="Add your comment">
</FORM>
<?php
// If a comment is submitted, add it to the database:
// The follow code is only enacted if the form above is submitted, in which case
// the page itself is sent the values. The code below simply creates a query command
// in the variable $sql and uses the function mysql_query() to process that query
// As this is not a SELECT query, the only data that mysql_query() will return is a
// boolean value representing the success of the query. If it returns FALSE, then
// the if statement will produce an error message.
if ($submitcomment == "Add your comment") {
$sql = "INSERT INTO Comments SET " .
"CommentText='$commenttext', " .
"CommentDate=CURDATE()," .
"AuthorName='$authorname', " .
"AuthorEmail='$authoremail'";
if (!mysql_query($sql)) {
echo("<P>Error adding submitted comment: " . mysql_error() . "</P>");
}
}
// Request the text of all the comments:
// Regardless of whether the code above is excuted, the following code sends
// a SELECT query to the database, asking for all of the fields and records.
// The SELECT query should return data which is pointed to by the variable $selectResult.
// Of course if the query fails, the if statement will produce an error message.
$selectResult = mysql_query("SELECT * FROM Comments", $databaseLink);
if (!$selectResult) {
echo("<P>Error performing query: " . mysql_error() . "</P>"); // Prints error message
exit();
}
// Display the results of the SELECT query above:
// The mysql_fetch_array() function is interesting, in that it returns each succesive
// row of a SELECT query (specified by the $selectResult variable), finally returning
// FALSE when there are no more rows. Each row is assigned to $row as an array, each
// element of which has the same name as the database field name. Thus the echo()
// statements output the data for that record with little bits of formatting, including
// treating the email address as a mailto link of the author's name
while ($row = mysql_fetch_array($selectResult) ) {
echo("<B>" . $row["CommentText"] . "</B>");
echo(" posted on: " . $row["CommentDate"] . " by ");
echo("<A HREF='mailto:" . $row["AuthorEmail"] . "'>" . $row["AuthorName"] . "</A>");
}
?>
</BLOCKQUOTE>
</BODY>
</HTML>
INLS 183 Project 8: Web Database Tutorial script file