Build a Support Knowledge Base using DB2 pureXML and PHP

Create an application that uses relational and XML data with IBM DB2 Express-C

Creating applications that use a hybrid of relational data and XML data is easy thanks to the pureXML® feature of IBM® DB2® database servers. In this tutorial, you use PHP to create a Web application that connects to an IBM DB2 Express-C database and stores some of its data in traditional relational database columns, and some of it in native XML columns. You also learn how to use SQL/XML queries to retrieve, insert, update, and delete data from this database. Beyond the hands-on, project-based training, the tutorial equips you with the skills and conceptual knowledge you need to develop your own hybrid applications.

Joe Lennon, Software developer, Core International

Joe Lennon is a 24-year-old software developer from Cork, Ireland. Joe is author of the forthcoming Apress book Beginning CouchDB, and has contributed several technical articles and tutorials to IBM developerWorks. In his spare time, Joe likes to play football (soccer), tinker with gadgets and work on his Xbox 360 gamer score.



19 November 2009

Also available in Portuguese

Before you start

This tutorial is for Web application developers who want to develop database-driven applications that are powered by an IBM DB2 database. You should be familiar with basic HTML, CSS, and PHP code in order to follow the tutorial. You should also have some experience with database management systems and the SQL language.

About this tutorial

One of the most useful and versatile technologies available in IBM DB2 is its native support for XML data through the pureXML feature. PureXML allows you to store, retrieve, and manipulate XML data alongside and in the same manner as relational data. This enables you to develop applications that harness the advantages of both relational databases and XML data. Such applications can be particularly useful when you have a large amount of XML data that you need to leverage without having to first convert it into a relational structure.

This tutorial gives you step-by-step instructions on how to use PHP to create a Support Knowledge Base system that stores its data using a combination of traditional DB2 relational columns and pureXML columns. The application uses the power of SQL/XML to map XML data as a relational column. This allows you to use PHP to retrieve the data as though it were stored in a relational manner.

The tutorial instructions are written with the assumption that you are creating the application on a local development server running Windows® XP with DB2 Express-C, Apache HTTP Server, and PHP installed. You should be able to adapt the instructions to other systems and configurations, but they have not been tested.

Application layout

The directory structure for the application is relatively simple. You will store the application in a subdirectory named kbase in your Apache installation's htdocs directory. On Windows systems, this directory is commonly found in the path C:\Program Files\Apache Software Foundation\Apache 2.2\htdocs. Beneath the kbase directory there will be four subdirectories:

  • classes — contains the PHP classes that are used to pull data from the database and into the application.
  • css — includes the CSS style sheet file that defines the look and feel of the Web application's user interface.
  • includes — contains files with the code for the application's header, navigation sidebar, and footer. PHP scripts include these files at the start and end of each page so that you don't have to copy the same code into each page's source file.
  • sql — contains the database SQL script that creates the DB2 database and its tables. You will use this script in the next section of this tutorial.

The application contains a series of Web pages that can be categorized as follows:

  • Administration view — contains pages that allow you to create and manage categories, articles, and comments.
  • Client (end user) view — contains the application home page, the search results page, and the pages for viewing a category and details of an article.

Prerequisites

In order to follow the steps in this tutorial, you need to have the following software installed:

A thorough guide to installing and configuring the above software is available in the developerWorks article series, "Leveraging pureXML in a Flex microblogging application" (see Resources for a link). Part 1 of this series shows you how to install DB2 Express-C. Part 3 has in-depth instructions on installing Apache HTTP Server and PHP.


Creating the DB2 database

In this section, you create the database for the Knowledge Base application. You need to have IBM DB2 Express-C installed and you need access to the DB2 Command Editor tool. The database for the application contains four tables, each of which includes a combination of regular columns and XML columns.

Create the database with SQL and DB2 Command Editor

The first step in creating the application is to create a new IBM DB2 database. From Windows, launch the DB2 Command Editor (Figure 1) as follows:

Start > Programs > IBM DB2 > DB2COPY1 (Default) > Command Line Tools > Command Editor

Figure 1. DB2 Command Editor
Screenshot of the DB2 Command Editor with nothing entered.

The main Command Editor interface is split into two sections. The top section is the editor area. This is where you enter the commands you want to issue to the database. The bottom section is the result area. This area displays the messages that the database responds with after you execute statements in the editor area.

Copy and paste the code from Listing 1 into the editor area of your DB2 Command Editor. Execute the code by clicking on the green arrow icon on the far left of the tool bar in the editor area . Please be patient as it may take a minute or so for the code to complete.

Listing 1. database.sql
CREATE DATABASE kbase USING CODESET UTF-8 TERRITORY us;
CONNECT TO kbase;

CREATE TABLE category (
    id        int not null generated by default as identity,
    data            xml not null,
    primary key(id)
);

CREATE TABLE article (
    id        int not null generated by default as identity,
    date_created    timestamp not null,
    date_modified    timestamp not null,
    view_count        int,
    category_id        int not null,
    data            xml not null,
    primary key(id),
    foreign key(category_id) references category(id) 
        on delete cascade
);

CREATE TABLE comment (
    id        int not null generated by default as identity,
    date_left        timestamp not null,
    approved        smallint not null,
    article_id        int not null,
    data            xml not null,
    primary key(id),
    foreign key(article_id) references article(id) 
        on delete cascade
);

CREATE TABLE rating (
    id         int not null generated by default as identity,
    date_rated        timestamp not null,
    article_id        int not null,
    data            xml not null,
    primary key(id),
    foreign key(article_id) references article(id) 
        on delete cascade
);

The code creates a new database named kbase with four database tables: category, article, comment, and rating. Each table has an id column and a data column. Some of the tables also have Meta columns such as date_created and foreign keys. The id column in each table is used as the unique identifier for the data in that table. The data column has a type of XML data and holds the main data in XML format. You will see later how this XML data is used in each table.

After you execute the database.sql code, you should receive a successful response from the DB2 server, as shown in Figure 2.

Figure 2. Result of executing database.sql
DB2 Command Editor with code from Listing 1 in the editor area and messages indicating successful execution in the results area.

With the database created, you are now ready to move on to creating the Knowledge Base application itself. In the next section, you will create a PHP class for connecting to the IBM DB2 database.


The database connection PHP class

The DB2 extension for PHP includes a series of functions that allow you to connect to and interact with the data in an IBM DB2 database. In this section you create a PHP class that wraps these functions. This allows you to use less code when interacting with the database. Other PHP classes can use this database class. You can call its object methods to initiate a database connection, escape potentially dangerous strings, execute database queries, and return the results of those queries.

Define the DB class

In this section, you create a PHP class named DB that handles the connection to the DB2 database. It is always a good idea to keep the database connection code separate from the rest of the application. That way, if you need to change the connection hostname, username, password, or even the database driver, you only have to do so in a single location.

Create a file named db.php, copy the code from Listing 2 into it, and save the file in your project's classes subdirectory.

Listing 2. db.php
<?php
class DB {
    private $conn;

    function __construct() {
        $database = "kbase";
        $hostname = "localhost";
        $port = 50000;
        $user = "username";
        $password = "password";

        $db_connect_string = "DRIVER={IBM DB2 ODBC DRIVER};"
            . "DATABASE=$database;"
            . "HOSTNAME=$hostname;PORT=$port;PROTOCOL=TCPIP;"
            . "UID=$user;PWD=$password;";

        $this->conn = db2_connect($db_connect_string, '', '');

        if(!$this->conn) {
            die(db2_conn_errormsg($this->conn));
        }
    }

