12.8 Project | Open Source Development with LAMP: Using Linux, Apache, MySQL, Perl, and PHP

The PHP project is a simple journal-entry manager. Several PHP pages are implemented: a launching point, a page to add an entry, a page to view the last ten entries with the option of searching for matching entries, and a page to view the details about a specific entry. The journal entries are stored in a MySQL database and dynamically displayed in the PHP pages.

12.8.1 Journal MySQL Database

First, the database is created. The database is named journal , and there is one table named entries . The entries table has four fields:

id ” This is a unique integer to identify the entry that the user wants to see displayed; it is an auto_increment field (discussed later) and the key into the table.
date ” This is the date and time the item was entered into the table; we discuss this data type later.
title ” This is the title of the entry, a 60-character text field.
text ” This is the text of the entry, a BLOB .

The id is defined as an auto_increment integer. This is a special type of integer that does not need to be assigned a value when a row is entered in the table ”it automatically takes the value of the most recent (or highest) value in the table plus 1.

For instance, when the first record is added, id is automatically assigned 1. When the second record is added, it is assigned 2, and so on for all the records. This data type ensures a sane unique value for each key entered into the table, and the programmer doesn't have to do any extra work to ensure that it receives a good value.

The date is defined as a datetime data type, which is a string that resembles 2002-04-19 07:36:09 , or the year-month-day hour :minute:second. Each record is stamped with the time that it was entered into the database, so the most recent entries can easily be grabbed by selecting them ordered by date in descending order. This field also tells the user when the entry was added. MySQL has many built-in commands to process dates, the most important of which is the now() function, which returns the current date/timestamp. Here is an example of the output of this function:

 mysql> select now();  +---------------------+   now()                 +---------------------+   2002-04-20 13:14:24   +---------------------+  1 row in set (0.00 sec)

To create the database, log in as the root MySQL user:

 $mysql  -uroot -p  Enter password:  Welcome to the MySQL monitor. Commands end with ; or \g.  Your MySQL connection id is 998 to server version: 3.23.41  Type help; or \h for help. Type \c to clear the buffer.  mysql>  CREATE DATABASE journal;  Query OK, 1 row affected (0.00 sec)  mysql>  GRANT SELECT,INSERT,UPDATE,DELETE,CREATE  ->  ON journal.*  ->  TO apache@localhost;  Query OK, 0 rows affected (0.03 sec)  mysql>  QUIT  Bye

Here we create the new database and grant the apache user privileges to create, insert, etc.

Now, in the apache persona, we can create the table. We could show you the MySQL commands necessary to create the table, but in the spirit of laziness , we have provided the database for you to create it from the command line:

 $  cd /var/www/misc  $  mysql -uapache -pLampIsCool journal < journal.sql

Now that the database is created (including some helpful entries), we can work on the PHP pages to display it.

12.8.2 Project Pages

First, we have the launching page. This page contains a brief description of the journal manager, and two links. One link goes to the page to add an entry, and the other link goes the page to view the entries.

You can view this page by checking out www.opensourcewebbook.com/php/project/ or http://localhost/php/project/ . The result can be seen in Figure 12.13.

Figure 12.13. PHP project launch page

graphics/12fig13.gif

This page requires no PHP code because it is just text and two links, so there is no reason to show the code. However, if you want to see it, check out /var/www/html/php/project/index.html .

Next , we have the page to add an entry. To see the page, go to www.opensourcewebbook.com/php/project/add/ or http://localhost/php/project/add/ , shown in Figure 12.14.

Figure 12.14. PHP poject add page

graphics/12fig14.gif

The content of this file is in /var/www/html/php/project/add/index.php . Note that Apache comes configured in Red Hat 7 to serve up index.php in the add directory by default (if there is no index.html , the normal default).

The user is presented with a form in which they can add the new journal entry. Also, there is a link at the bottom of the page to view the journal entries. Supposedly, if the user requests this page, they want to add an entry, so let's add one. We enter the title "PHP Project" and the text "A journal entry for the PHP Project." Clicking the Add button posts the data to the same page ( add/index.php , as specified in the <form> tag). A response is given so that the user knows the data was entered correctly, and then the form is displayed again so another entry can be added.

That's how it's supposed to work; now it's time to look at the code. First, we have the form:

 <form action="/php/project/add/" method="post">  <table>    <tr>      <td>Title:</td>      <td><input type="text" name="title" width="60">    </tr>    <tr>      <td valign="top">Text:</td>      <td><textarea name="text" cols="40" rows="5"></textarea></td>    </tr>    <tr>      <td colspan="2">          <input type="submit" value="Add Entry">          <input type="reset" value="Reset Entry">      </td>    </tr>  </table>  </form>

