Editing List Items
With a little bit of creativity, it's possible to revise the form and form processor demonstrated in
Listing 3 and
Listing 4 to support editing existing records.
First, though, let's revise the index page slightly to add a "Change" link next to each record. To do that, when building the Pending Items table, add an extra
<td> tag containing the "Change" link. Note that the link builds a URL containing the item's ID. The relevant code is highlighted below:
...
<table>
<tr>
<td class="head"></td>
<td class="head">Due date</td>
<td class="head">Priority</td>
<td></td>
</tr>
<?php foreach ($pending as $p) {
$class = (mktime() > $p['due']) ? 'late' : 'ontime';
?>
<tr>
<td class="<?php echo $class; ?>">
<?php echo $p['name']; ?></td>
<td class="<?php echo $class; ?>">
<?php echo date('d M Y', $p['due']); ?></td>
<td class="<?php echo $class; ?>">
<?php echo $priorities[$p['priority']]; ?></td>
<td><a href="form.php?id=
<?php echo (int) $p['id']; ?>">Change</a></td>
</tr>
<?php } ?>
</table>
...
Next, revise
form.php so that when the script is called with a record ID, it automatically retrieves the corresponding record from the database and pre-populates the form.
Listing 5 contains the revised version of the form.
The first new thing
Listing 5 does is add various PDO calls to the beginning of the script. When called with a record ID as parameter, the script initializes a new PDO connection, and uses a
SELECT query to retrieve the corresponding record as an array of field-value pairs. This same snippet of code also turns the UNIX timestamp value for the
due field into separate numeric values for day, month, and year.
Within the form itself, the script then modifies the various input fields such that they retrieve the record set array generated by
query() and
fetch(), pre-populating the form with these values. Where the input fields are drop-downs, the script uses a conditional test to automatically pre-select the correct option. This version of the script also introduces a new hidden field in
Listing 6 named
id, to hold the ID of the record being retrieved.
One important implication of the changes to
form.php is that when a user submits this form, the
save.php script requires additional intelligence to determine whether the data being submitted belongs to an existing or a new record, so that it can formulate an appropriate UPDATE or INSERT query. Thus,
save.php needs to change as well.
Listing 6 contains the revised version of
form.php.
Not too many changes here—the script now uses the hidden
id field as a flag to determine whether an UPDATE or INSERT query is appropriate. PDO's
exec() method is used as before, to execute the query on the database.
Marking and Deleting List Items
At this point, you've seen how to perform
SELECT,
INSERT and
UPDATE queries on the small-footprint SQLite database from a PHP web application. The remaining tasks are to be able to delete records with the
DELETE command and mark records as complete with an UPDATE command.
Neither of these two functions is particularly difficult to implement. Marking an item as complete involves updating the item's
'status' field from 1 (pending) to 0 (complete). The
done.php script handles the update:
<?php
// check for record ID
if (isset($_GET['id']) && is_numeric($_GET['id'])) {
$id = (int) $_GET['id'];
try {
// connect to database
$dbh = new PDO('sqlite:../../db/todo.db3');
// update item record
// change status field
$now = mktime();
$dbh->exec("UPDATE items SET status = '0',
complete = '$now' WHERE id = '$id'");
// redirect to index page
header('Location: index.php');
exit();
} catch (PDOException $e) {
die('Error: ' . $e->getMessage());
}
} else {
die('Error in form submission');
}
?>
The preceding script uses an
UPDATE query to change the
status field of the selected record to 0, again via PDO's
exec() method. Notice that the script also saves the current date (the completion date) in UNIX timestamp format.
Removing an item consists merely of issuing a
DELETE query using the item's record ID, which the
delete.php script handles:
<?php
// check for record ID
if (isset($_GET['id']) && is_numeric($_GET['id'])) {
$id = (int) $_GET['id'];
try {
// connect to database
$dbh = new PDO('sqlite:../../db/todo.db3');
// delete item
$dbh->exec("DELETE FROM items WHERE id = '$id'");
// redirect to index page
header('Location: index.php');
exit();
} catch (PDOException $e) {
die('Error: ' . $e->getMessage());
}
} else {
die('Error in form submission');
}
?>
At this point, it's also necessary to update the application's index page, to add links to these new functions next to each record.
Listing 7 contains the revised index page.
The changes cause every pending item to display options to "Mark as Done" and "Remove," and every completed item to display a "Remove" option. The script has one further refinement—it checks the current date (for pending items) or the completed date (for completed items) against the item's due date, and marks "late" items in red. You can see all these features in
Figure 1.
Backing Up And Restoring The Database
At this point, the to-do list application is complete—you can begin using it to save to-do items, assign and edit priorities, mark items as done, and remove completed items. Before ending this article, though, it's worthwhile spending a few moments on an important aspect of the application's operational use: backing up the database.
Backing up a SQLite database is simple because the database is stored in a single disk file. By far the easiest backup mechanism is simply to copy the file onto backup media and store it in a safe place. However, there's also another way—using SQLite's built-in
.dump command to generate a file containing the CREATE TABLE and INSERT commands necessary to recreate the database from scratch:
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE items (
id integer not null primary key,
name text not null,
due integer not null,
complete integer null,
priority integer not null,
status integer not null
);
INSERT INTO "items" VALUES(1,
'Buy anniversary present',1184437800,NULL,4,1);
INSERT INTO "items" VALUES(2,
'Send invoices',1180549800,NULL,5,1);
INSERT INTO "items" VALUES(3,
'Finish homework',1180549800,NULL,1,0);
INSERT INTO "items" VALUES(4,
'Visit Jane',1180290600,NULL,2,1);
INSERT INTO "items" VALUES(5,
'Finalize hotel reservations',1181737000,NULL,5,1);
INSERT INTO "items" VALUES(6,
'Angie''s birthday',1182537000,NULL,4,1);
INSERT INTO "items" VALUES(7,
'Weed the lawn',1182237000,NULL,1,1);
COMMIT;
Sending the dump to the screen is not very useful, but you can direct the output to a file by preceding the
.dump command with an
.output command specifying a file name:
sqlite> .output todo.sql
sqlite> .dump
sqlite> .output stdout
Restoring a database from this backup file is simple; you call SQLite's
.read command with the file name. That automatically imports and executes the SQL in the named file, which recreates the database:
sqlite> .read todo.sql
As you can see, using a small-footprint database in a Web application has become a viable option—and there are small-footprint databases that support the platforms, languages, and feature sets you need. So the next time you need database functionality but don't want the cost or the space requirements of a full-size database, consider giving one of these small-footprint databases a try.
Previous Page