    function safe_no_html($string, $include_quotes=true) {
        htmlentities($string, ENT_QUOTES, 'utf-8');

        if($include_quotes) 
return "'".db2_escape_string($string)."'";
        else return db2_escape_string($string);
    }

    function query($sql) {
        $result = db2_exec($this->conn, $sql);

        if(!$result) {
            die(db2_stmt_errormsg());
        } else {
            return $result;
        }
    }

    function get_row($result) {
        return db2_fetch_array($result);
    }
}
?>

This class comprises a single private member variable, $conn, which is used to store the database connection. The class constructor (the __construct function) contains the database settings. Within the class constructor, replace the values for the $user and $password variables with the correct values for your DB2 installation. These settings are used to form a database connection string, which is in turn used to connect to the database using the db2_connect function.

The DB class also includes three functions that you can use to work with the data in the database:

  • safe_no_html— accepts a string and returns a value that is safe to insert into the database. It uses the db2_escape_string function to escape any characters that may trip up the database, and it sanitizes any potentially harmful HTML or JavaScript code using the htmlentities function. The optional second argument, $include_quotes, can be set to false if you do not require the return value to include single quotes surrounding the result.
  • query— takes a single argument, $sql, which should include an SQL statement that is to be executed on the database. It uses the db2_exec function to run the statement on DB2, and if successful returns a $result resource. If an error occurs, the application stops processing, and the DB2 error message is output to the user.

    Note: Outputting database messages to the user is very convenient when developing an application but should never be used in a production environment because it can reveal important details to potential attackers. Also, when you are working in a production environment and executing statements via PHP, you should use prepared statements as opposed to straight SQL strings.

  • get_row— takes a $result resource as an argument and returns the next result row using the db2_fetch_array function.

In the next section, you will create two application classes that use the DB class to communicate with the DB2 server.


Application classes

The Knowledge Base application has two primary components: categories and articles. Each article will belong to one, and only one, category. Also, each article may have zero to many comments and zero to many ratings associated with it. In this section, you create two PHP classes (Category and Article) that incorporate all of the functions that can be performed on these components.

Create the Category class

The first class you create is the Category class. This class contains the instance variables and methods for creating Category objects. Each category has an $id variable, which is stored in an int column in the database, and a name variable, which is stored within the XML column named data. For each row on the category table in the database, there will be an XML document stored in the data column with the format shown in Listing 3.

Listing 3. XML for row on the category table
<category>
    <name>Category Name</name>
</category>

Note: You may be wondering why the outer <category> tags are needed here. They actually aren't required in this instance, but if you wanted to add more data fields in the future, you would need to wrap it in <category> tags because DB2 pureXML requires a single outer node in its XML columns.

The Category class has several functions. It has getter and setter methods for working with defined properties in the class, a save function for saving new and existing categories to the database, a delete function for deleting categories, and more functions for retrieving information about one or more categories.

Listing 4 contains an excerpt of the category.php file, which defines the Category class. For the full class definition, download the source code from the Downloads section. Save your category.php file in your project's classes subdirectory.

Listing 4. Excerpt from category.php
...
    function save() {
        $sql = "";
        if($this->id) 
            $sql = "UPDATE category SET data = (xmlparse(document
 '<category><name>".$this->db->safe_no_html($this->name, 
false)."</name></category>')) WHERE id = $this->id";
else $sql = "INSERT INTO category(data) VALUES((xmlparse(document 
'<category><name>".$this->db->safe_no_html($this->name, 
false)."</name></category>')))";

        $result = $this->db->query($sql);
        if($result) return true;
        else return false;
    }

    function delete() {
        $sql = "DELETE FROM category WHERE id = $this->id";
        $result = $this->db->query($sql);
        if($result) return true;
        else return false;
    }

    function getAllCategories() {
        $sql = 'SELECT c.id, x.name FROM category c, '
            .' XMLTABLE(\'$d/category\' PASSING c.data AS "d" '
            .' COLUMNS name VARCHAR(200) PATH \'name\') AS x '
            .' ORDER BY x.name';
        $result = $this->db->query($sql);
        $rows = array();
        if($result) {
            while($row = $this->db->get_row($result)) {
                $rows[] = array($row[0], $row[1]);
            }
        }
        return $rows;
    }
...

The class has three variables. The $db variable is used to instantiate a connection to the database. The database connection is used by the class each time it needs to retrieve, save, or delete data. The $db variable is called as a new DB object when the Category class is constructed. The methods of the DB class then become available to the Category class via the syntax:

$this->db->method_name()

You use this syntax in the functions that work with the database. For example:

$this->db->query($sql)

The save function is quite clever in that it handles both the creating of new records and the updating of existing ones. It checks if the object has a value set for the $id property. If so, it performs an UPDATE statement, but if not, it uses an INSERT statement to create a new record instead.

SQL/XML explained

If you are familiar with SQL and developing applications powered by relational databases, you may have noticed the unusual functions used in the SQL statements in the Category class. These functions are special extensions to the SQL standard that are made available by IBM DB2. This type of extended SQL is referred to as SQL/XML.

The first use of SQL/XML is evident in the save function. The function uses the XMLPARSE function to parse the text passed to it as XML. This works well in this instance because the XML data you are working with is very basic. However, you will see examples of more advanced XML parsing requirements when you create the Article class.

SQL/XML is also used in getAllCategories, load, and getCategoryNameById via the XMLTABLE function. Let's investigate this in more detail by looking at the SELECT statement in the getAllCategories function shown in Listing 5. All of the PHP string formatting has been removed from the listing for the sake of readability.

Listing 5. SELECT statement in the getAllCategories function
SELECT c.id, x.name
FROM category c, XMLTABLE('$d/category' PASSING c.data AS "d"
    COLUMNS name VARCHAR(200) PATH 'name') AS x
ORDER BY x.name

All of the data in this SELECT statement is retrieved from the table category. This table features a column named data that has a type of XML. Listing 6 takes the <name> XML tag from this column and maps it as a relational column name. The result is placed into a hypothetical table named x, which makes it accessible in the rest of the query.

Listing 6. Mapping the <name> tag as a relational column name
XMLTABLE('$d/category' PASSING c.data AS "d"
    COLUMNS name VARCHAR(200) PATH 'name') AS x

This allows you to access the contents of the <name> tag just like you would a typical relational database column.

Create the Article class

The other application class you need to create is the Article class. Listing 7 contains an excerpt of the article.php file, which defines the Article class. For the full class definition, download the source code from the Downloads section. Save your article.php file in your project's classes subdirectory.