The action is /php/project/add/ , which is the index page in that directory, or in this case, the same file in which this form resides. Then two fields are created: a text field that is named title and a text area field named text . These two widgets are posted to our program, so we need to grab their values. Also, the method for this form is post , so our program must grab the posted data. It does so at the top of the file:

 <?      $title = $HTTP_POST_VARS["title"];      $text = $HTTP_POST_VARS["text"];

Here is the section of PHP code where the values in the title and text fields are put into variables named $title and $text .

This is followed by the code to insert the data into the table:

 if ($title and $text) {      // we have posted data -- first check its length      // so no one nasty is posting a 10meg text value      if (strlen($title) < 60 and strlen($text) < 1000) {          // connect to the server          $mysql = mysql_connect("localhost", "apache", "LampIsCool")                           or die("could not connect to mysql");          // select the "journal" database          mysql_select_db("journal")             or die("select failed-".mysql_errno() . ": " .                    mysql_error());          // insert into the table          mysql_query("INSERT INTO entries (title,text,date)                       VALUES ($title,$text,now())")             or die("query failed-".mysql_errno() . ": " .                    mysql_error());      } else {          // clear out the title and the text          $title = "";          $text = "";      }  }  ?>

First, the code checks to see whether it has received the title and the entry text in posted data with if ($title and $text) . If so, it does a sanity check on the data, making sure that the length of the input data is not too big. Then, a connection is made to the server, selecting the journal database. We then insert the title, the entry text, and the current date/timestamp (the result of the now() function).

"Hey," you say to yourself, "isn't there a potential problem with how we are inserting?" We use $title in the INSERT command, but what if the user enters a string into the title widget such as "I'm hacking PHP code?" Won't the single quote in the string break the MySQL command because the string itself is contained in single quotes? Nope, because PHP automagically escapes single quotes. PHP escapes double quotes as well.

The last bit of interesting code in this page is found right above the form tag:

 <?      if ($title and $text) {          print "<p>Entry added! Fill out the form below to add another                entry.";          print "Or, click the link below to see the added entry.</p>";      }else{          print "<p>Fill out the form below to add an entry:</p>";      }  ?>

This code determines whether a title and an entry text have been given. If so, give the user a nice message saying that all is well. Otherwise, the original message is shown.

By the way, the preceding code could have been written as:

 <? if ($title and $text) : ?>  <p>Entry added! Fill out the form below to add another entry.  Or, click the link below to see the added entry.</p>  <? else : ?>  <p>Fill out the form below to add an entry:</p>  <? endif; ?>

TMTOWTDI in PHP as in all things LAMP.

Now, we have the view page. To see the page, go to either www.opensourcewebbook.com/php/project/view/ or http://localhost/php/project/view/ . This resembles Figure 12.15. The contents of the page can be found in the file /web/www/html/php/project/view/index.php . This page presents the user with an option to search the entries to narrow the search results. The fields searched for the data are title and text .

Figure 12.15. PHP project view page

graphics/12fig15.jpg

Then a list of all entries matching the search string (or all the entries if there is no search string) is shown. Actually, only the ten most recent entries are shown, because eventually there might be hundreds of journal entries.

The data displayed is the entry's date and title. Clicking the title shows the details for the specific entry (we'll look at that page in a bit).

This code is found at the top of the file:

 <?      $search = $HTTP_POST_VARS["search"];      // connect      $mysql = mysql_connect("localhost", "apache", "LampIsCool")          or die("could not connect to mysql");      // select the "journal" database      mysql_select_db("journal")          or die("select failed - " . mysql_errno() . ": " . mysql_error());      if ($search) {          $query = "SELECT id,date,title                    FROM entries                    WHERE title LIKE %$search%                          OR text LIKE %$search%                    ORDER BY date DESC                    LIMIT 10";      } else {          $query = "SELECT id,date,title                    FROM entries                    ORDER BY date DESC                    LIMIT 10";      }      // execute the MySQL query, grab the result in $result      $result = mysql_query($query)          or die("query failed - " . mysql_errno() . ": " . mysql_error());  ?>

First, $search is assigned the search string (if any) entered into the search string widget. Then a connection is made to the server, and the journal database is selected.

If a search string is entered and stored in $search , a query is made that includes WHERE title LIKE %$search% OR text LIKE %$search% . This query uses the WHERE ... LIKE clause. If we say WHERE title LIKE %$search% , the LIKE pattern works just like a shell glob, where " % "is like " * ". So, %foo% means "any text followed by foo followed by any text," much as we would say in the shell as *foo* .

Therefore, our LIKE clause reads as "where the field title matches the pattern %$search% , or any text followed by the value of the variable $search followed by any text." Or, more simply, match a title that contains our search string somewhere in it.

