RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Getting Small: Building Lightweight Web Applications with Small-Footprint Databases : Page 4

Not every application needs a full-featured enterprise-scale database. In such cases, you can reduce costs and save resources by using a small-footprint database.

Developing the Main To-Do List Page
Now you can begin writing some code to interact with the database created in the previous section. The application index page is a good place to begin. This displays a summary of pending and completed to-do items. For greater portability, this listing and all subsequent listings use PHP's PDO extension and PDO/SQLite driver, both of which are installed by default in PHP version 5.1 and later. Listing 1 contains the complete code for the index page.

Figure 3. Index.php Output: The index.php script retrieves pending and completed items and displays them as a neat table.
Most of the work in Listing 1 happens in the first few lines, which set up a connection to the SQLite database and retrieve records from it. First, the script initializes a new PDO object and passes it a DSN string that tells it where to find the SQLite database file. This DSN string varies depending on the database you're trying to connect to (see the sidebar "Portability" for more on this topic). Next, the script calls the PDO object's query() method to execute two SELECT queries; the first retrieves a list of pending items, and the second retrieves a list of completed items. In both cases the fetchAll() method formats the returned result set as a nested series of arrays, to simplify later processing.

After obtaining all the required data, the script generates a simple HTML page and uses a foreach() loop to process the arrays returned by fetchAll(). For each record, the script prints the description, due date, completion date (if available), and priority to the page in a neat table (see Figure 3). Note how the script uses PHP's date() method to format the UNIX timestamp returned in the due and complete fields into a human-readable date representation.

You may have also noticed that Listing 1 includes two external files. The first, include.php, merely contains constants used in more than one listing, such as English-language labels for priority levels and month names:

   // define labels for priority values
   $priorities = array (
       '1' => 'Very Low',
       '2' => 'Low',
       '3' => 'Medium',
       '4' => 'High',
       '5' => 'Very High',
   // define array of month names
   $months = array(
       '1' => 'Jan',
       '2' => 'Feb',
       '3' => 'Mar',
       '4' => 'Apr',
       '5' => 'May',
       '6' => 'Jun',
       '7' => 'Jul',
       '8' => 'Aug',
       '9' => 'Sep',
       '10' => 'Oct',
       '11' => 'Nov',
       '12' => 'Dec',
The second, main.css (see Listing 2), contains the CSS style sheet used to format the various HTML documents generated by the application.

Adding List Items
Listing 1 also contains a link to form.php (see Listing 3), which is the script that adds new to-do items to the database using an HTML form.

At first glance, Listing 3 is a fairly uncomplicated form that builds the screen shown in Figure 2 containing a text field for the item description and various drop-down selectors for the due date and priority. However, it will become more complicated shortly, so take a moment now to admire its simplicity.

When a user submits this form, it invokes the PHP script named save.php (see Listing 4), which does the hard work of validating the user's input and generating the SQL necessary to save it to the database.

Listing 1 showed you how to retrieve records from an SQLite database with a SELECT query. Listing 4 demonstrates adding a new record to the database. The top half of Listing 4 is devoted solely to validation: the script uses a regular expression to test the name field to ensure it doesn't contain illegal characters, and the checkdate() function to verify that the supplied due date is, in fact, a valid date.

Assuming the input is valid, the script proceeds to initialize a new PDO instance in the same manner as Listing 1, using PDO's quote() method to escape special characters in the form submission. The script then interpolates these values into a SQL INSERT query string. Finally, it uses PDO's exec() method to execute the query, saving the record to the database. The exec() method returns a number specifying how many rows were modified as a result of the query. After the query has completed, the script destroys the PDO instance, and redirects the client browser to the index page (Listing 1).

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date