Listing 7. Excerpt from article.php
...
    function save() {
        $sql = "";

        if($this->id) {
            $sql = "UPDATE article SET data = (XMLDOCUMENT(XMLELEMENT(NAME 
\"article\", XMLCONCAT("
                . " XMLELEMENT(NAME \"title\", ".$this->db->safe_no_html
($this->title)."), "
                . " XMLELEMENT(NAME \"content\", ".$this->db->safe_no_html
($this->content)."))))), "
                . " category_id = $this->category_id, date_modified = CURRENT 
TIMESTAMP WHERE id = $this->id";
        } else {
            $sql = "INSERT INTO article(date_created, date_modified, view_count, 
category_id, data) VALUES ( "
                . " CURRENT TIMESTAMP, CURRENT TIMESTAMP, 0, $this->category_id, 
(XMLDOCUMENT(XMLELEMENT(NAME \"article\", XMLCONCAT("
                . " XMLELEMENT(NAME \"title\", ".$this->db->safe_no_html
($this->title)."), "
                . " XMLELEMENT(NAME \"content\", ".$this->db->safe_no_html
($this->content)."))))))";
        }
        $result = $this->db->query($sql);
        if($result) return true;
        else return false;
    }
...

    function getArticlesBySearchTerm($search_term) {
        $search_term = strtolower(trim($search_term));
        $sql = 'SELECT a.id, x.title, a.date_created, a.view_count FROM article a, '
            . ' XMLTABLE(\'$d/article\' passing a.data as "d" '
            . ' COLUMNS title VARCHAR(200) PATH \'title\', '
            . ' content VARCHAR(4000) PATH \'content\') as x '
            . ' WHERE LOWER(x.title) LIKE \'%'.$this->db->safe_no_html
($search_term, false).'%\' '
            . ' OR LOWER(x.content) LIKE \'%'.$this->db->safe_no_html
($search_term, false).'%\' '
            . ' ORDER BY x.title';
        $result = $this->db->query($sql);
        $rows = array();
        while($row = $this->db->get_row($result)) {
            $rows[] = array($row[0], $row[1], $row[2], $row[3]);
        }
        return $rows;
    }
...
    function addComment($name, $comments, $ip_address) {
        $sql = 'INSERT INTO comment(date_left, article_id, approved, data) VALUES 
(CURRENT TIMESTAMP, '
            . $this->getId().', 0, (XMLDOCUMENT(XMLELEMENT(NAME "comment", 
XMLCONCAT('
            . ' XMLELEMENT(NAME "name", \''.$this->db->safe_no_html
($name, false).'\'), '
            . ' XMLELEMENT(NAME "ip_address", \''.$ip_address.'\'), '
            . ' XMLELEMENT(NAME "content", \''.$this->db->safe_no_html
($comments,false).'\'))))))';
        $result = $this->db->query($sql);
        if($result) return true;
        else return false;
    }
...

Notice that this class is quite a bit longer than the Category class. It contains getters and setters for the Article class; functions for retrieving, saving, and deleting articles; functions for retrieving and creating comments and ratings; and functions for moderating comments on articles. Many of the functions are similar to each other, so let's investigate some of the more important concepts introduced in this class.

The first function of interest is the save function. Listing 8 shows the INSERT and UPDATE statements in this function without the PHP string formatting.

Listing 8. INSERT and UPDATE statements from the save function
INSERT INTO article(date_created, date_modified, view_count, 
    category_id, data)
VALUES (CURRENT TIMESTAMP, CURRENT TIMESTAMP, 0, $this->category_id,
    (XMLDOCUMENT(XMLELEMENT(NAME "article", XMLCONCAT(
        XMLELEMENT(NAME "title", $this->title),
        XMLELEMENT(NAME "content", $this->content)
    ))))
)

UPDATE article
SET data = (XMLDOCUMENT(XMLELEMENT(NAME "article", XMLCONCAT(
    XMLELEMENT(NAME "title", $this->title),
    XMLELEMENT(NAME "content", $this->content)
)))), 
category_id = $this->category_id, 
date_modified = CURRENT TIMESTAMP
WHERE id = $this->id

Much of these two statements should look familiar. In the INSERT statement, all but the value being inserted into the data column is standard relational SQL. Similarly, only the data column is being updated with any unusual data in the UPDATE statement. In both cases, the value being inserted into the data column is generated by the code shown in Listing 9.

Listing 9. Generating the value being inserted into the data column
(XMLDOCUMENT(XMLELEMENT(NAME "article", XMLCONCAT(
    XMLELEMENT(NAME "title", $this->title),
    XMLELEMENT(NAME "content", $this->content)
))))

XML mapping explained

Let's take a closer look at what the XML mapping code in the Article class does. First, the XMLDOCUMENT function tells the database to create a new XML document. This function takes the XML document itself as an argument. The XMLELEMENT function defines an XML element that is to be included in the XML document. It first takes the element name as its argument and secondly the value that is to be included within the tags of the element. This is fairly straightforward for the elements with names, title, and content, but what about the first use of the XMLELEMENT function? This is a little different in that in this case we are defining the single outer node (named article), which should contain the inner title and content elements within it. In this case, the XMLCONCAT function constructs the contents of the article node with the title and content elements added as the value.

For example, if the value of $this->title is "Guide to DB2" and $this->content is "This is an in-depth guide to IBM DB2", Listing 9 would generate the XML shown in Listing 10 to be saved in the data column of the article table.

Listing 10. Example of XML output from Listing 9
<article>
    <title>Guide to DB2</title>
    <content>This is an in-depth guide to IBM DB2</content>
</article>

The next function of note is the getArticlesBySearchTerm function. This function illustrates how elements within an XML column that are mapped as relational columns can be used in the WHERE clause of an SQL statement to restrict the data returned by the query, just as if they were a regular relational column. The SELECT statement (with PHP string formatting stripped out) is shown in Listing 11.

Listing 11. SELECT statement
SELECT a.id, x.title, a.date_created, a.view_count 
FROM article a, XMLTABLE('$d/article' PASSING a.data as "d"
    COLUMNS title VARCHAR(200) PATH 'title',
        content CLOB(16M) PATH 'content') AS x
WHERE LOWER(x.title) LIKE '%$search_term%'
OR LOWER(x.content) LIKE '%$search_term%'
ORDER BY x.title

Notice that the title and content elements in the data XML column are mapped as relational columns under the hypothetical table x. These are then used in the WHERE clause to filter the search results to only those rows where the search term is contained in either the title or content element.

Other significant Article functions

Another interesting function of the Article class is getHighestRatedArticles. This function returns a maximum of five articles, ordered by the average rating in descending order (highest rated article at the top). The SELECT statement in this function actually uses two XMLTABLE functions to define two hypothetical tables. This enables it to source information from the title element in the article table's data XML column and the value element in the rating table's data XML column. It then groups the results based on the average rating value. The extracted and formatted version of the SQL statement is shown in Listing 12.

Listing 12. Extracted and formatted version of the SQL statement
SELECT a.id, ax.title, a.date_created, a.view_count, AVG(rx.value)
FROM article a, rating r, XMLTABLE('$d/article' PASSING a.data AS "d"
    COLUMNS title VARCHAR(200) PATH 'title') AS ax,
XMLTABLE('$d/rating' PASSING r.data as "d"
    COLUMNS value INT PATH 'value') AS rx
WHERE a.id = r.article_id
GROUP BY a.id, ax.title, a.date_created, a.view_count
ORDER BY AVG(rx.value) DESC
FETCH FIRST 5 ROWS ONLY

Notice that it is possible to map more than one XML document to a relational table in a single SELECT statement. This example also highlights the fact that the columns are treated the exact same way as regular columns — if you use a group function (AVG in this case), you must include all of the other columns in the GROUP BY clause, including any that have been mapped by the XMLTABLE function.

The heart of the Knowledge Base application is contained in the two classes you just created. All of the functions for retrieving, inserting, updating, and deleting data are stored in these two classes. Later in the tutorial, you will see how these are used by the application. In the next section, you create header and footer scripts, which are be used by each page in the application to avoid repetition of HTML code.


Common design components

Rather than repeating the same HTML code for elements that are common to every application page, you can put the common elements into files that each page can then include programmatically. In this section, you create the header and footer include files for the application's interface.

With the most difficult part of the application taken care of, you can now concentrate on piecing together the user interface for the Knowledge Base application. Figure 3 shows what the final user interface looks like.

Figure 3. The final product
Screenshot of the Knowledge Base application. The four parts of the user interface are described in the following text.

The application's interface is split into four sections. At the top is the header, which contains the application name and a search form. The center area is split into two columns. The left column contains the application's navigation links for available categories and administration pages. The right column is the main content area where the content of each individual page is displayed. At the bottom of the screen is the footer, which in this case simply contains a copyright notice.

Create the header and footer

In this section, you create two files: header.php and footer.php. The header.php file draws the top header and the navigation sections. The footer.php file draws the bottom footer and closes the HTML page. These files are very straightforward, so let's zip right through them.

Listing 13 contains an excerpt of the header.php file. The source code in the Downloads section contains the complete file. Save your header.php file in your project's includes subdirectory.

Listing 13. Excerpt from header.php
...
                <!-- Start Sidebar Section -->
                <div class="sidebar">
                    <div class="box">
                        <div class="box_header">
                            Categories
                        </div>
                        <div class="box_content">
                            <ul>
                                <?php
                                if(is_array($cat_list_items) && 
count($cat_list_items) > 0) {
                                    for($i = 0; $i < count($cat_list_items); $i++) {
                                        $cat_id = $cat_list_items[$i][0];
                                        $cat_name = $cat_list_items[$i][1];
                                        echo '<li><a
 href="category_view.php?id='.$cat_id.'">'.$cat_name.'</a>';
                                    }
                                } else {
                                    echo "<li>No categories found</li>";
                                }
                                ?>
                            </ul>
                        </div>
                    </div>
 ...

The only slightly complex feature of this file is that it retrieves the list of categories to display in the navigation area from the DB2 database using the getAllCategories function of the Category class. If this function returns a suitable result as an array, it shows each category as a link to the View Category page for that particular category. Also, notice that the <title> tag uses the $title variable, even though the variable is not actually declared in the header.php file itself. This allows you to define the page title in each application page by declaring the $title variable before you include the header.php file in each page.

Listing 14 shows the contents of the footer.php file, which you should also save in your project's includes subdirectory.

Listing 14. footer.php
                </div>
                <!-- End Content Section -->

                <div class="clear">&nbsp;</div>
            </div>
            <!-- End Main Section -->

            <!-- Start Footer -->
            <div class="footer">
                &copy; 2009 Knowledge Base. All rights reserved.
            </div>
            <!-- End Footer -->
        </div>
        <!-- End Page Container -->
    </body>
</html>

This file actually contains no PHP code at all. It simply closes off the page with a copyright notice and the required closing HTML elements. In the next section, you will create the administration interface for your application.


Creating the administration interface

With the underlying classes and common components taken care of, you can now move on and create the actual user interface for the application. Before you begin, let's take a look at the pages that comprise the application. By the end of this tutorial, you should have the following files in your project's root directory:

  • article_comment_process.php
  • article_edit.php
  • article_edit_process.php
  • article_manage.php
  • article_manage_process.php
  • article_rate_process.php
  • article_view.php
  • category_edit.php
  • category_edit_process.php
  • category_manage.php
  • category_manage_process.php
  • category_view.php
  • comment_moderate.php
  • comment_moderate_process.php
  • index.php
  • search_results.php

Most of these files are for the administration interface, which allows you to add, edit, and delete articles and categories, and moderate comments. In this section, you first create the administration interface, starting with the form to create or edit categories. When you have completed the development of the administration interface, you build the client pages, which allow users to view categories, view articles, search for articles, add comments, and submit ratings.

Note: It is outside the scope of this tutorial to provide details on the creation of style sheets, however, the source code in the Downloads section contains a style sheet file named main.css in the css subdirectory. Be sure to copy this file to your project's css subdirectory so that it can be used to control the look and formatting of the application's interface pages.

Create the category administration pages

The first administration page to create is the Create Category/Edit Category page. This page allows for both the creation of new categories and the modification of existing ones. It determines whether it is in Create or Edit mode based on whether an id parameter is passed to the page.

Create a file named category_edit.php, copy the code from Listing 15 into it, and save the file in your project's root directory.

Listing 15. category_edit.php
<?php
require_once("classes/category.php");

if(isset($_GET['id'])) {
    $id = $_GET['id'];
    $category = new Category;
    $category->load($id);
}

if(isset($_GET['msg_type']) && isset($_GET['msg'])) {
    if($_GET['msg_type'] == "1") $success_msg = $_GET['msg'];
    else if($_GET['msg_type'] == "2") $error_msg = $_GET['msg'];
}

if(isset($id)) $title = "Edit Category";
else $title = "Create New Category";

include("includes/header.php");
?>
<div class="box">
    <div class="box_header">
        <?php echo $title; ?>
    </div>
    <div class="box_content">
        <?php
        if(isset($success_msg)) echo '<div class="success_msg">'.
$success_msg.'</div>';
        else if(isset($error_msg)) echo '<div class="error_msg">'.
$error_msg.'</div>';
        ?>
        <div class="category_form">
            <form name="category" method="post" 
action="category_edit_process.php">
                <?php
                if(isset($id)) {
                    echo '<div class="category_form_field">';
                    echo '<label for="id">ID:</label>';
                    echo '<input type="text" name="id" id="id" value="'.$id.'" 
readonly="readonly" />';
                    echo '</div>';
                }
                ?>
                <div class="category_form_field">
                    <label for="name">Name:</label>
                    <input type="text" name="name" id="name"
                    <?php if(isset($category)) echo 'value="'.
$category->getName().'"'; ?>
                    />
                </div>
                <?php
                if(isset($id)) echo '<input type="submit" 
value="Edit Category" />';
                else echo '<input type="submit" value="Create Category" />';
                ?>
            </form>
        </div>

        <div class="admin_link">
            <a href="category_manage.php">Manage Existing Categories</a>
        </div>
    </div>
</div>
<?php
include("includes/footer.php");
?>

This page first checks to see whether the GET parameter id has been passed to it. If so, it sets the title of the page to Edit Category and displays the category's ID and name values (see Figure 4). If the parameter is not passed, it sets the title of the page to Create New Category and allows the user to enter a name and create a new category (Figure 5).

Figure 4. Edit Category
Edit Category interface of category_edit.php: Category ID and Name fields with an Edit Category button and Manage Categories link.
Figure 5. Create New Category
Create New Category interface of category_edit.php: Name field with Create Category button and Manage Categories link.

The actual processing of the new or existing category is not handled in the category_edit.php file itself. A common mistake that Web application developers often make is placing manipulation code in the same file as the form for submitting it. Doing this can cause the form to be resubmitted multiple times if the user refreshes the page or presses the back button. To prevent this, you should handle the processing of the database transactions in a separate file and then send the user back to the form or a different page.

For this application, the form is submitted to a file named category_edit_process.php. Create a file named category_edit_process.php, copy the code from Listing 16 into it, and save the file in your project's root directory.

Listing 16. category_edit_process.php
<?php
require_once("classes/category.php");

$query_string = "";

$category = new Category;
if(isset($_POST['id'])) {
    $category->setId($_POST['id']);
    $query_string = '?id='.$_POST['id'];
}

if(strlen($_POST['name']) > 0) {
    $category->setName($_POST['name']);
    $success = $category->save();
    if($success) {
        $msg = "Category saved successfully.";
        $msg_type = 1;
    } else {
        $msg = "Database error occurred.";
        $msg_type = 2;
    }
} else {
    $msg = "Name is a mandatory field.";
    $msg_type = 2;
}

if(strlen($query_string) > 0) {
    $query_string .= "&msg=$msg&msg_type=$msg_type";
} else {
    $query_string = "?msg=$msg&msg_type=$msg_type";
}

header("Location: category_edit.php".$query_string);
?>

The code in this file accepts parameters that match up to the form controls in the category_edit.php page. If the action being performed is an edit, it sets the ID of the category in the object and saves. The Category class will recognize that it is an existing category and update it accordingly. Similarly, if it is a new category, the class will recognize that there is no ID and will create a new record. The category_edit_process.php script generates a success or error message based on whether the database transaction was successful and then returns the user to the previous page. Figure 6 shows an example of the success message. Note the green box with the success message above the ID field.

Figure 6. Success message after Category Edit
Edit Category interface of category_edit.php with message 'Category saved successfully'.

The page you just created allows users to edit categories, but it also requires that the category ID be passed to the page as a parameter. No decent application would expect a user to know how to pass such parameters or to know a category's ID. So, let's create a Manage Categories page that lists the existing categories in the database and provides the user with links to View, Edit, or Delete a particular category. Create a file named category_manage.php, copy the code from Listing 17 into it, and save the file in your project's root directory.

Listing 17. category_manage.php
<?php
require_once("classes/category.php");

if(isset($_GET['msg_type']) && isset($_GET['msg'])) {
    if($_GET['msg_type'] == "1") $success_msg = $_GET['msg'];
    else if($_GET['msg_type'] == "2") $error_msg = $_GET['msg'];
}

$title = "Manage Categories";

$category = new Category;
$categories = $category->getAllCategories();

include("includes/header.php");
?>
<div class="box">
    <div class="box_header">
        <?php echo $title; ?>
    </div>
    <div class="box_content">
        <?php
        if(isset($success_msg)) echo '<div class="success_msg">'
.$success_msg.'</div>';
        else if(isset($error_msg)) echo '<div class="error_msg">'
.$error_msg.'</div>';
        ?>
        <div class="category_manage">
            <table cellpadding="6" cellspacing="0" border="0">
                
<thead><tr><th>ID:</th><th>Name:</th><th>
Actions:</th></tr></thead>
                <tbody>
                    <?php
                    if(is_array($categories) && count($categories) > 0) {
                        for($i = 0; $i < count($categories); $i++) {
                            $cat_id = $categories[$i][0];
                            $cat_name = $categories[$i][1];
                            if(($i+1) % 2 == 0) echo '<tr>';
                            else echo '<tr class="odd">';
                            echo '<td>'.$cat_id.'</td>';
                            echo '<td>'.$cat_name.'</td>';
                            echo '<td>';
                            echo '<a href="category_view.php?id='.
$cat_id.'">View</a> | ';
                            echo '<a href="category_edit.php?id='.
$cat_id.'">Edit</a> | ';
                            echo '<a href="category_manage_process.php?id='.
$cat_id.'">Delete</a>';
                            echo '</td></tr>';
                        }
                    } else {
                        echo '<tr><td colspan="3">No categories found.
</td></tr>';
                    }
                    ?>
                </tbody>
            </table>
        </div>
        <div class="admin_link">
            <a href="category_edit.php">Create New Category</a>
        </div>
    </div>
</div>
<?php
include("includes/footer.php");
?>

The main function of this page is to make a call to the getAllCategories function in the Category class and output the ID and name of each category into a table with links to view, edit, and delete. The getAllCategories function returns an array. Each item in the array is itself an array with two values: the category ID and category name. The page checks that the result is in fact an array and that it is not empty. If these check out, it loops through the array and outputs a table row for each category. Figure 7 shows an example of what the output of the page looks like.

Figure 7. Manage Categories
Sample Manage Categories page from category_manage.php. 12 categories listed with ID, Name and links for View, Edit, and Delete

Although this page might look good, it's not exactly 100% functional at this point. You will implement the View functionality later, but now let's give the application the ability to delete categories. Like the Create/Edit page, the Manage page also uses an intermediate PHP script to process deletions. Create a file named category_manage_process.php, copy the code from Listing 18 into it, and save the file in your project's root directory.

Listing 18. category_manage_process.php
<?php
require_once("classes/category.php");

$query_string = "";

$category = new Category;
if(isset($_GET['id'])) {
    $category->setId($_GET['id']);
}

$success = $category->delete();
if($success) {
    $msg = "Category deleted successfully.";
    $msg_type = 1;
} else {
    $msg = "Database error occurred.";
    $msg_type = 2;
}

$query_string = "?msg=$msg&msg_type=$msg_type";

header("Location: category_manage.php".$query_string);
?>

This code in this file works much the same as the category_edit_process.php file, except it tells the database to delete an existing category rather than to create a new one or to update one. When users delete a category, they see a success message on the Manage Categories page, similar to the message shown on the Edit Category page in Figure 6.

Create the article administration pages

Next, you need to allow the adding, editing, and deleting of articles. This works pretty much in the same way as category management. The only real added complexity is that the article contains a foreign key of category ID that maps back to the category table. As a result, the Create/Edit Article page needs to allow the user to select a Category from those stored in the database and store that category's ID in the category_id column.

Listing 19 contains an excerpt of the article_edit.php file, which contains the code for the page that allows users to create and edit Knowledge Base articles. The source code in the Downloads section contains the complete file. Save your article_edit.php file in your project's root directory.

Listing 19. Excerpt from article_edit.php
...
if(isset($_GET['id'])) {
    $id = $_GET['id'];
    $article = new Article;
    $article->load($id);
}
...
        <div class="article_form">
            <form name="article" method="post" action="article_edit_process.php">
                <?php
                if(isset($id)) {
                    echo '<div class="article_form_field">';
                    echo '<label for="id">ID:</label>';
                    echo '<input type="text" name="id" id="id" value="'.$id.'" 
readonly="readonly" />';
                    echo '</div>';
                }
                ?>
                <div class="article_form_field">
                    <label for="title">Title:</label>
                    <input type="text" name="title" id="title"
                    <?php if(isset($article)) echo 'value="'.$article->getTitle()
.'"'; ?>
                    />
                </div>
                <div class="article_form_field">
                    <label for="category">Category:</label>
                    <select name="category" id="category">

                    </select>
				</div>
...
                <div class="article_form_field">
                    <label for="content">Article Content:</label>
                    <textarea name="content" id="content"><?php 
if(isset($article))echo $article->getContent(); ?></textarea>
                </div>
                <?php
                if(isset($id)) echo '<input type="submit" 
value="Edit Article" />';
                else echo '<input type="submit" value="Create Article" />';
                ?>
            </form>
...

The above listing shows the code that detects whether the form should be in create or edit mode. It reads this from the GET variable id. The article form then uses the result of this detection to determine whether or not it should print out existing values in the form fields.

When creating a new article, the page looks like the screenshot in Figure 8.

Figure 8. Create New Article
Create New Article interface of article_edit.php: Title, Category, Content fields ; Create Article button; Manage Articles link

When editing an existing article, the page has more fields that are displayed for information purposes (read only), as shown in Figure 9.

Figure 9. Edit Article
Edit Article interface of article_edit.php: Additional read only fields for article detail ; Edit Article button; Manage Articles link.

Like the Create/Edit Category page, this page uses an intermediate script to process the actual insert or update transaction before returning the user to the Create/Edit Article page. Create a file named article_edit_process.php, copy the code from Listing 20 into it, and save the file in your project's root directory.

Listing 20. article_edit_process.php
<?php
require_once("classes/article.php");

$query_string = "";

$article = new Article;
if(isset($_POST['id'])) {
    $article->setId($_POST['id']);
    $query_string = '?id='.$_POST['id'];
}

if(!(strlen($_POST['title']) > 0)) {
    $msg = "Title is a mandatory field.";
    $msg_type = 2;
} else if(!(strlen($_POST['category']) > 0) || ($_POST['category'] == "0")) {
    $msg = "Category is a mandatory field.";
    $msg_type = 2;
} else if(!(strlen($_POST['content']) > 0)) {
    $msg = "Article Content is a mandatory field.";
    $msg_type = 2;
} else {
    $article->setTitle($_POST['title']);
    $article->setCategoryId($_POST['category']);
    $article->setContent($_POST['content']);
    $success = $article->save();
    if($success) {
        $msg = "Article saved successfully.";
        $msg_type = 1;
    } else {
        $msg = "Database error occurred.";
        $msg_type = 2;
    }
}

if(strlen($query_string) > 0) {
    $query_string .= "&msg=$msg&msg_type=$msg_type";
} else {
    $query_string = "?msg=$msg&msg_type=$msg_type";
}

header("Location: article_edit.php".$query_string);
?>

In order for users to be able to edit and delete articles, you need to provide a list of existing articles from which a user can choose to edit or delete. Listing 21 contains an excerpt of the article_manage.php file, which contains the code for the Manage Articles page. The source code in the Downloads section contains the complete file. Save your article_manage.php file in your project's root directory.

Listing 21. Excerpt from article_manage.php
...
$article = new Article;
$articles = $article->getAllArticles();
...
            <table cellpadding="6" cellspacing="0" border="0">
                
<thead><tr><th>ID:</th><th>Title:
</th><th>Category:</th>
                    <th>Views:</th><th>Actions:</th>
</tr></thead>
                <tbody>
                    <?php
                    if(is_array($articles) && count($articles) > 0) {
                        for($i = 0; $i < count($articles); $i++) {
                            $art_id = $articles[$i][0];
                            $art_title = $articles[$i][1];
                            $art_cat = $articles[$i][4];
                            $art_vc = $articles[$i][5];
                            if(($i+1) % 2 == 0) echo '<tr>';
                            else echo '<tr class="odd">';
                            echo '<td>'.$art_id.'</td>';
                            echo '<td>'.$art_title.'</td>';
                            echo '<td>'.$art_category->getCategoryNameById
($art_cat).'</td>';
                            echo '<td>'.$art_vc.'</td>';
                            echo '<td>';
                            echo '<a href="article_view.php?id='.$art_id.
'">View</a> | ';
                            echo '<a href="article_edit.php?id='.$art_id.
'">Edit</a> | ';
                            echo '<a href="article_manage_process.php?id='.$art_id.
'">Delete</a>';
                            echo '</td></tr>';
                        }
                    } else {
                        echo '<tr><td colspan="3">No articles 
found.</td></tr>';
                    }
                    ?>
                </tbody>
            </table>
...

This page is similar to the corresponding page for managing categories. It is shown in Figure 10.

Figure 10. Manage Articles
Manage Articles page from articles_manage.php. 4 articles listed by ID, Title, Category, Views; Action links for View, Edit, Delete

In its current form, this page only allows you to edit articles. The viewing of articles is covered later when you implement the client side of the application, but now let's add the delete functionality. Create a file named article_manage_process.php, copy the code from Listing 22 into it, and save the file in your project's root directory.

Listing 22. article_manage_process.php
<?php
require_once("classes/article.php");

$query_string = "";

$article = new Article;
if(isset($_GET['id'])) {
    $article->setId($_GET['id']);
}

$success = $article->delete();
if($success) {
    $msg = "Article deleted successfully.";
    $msg_type = 1;
} else {
    $msg = "Database error occurred.";
    $msg_type = 2;
}

$query_string = "?msg=$msg&msg_type=$msg_type";

header("Location: article_manage.php".$query_string);
?>

Create the comment moderation page

The administration section of the Knowledge Base application is almost complete. The last feature you need to implement is the Moderate Comments page, which allows the administrator to approve or reject any comments submitted for an article. This page is similar in style to the Manage Articles page. It displays any comments with the value of the approved column set to 0 (zero). Next to each comment it displays two links: one for Approve and one for Reject. Clicking Approve sets the value of the approved column to 1, which means the comment is displayed on the View Article page along with any other approved comments. Clicking Reject sets the value of the approved column to 2, which means the comment will not be displayed.

Listing 23 contains an excerpt of the comment_moderate.php file, which contains the code for the Moderate Comments page. The source code in the Downloads section contains the complete file. Save your comment_moderate.php file in your project's root directory.

Listing 23. Excerpt from comment_moderate.php
...
$article = new Article;
$comments = $article->getPendingComments();
...
            <table cellpadding="6" cellspacing="0" border="0">
                <thead><tr><th>Comment:</th><th>Actions:
</th></tr></thead>
                <tbody>
                    <?php
                    if(is_array($comments) && count($comments) > 0) {
                        for($i = 0; $i < count($comments); $i++) {
                            $com_id = $comments[$i][0];
                            $com_name = $comments[$i][1];
                            $com_date = $comments[$i][2];
                            $com_text = $comments[$i][4];
                            $com_title = $article->getArticleTitleById
($comments[$i][3]);
                            if(($i+1) % 2 == 0) echo '<tr>';
                            else echo '<tr class="odd">';
                            echo '<td>'.$com_text.'<br /><em>Left 
By: '.$com_name
                                .' on '.$com_date.'<br />Article: '.$com_title
.'</td>';
                            echo '<td>';
                            echo '<a
 href="comment_moderate_process.php?id='.$com_id.'&action=A">Approve</a> | ';
                            echo '<a
 href="comment_moderate_process.php?id='.$com_id.'&action=R">Reject</a>';
                            echo '</td></tr>';
                        }
                    } else {
                        echo '<tr><td colspan="3">No comments pending 
moderation.</td></tr>';
                    }
                    ?>
                </tbody>
            </table>
...

Figure 11 shows an example of the Moderate Comments page. Each pending comment is displayed with the comment text, the name of the person that left it, when it was left, and the title of the article it is related to.

Figure 11. Moderate Comments
Moderate Comments page from comment_moderate.php. 3 comments listed with details; Action links for Approve, Reject.

Now let's enable the Approve and Reject links on the Moderate Comments page. Create a file named comment_moderate_process.php, copy the code from Listing 24 into it, and save the file in your project's root directory.

Listing 24. comment_moderate_process.php
<?php
require_once("classes/article.php");

$query_string = "";

$article = new Article;
if(isset($_GET['id']) && isset($_GET['action'])) {
    if($_GET['action'] == "A") {
        $success = $article->approveComment($_GET['id']);
        $msg = "Comment approved successfully.";
    } else if($_GET['action'] == "R") {
        $success = $article->rejectComment($_GET['id']);
        $msg = "Comment rejected successfully.";
    }
}

if($success) {
    $msg_type = 1;
} else {
    $msg = "Database error occurred.";
    $msg_type = 2;
}

$query_string = "?msg=$msg&msg_type=$msg_type";

header("Location: comment_moderate.php".$query_string);
?>

This script looks for two parameters: an id and an action indicator, which should be either "A" (Approve) or "R" (Reject). Based on this indicator, it marks the selected comment as approved or rejected and redirects the user back to the Moderate Comments page.

That's the last of the administration pages — next it's time to create those all-important client-side pages that will be seen by the end users of the application.


Creating the client interface

In this section you create the main interface that users see when they use the application.

The client interface consists of these four pages:

  • Home — displays a list of the newest articles, the highest rated articles, and the most viewed articles.
  • Search Results — displays a list of articles found where the title or content matches a search term that a user entered in the search form that appears at the top of every page in the application.
  • View Category — displays all of the articles in a particular category.
  • View Article — clicking on the title of an article on any of the three pages listed above, takes the user to the View Article page. This page displays the Meta information about the article as well as the main content of the article itself. It displays any approved comments left on the article and its current average rating. It also includes a rating form and a comment form to allow users to rate the article or leave a comment (pending approval by an administrator).

Create the home page

The Home page is what users see when they first launch the application. It makes sense that you would want this page to display the newest articles, highest rated articles, and most popular articles.

Listing 25 contains an excerpt of the index.php file, which contains the code for the application home page. The source code in the Downloads section contains the complete file. Save your index.php file in your project's root directory.

Listing 25. Excerpt from index.php
...
$article = new Article;
$newest_articles = $article->getTopArticles("date_created DESC");
$highest_rated_articles = $article->getHighestRatedArticles();
$most_viewed_articles = $article->getTopArticles("view_count DESC");
...
    <div class="box_content">
        <?php
        if(is_array($newest_articles) && count($newest_articles) > 0) {
            for($i = 0; $i < count($newest_articles); $i++) {
                $art_id = $newest_articles[$i][0];
                $art_title = $newest_articles[$i][1];
                $art_dc = $newest_articles[$i][2];
                $art_vc = $newest_articles[$i][3];
                $comments = $article->countCommentsByArticleId($art_id);
                $rating = $article->getRatingByArticleId($art_id);
                $rating_count = $rating['rating_count'];
                $rating_average = number_format($rating['rating_average'], 2, '.', '');
                if($rating_average == 0) $rating_average = "Not yet rated";

                if(($i+1) % 2 == 0) echo '<div class="box_item">';
                else echo '<div class="box_item odd">';
                echo '<div class="box_item_title"><a
 href="article_view.php?id='.$art_id.'">'.$art_title.'</a></div>';
                echo '<div class="box_item_date">'.$art_dc.'</div>';
                echo '<div class="box_item_category">'.$art_vc.' 
view(s)</div>';
                echo '<div class="box_item_comments">'.$comments.' 
Comment(s)</div>';
                echo '<div class="box_item_rating">Rating: '.$rating_average.' 
('.$rating_count.' votes)</div>';
                echo '<div class="clear">&nbsp;</div>';
                echo '</div>';
            }
        } else {
            echo '<div>No articles found.</div>';
        }
        ?>
    </div>
</div>
...

The above listing includes the code that displays the newest articles. This code is almost identical in concept to the code for the highest rated and most viewed articles.

The Home page uses two functions in the Article class: getTopArticles and getHighestRatedArticles. The getTopArticles function accepts a parameter that is plugged into the ORDER BY clause to determine how the results should be ordered. Each of these functions returns an array of articles. The script then loops through the returned array and displays a link to each article along with some more detail about that article. Figure 12 shows an example of the Home page.

Figure 12. Home page
Example of the Home page. Main content area shows Newest, Highest Rated, and Most Viewed articles.

Create the Search Results and View Category pages

The Search Results and View Category pages are similar. The Search Results page returns results based on a search term that a user entered in the search form that appears at the top of every page in the application. The View Category page displays all the articles in a particular category. The ID of the category is passed to the page either by the left-hand navigation category links or the Manage Categories administration page.

Create a file named search_results.php, copy the code from Listing 26 into it, and save the file in your project's root directory.

Listing 26. search_results.php
<?php
require_once("classes/article.php");

if(isset($_GET['query'])) {
    $search_term = $_GET['query'];
    
    $article = new Article;
    $search_articles = $article->getArticlesBySearchTerm($search_term);
    
    $title = "Search Results for "$search_term"";
}

include("includes/header.php");
?>
<div class="box">
    <div class="box_header">
        <?php echo $title; ?>
    </div>
    <div class="box_content">
        <?php
        if(is_array($search_articles) && count($search_articles) > 0) {
            for($i = 0; $i < count($search_articles); $i++) {
                $art_id = $search_articles[$i][0];
                $art_title = $search_articles[$i][1];
                $art_dc = $search_articles[$i][2];
                $art_vc = $search_articles[$i][3];
                $comments = $article->countCommentsByArticleId($art_id);
                $rating = $article->getRatingByArticleId($art_id);
                $rating_count = $rating['rating_count'];
                $rating_average = number_format($rating['rating_average'], 2, '.', '');
                if($rating_average == 0) $rating_average = "Not yet rated";
                
                if(($i+1) % 2 == 0) echo '<div class="box_item">';
                else echo '<div class="box_item odd">';
                echo '<div class="box_item_title"><a
 href="article_view.php?id='.$art_id.'">'.$art_title.'</a></div>';
                echo '<div class="box_item_date">'.$art_dc.'</div>';
                echo '<div class="box_item_category">'.$art_vc.' 
view(s)</div>';
                echo '<div class="box_item_comments">'.$comments.' 
Comment(s)</div>';
                echo '<div class="box_item_rating">Rating: '.$rating_average.' 
('.$rating_count.' votes)</div>';
                echo '<div class="clear">&nbsp;</div>';
                echo '</div>';
            }
        } else {
            echo '<div>No articles found that match the query
 <strong>'.$search_term.'</strong></div>';
        }
        ?>
    </div>    
</div>        
<?php
include("includes/footer.php");
?>

Figure 13 shows an example of the Search Results page.

Figure 13. Search Results
Example of the Search Results page. 2 articles are listed with links to article content, number of comments, date and rating.

Next, create the View Category page. Create a file named category_view.php, copy the code from Listing 27 into it, and save the file in your project's root directory.

Listing 27. category_view.php
<?php
require_once("classes/category.php");
require_once("classes/article.php");

if(isset($_GET['id'])) {
    $category_id = $_GET['id'];
    
    $category = new Category;
    $category->load($category_id);
    $category_name = $category->getName();
    
    $title = $category_name.' Articles';
    
    $article = new Article;
    $category_articles = $article->getArticlesByCategory($category_id);
}

include("includes/header.php");
?>
<div class="box">
    <div class="box_header">
        <?php echo $title; ?>
    </div>
    <div class="box_content">
        <?php
        if(is_array($category_articles) && count($category_articles) > 0) {
            for($i = 0; $i < count($category_articles); $i++) {
                $art_id = $category_articles[$i][0];
                $art_title = $category_articles[$i][1];
                $art_dc = $category_articles[$i][2];
                $art_vc = $category_articles[$i][3];
                $comments = $article->countCommentsByArticleId($art_id);
                $rating = $article->getRatingByArticleId($art_id);
                $rating_count = $rating['rating_count'];
                $rating_average = number_format($rating['rating_average'], 2, '.', '');
                if($rating_average == 0) $rating_average = "Not yet rated";
                
                if(($i+1) % 2 == 0) echo '<div class="box_item">';
                else echo '<div class="box_item odd">';
                echo '<div class="box_item_title"><a
 href="article_view.php?id='.$art_id.'">'.$art_title.'</a></div>';
                echo '<div class="box_item_date">'.$art_dc.'</div>';
                echo '<div class="box_item_category">'.$art_vc.' 
view(s)</div>';
                echo '<div class="box_item_comments">'.$comments.' 
Comment(s)</div>';
                echo '<div class="box_item_rating">Rating: '.$rating_average.' 
('.$rating_count.' votes)</div>';
                echo '<div class="clear">&nbsp;</div>';
                echo '</div>';
            }
        } else {
            echo '<div>No articles to display.</div>';
        }
        ?>
    </div>    
</div>        
<?php
include("includes/footer.php");
?>

The resulting interface for this page is similar to the Search Results page. Figure 14 shows an example.

Figure 14. View Category
View Category page lists articles for a single category. article title is link to content.

Create the View Article page

The final piece of the puzzle is the View Article page. This page is the most complex client interface page, but it is still rather simple. It uses the load function of the Article class to retrieve all the details of the selected article. It then uses the getRatingByArticleId, countCommentsByArticleId, and getCommentsByArticleId functions to get back the related rating and comments information. It also calls the incrementViewCount function, which takes the current view count for the article and increases it by one.

Listing 28 contains an excerpt of the article_view.php file, which contains the code for the View Article page. The source code in the Downloads section contains the complete file. Save your article_view.php file in your project's root directory.

Listing 28. Excerpt from article_view.php
...
    $article->incrementViewCount();

    $rating = $article->getRatingByArticleId($article_id);
...
    $comment_count = $article->countCommentsByArticleId($article_id);
    $comments = $article->getCommentsByArticleId($article_id);

...
    $category_name = $category->getCategoryNameById($article->getCategoryId());
}
...
        <div class="article_meta">
...
        </div>
        <div class="article_body">
            <?php echo $article->getContent(); ?>
        </div>
        <div class="article_rating">
            <div class="article_rating_rate">
                <form name="rating" method="post" action=
"article_rate_process.php">
...
                </form>
            </div>
            <div class="article_rating_votes">
                Rating: <?php echo $rating_average; ?>
                (based on <?php echo $rating_count; ?> votes)
            </div>
            <div class="clear">&nbsp;</div>
        </div>
    </div>
</div>
<div class="spacer">&nbsp;</div>
<div class="box">
    <div class="box_header">
        Comments
    </div>
    <div class="box_content">
        <div class="comment_form">
            <div class="comment_form_title">
                Leave a Comment
            </div>
            <form name="comment" method="post"
...
            </form>
        </div>
        <div class="comment_list">
            <div class="comment_list_title">
                <?php echo $comment_count; ?> Comment(s)
            </div>
            <?php if($comment_count > 0) { ?>
            <div class="comment_list_items">
                <?php
                if(is_array($comments) && count($comments) > 0) {
                    for($i = 0; $i < count($comments); $i++) {
                        $comment_name = $comments[$i][0];
                        $comment_date = $comments[$i][1];
                        $comment_content = $comments[$i][2];
                        if(($i+1) % 2 == 0) echo '<div class=
"comment_list_item">';
                        else echo '<div class="comment_list_item odd">';
                        echo '<div class="comment_list_item_author">'.
$comment_name
                            .' on '.$comment_date.' said:</div>';
                        echo '<div class="comment_list_item_content">'.
$comment_content.'</div>';
                        echo '</div>';
                    }
                }
                ?>
            </div>
            <?php } ?>
        </div>
    </div>
...

In addition to displaying all the information about the article and including the article content itself, this page also includes two forms: one for submitting a rating and one for submitting a comment. Each of these forms has a corresponding process script.

Processing article ratings

First, let's handle the article ratings. Create a file named article_rate_process.php, copy the code from Listing 29 into it, and save the file in your project's root directory.

Listing 29. article_rate_process.php
<?php
require_once("classes/article.php");

$query_string = "";

$article = new Article;
if(isset($_POST['article_id'])) {
    $article->setId($_POST['article_id']);
    $query_string = '?id='.$_POST['article_id'];
}

if((strlen($_POST['vote']) == 0) || $_POST['vote'] == "0") {
    $msg = "You must select a rating from 1 to 5.";
    $msg_type = 2;
} else if(!isset($_POST['article_id'])) {
    $msg = "You can only rate when viewing an article.";
    $msg_type = 2;
} else {
    $rating_value = $_POST['vote'];
    $ip_address = $_SERVER['REMOTE_ADDR'];
    $success = $article->addRating($rating_value, $ip_address);
    if($success) {
        $msg = "Rating added successfully.";
        $msg_type = 1;
    } else {
        $msg = "Database error occurred.";
        $msg_type = 2;
    }
}

if(strlen($query_string) > 0) {
    $query_string .= "&msg=$msg&msg_type=$msg_type";
} else {
    $query_string = "?msg=$msg&msg_type=$msg_type";
}

header("Location: article_view.php".$query_string);
?>

Processing new article comments

The final piece of code handles adding new comments to the pending moderation queue. Create a file named article_comment_process.php, copy the code from Listing 30 into it, and save the file in your project's root directory.

Listing 30. article_comment_process.php
<?php
require_once("classes/article.php");

$query_string = "";

$article = new Article;
if(isset($_POST['article_id'])) {
    $article->setId($_POST['article_id']);
    $query_string = '?id='.$_POST['article_id'];
}

if(strlen($_POST['name']) == 0) {
    $msg = "Name is a mandatory field.";
    $msg_type = 2;
} else if(strlen($_POST['comments']) == 0) {
    $msg = "Comments is a mandatory field.";
    $msg_type = 2;
} else {
    $name = $_POST['name'];
    $comments = $_POST['comments'];
    $ip_address = $_SERVER['REMOTE_ADDR'];
    $success = $article->addComment($name, $comments, $ip_address);
    if($success) {
        $msg = "Your comment has been submitted and is pending approval.";
        $msg_type = 1;
    } else {
        $msg = "Database error occurred.";
        $msg_type = 2;
    }
}

if(strlen($query_string) > 0) {
    $query_string .= "&msg=$msg&msg_type=$msg_type";
} else {
    $query_string = "?msg=$msg&msg_type=$msg_type";
}

header("Location: article_view.php".$query_string);
?>

Figure 13 shows an example of the View Article page.

Figure 15. View Article
View Article page has description and article details plus rating and comment forms.

That's it. You should now have an operational Knowledge Base application that could be used in a support department to store documentation on various issues that are repeatedly raised by customers. Also, the Knowledge Base is built in such a way that, without developing much additional code you could convert it into a blog, wiki, FAQ site, document management system, or other type of application.

The next section provides a summary of the tutorial along with some ideas for potential enhancements that you could develop to make the application better.


Summary

In this tutorial, you learned how to create a Support Knowledge Base application in PHP that is powered by an IBM DB2 pureXML database. First, you created database tables in DB2 that featured a hybrid of relational and XML columns. Next, you learned how to create a database class in PHP that would abstract the commands to be sent to the database. You then saw how to create the Knowledge Base application's primary classes, which take care of retrieving and manipulating the data for articles, categories, comments, and ratings. Next, you created the common interface components and then created the back end administration interface before finally developing the client interface.

With the information in this tutorial, you should have a solid foundation on which you can build a hybrid relational-XML powered Web application using DB2 and PHP.

Suggested improvements

Before deploying such an application, you would of course want to incorporate user authentication and authorization to protecting the administration pages from access by unauthorized users. You would also need to evaluate the code from a security point of view to ensure protection from SQL injection and Cross Site Scripting attacks.

In addition, with a little extra code you could fairly easily incorporate some of the following enhancements:

  • Allow for subcategories.
  • Include article tagging and a tag cloud.
  • Allow articles to be associated with more than one category.
  • Paginate search results and the listings on the View Category page.
  • Allow administrators to add, edit, and delete comments and ratings.
  • Only allow one rating per IP address per article.
  • Automatically approve comments by those users who have submitted comments before that were approved.
  • Syndicate content using RSS feeds.
  • Include social media and sharing links (Foe example "Share on Twitter" or "Share on Facebook")
  • Allow for rich content in articles. This could be incorporated using a WYSIWYG JavaScript editor framework such as TinyMCE or CKeditor.
  • Display related articles on the View Article page.
  • Enable attachment of documents and files to articles.

The sky's the limit!


Download

DescriptionNameSize
Knowledge Base source codekbase.zip18KB

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 for Linux, UNIX, and Windows.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  • Get the latest Windows binaries for PHP Point at http://windows.php.net/download/. This article used PHP version 5.2.11. Do not use PHP 5.3, as it does not support PECL extensions at this time.
  • Download the DB2 extension for PHP.
  • Innovate your next open source development project with IBM trial software, available for download or on DVD.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, XML
ArticleID=447983
ArticleTitle=Build a Support Knowledge Base using DB2 pureXML and PHP
publish-date=11192009