The search string in the text field is also checked. If either of those fields contains the search pattern, the item is selected.

This query returns the ten (with LIMIT 10 ) most recent (with ORDER BY date DESC )entries. If no search string is stored in $search , the query simply returns the ten most recent entries.

Later in the page you see this code:

 <?    if ($search) {      print "<p>Search string: <b>" . $search . "</b></p>";    }  ?>

The purpose of this is to tell the user what search criteria they entered. It's just a nice habit to verify such things.

Then we see this code:

 <? if (mysql_num_rows($result) > 0) : ?>      .      .      .  <? else : ?>  <p>Sorry, no records found. Please try again.</p>  <? endif; ?>

If the query returned at least one row of data (meaning the search returned some records), display the result (shown next). Otherwise, tell the user that no records were found. Here is the code to display the table (contained in the if block):

 <p>Click on the title of one of the entries below to see the details.</p>  <table border="0" cellspacing="0" cellpadding="5">    <tr>      <th align="left">Date</th>      <th align="left">Title</th>    </tr>  <?    $i=0;    while ($array = mysql_fetch_array($result)) {      if ($i%2==0){          $bgcolor = "#ffffff";      }else {          $bgcolor = "#cccccc";      }      print " <tr bgcolor=" . $bgcolor . ">";      print " <td>" . $array["date"] . "</td>";      print  <td valign="top"><a href="/php/project/view/entry/?id=            .$array["id"] . "><font color="#999966"><b>            .$array["title"] . </b></font></a></td>;      print " </tr>";      $i++;    }

This prints the HTML to start the table and for the first row of the table. Then, the records that were returned by the SELECT are looped through, printing the HTML for the record, including different background colors for the even elements and the odd elements. The HTML for each entry includes a link to /php/project/view/entry/ passing the ID into that page. Then we see this code:

 <?      // free memory      mysql_free_result($result);      // we are all done, so close the MySQL connection      mysql_close($mysql);  ?>

Here the MySQL connection is cleaned up nicely . Finally, the following code is seen:

 <?      if ($search) {          print "<p><a href=\"/php/project/view/\">                    <font color=\"#999966\">";          print "<b>View all entries</b></font></a>";          print "<hr>";      }  ?>

The purpose of this code is to provide a link to the user who entered the search string. This link goes to the view page with no search string, thereby showing all entries (or at least the last ten entries) without searching.

The final page to examine is the page displaying the detail of one of the entries. For an example, go to www.opensourcewebbook.com/php/project/view/entry/?id=6 or http://localhost/php/project/view/entry/?id=6 . This resembles Figure 12.16. You can find this file online at /var/www/html/php/project/view/entry/index.php .

Figure 12.16. PHP project view entry page

graphics/12fig16.gif

This page is the detail information for journal entry 6. It displays the date the entry was added, its title, and its text. At the top of this page is the following PHP code:

 <?      $id = $HTTP_GET_VARS["id"];      // flag to tell us if the record was found      $found = 0;     if ($id>0){          // we have an id number, so first          // connect          $mysql = mysql_connect("localhost", "apache", "LampIsCool")              or die("could not connect to mysql");          // select the "journal" database          mysql_select_db("journal")              or die("select failed - " . mysql_errno() . ": " .                      mysql_error());                // execute the MySQL query, grab the result in $result          $result = mysql_query("SELECT title,text,date                                 FROM entries                                 WHERE id = $id")              or die("query failed-".mysql_errno() . ": " .                      mysql_error());          // see if we found a record          if (mysql_num_rows($result) == 1) {              // grab the result in $array              $array = mysql_fetch_array($result);              // we found a record, so set the flag              $found = 1;          }          // free memory          mysql_free_result($result);          // we are all done, so close the MySQL connection          mysql_close($mysql);      }  ?>

First, the ID number for the journal entry is stored in $id . Then, if there is an ID number and it is greater than 0, we connect to the server, select the journal database, and grab the title , text , and date from the record for the given id . Then, we make sure we have a record, and if so, the data is stored in $array and the flag is set to true. Then the connection is cleaned up.

Later in the page, there is this code:

 <? if ($found) : ?>  <table border="0" cellspacing="0" cellpadding="5">    <tr><th valign="top">Date:</th><td><? echo $array["date"]; ?></td></tr>    <tr><th valign="top">Title:</th><td><? echo $array["title"];                               ?></td></tr>    <tr><th valign="top">Text:</th><td><? echo $array["text"]; ?></td></tr>  </table>  <? else :?>  <p>Sorry, id <b><? echo $id; ?></b> was not found.</p>  <? endif; ?>

This code checks to see whether a record was found. If so, a table is built displaying the date , title , and text values for that entry, each of which is stored in the variable $array . If the record was not found, the user is told, sorry, better luck next time.