By Kevin Waterson
Contents
Abstract
SQLite is an ultra lite database replacement that comes bundled with PHP. Because of its light weight it lacks many of the features found in more robust applications such as MySQL, PostgresQL and others. One of the features that is lacking is the ON DUPLICATE KEY UPDATE that is often used to automatically UPDATE a record, should a duplicate field be found. Here a demonstration is provided to duplicate this behaviour using PHP Exceptions.
The database
The database for this example is quite simple, and contains just four fields. Another feature lacking in SQLite is the ability to update timestamp fields. SQLite uses
<?php
/*** make it or break it ***/
error_reporting(E_ALL);
try
{
$sql = "CREATE TABLE animals (
animal_id INTEGER PRIMARY KEY,
animal_name TEXT NOT NULL,
animal_type TEXT UNIQUE NOT NULL,
last_updated TIMESTAMP NOT NULL
)";
/*** create the database file in /tmp ***/
$dbh = new PDO("sqlite:/tmp/animals.sqlite");
/*** set all errors to excptions ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** run the create table query ***/
$dbh->query($sql);
echo 'done';
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
Creating Records
With the Database in place, a few records can be added. Here the PDO extension is used to interface with SQLite. One of the benifits of using the PDO class, other than providing a standard interface for all databases, it has a built in exception class. It is this exception class that will be used to trigger an duplicate response.
<?php
/*** make it or break it ***/
error_reporting(E_ALL);
try
{
/*** create the database file in /tmp ***/
$dbh = new PDO("sqlite:/tmp/animals.sqlite");
/*** set all errors to excptions ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** begin a transaction ***/
$dbh->beginTransaction();
$dbh->exec("INSERT INTO animals
(animal_name, animal_type, last_updated)
VALUES ('bruce', 'dingo', DATETIME('NOW'));");
$dbh->exec("INSERT INTO animals
(animal_name, animal_type, last_updated)
VALUES ('bruce', 'wombat', DATETIME('NOW'));");
$dbh->exec("INSERT INTO animals
(animal_name, animal_type, last_updated)
VALUES ('bruce', 'koala', DATETIME('NOW'));");
/*** commit the INSERTs ***/
$dbh->commit();
echo 'done';
}
catch(Exception $e)
{
echo $e->getMessage();
}
?>
Retrieving Records
Of course, PDO makes getting the created records as easy as inserting them.
<table>
<thead><tr><th>Animal ID</th><th>Animal Name</th><th>Animal Type</th><th>Last Updated</th></tr></thead>
<tfoot></tfoot>
<tbody>
<?php
/*** make it or break it ***/
error_reporting(E_ALL);
try
{
/*** create the database file in /tmp ***/
$dbh = new PDO("sqlite:/tmp/animals.sqlite");
/*** set all errors to excptions ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM animals";
$result = $dbh->query($sql);
foreach($result as $row)
{
echo '<tr>
<td>'.$row['animal_id'].'</td>
<td>'.$row['animal_name'].'</td>
<td>'.$row['animal_type'].'</td>
<td>'.$row['last_updated'].'</td>
</tr>';
}
}
catch(Exception $e)
{
echo $e->getMessage();
}
?>
</tbody>
</table>
The HTML table created should look a little like this.
| Animal ID | Animal Name | Animal Type | Last Updated |
|---|---|---|---|
| 1 | bruce | dingo | 2008-11-09 03:40:41 |
| 2 | bruce | wombat | 2008-11-09 03:40:41 |
| 3 | bruce | koala | 2008-11-09 03:40:41 |
Add Duplicate Record
As the animal_type field was defined as UNIQUE in the table definition, only one type of animal may be entered. Should the entry needed to be updated, rather than refused, upon INSERT, most databases would handle this with ON DUPLICATE KEY UPDATE. Because this feature is omitted in SQLite, another solution needs to be found.
A trigger could be created within SQLite that mimics ON DUPLICATE KEY UPDATE,but for this tutorial a way forward can be had with exceptions. Because the error mode is set to PDO::ERRMODE_EXCEPTION, the exception can be caught, and the Exception error code checked, and an UPDATE can be run on that row.
<?php
/*** make it or break it ***/
error_reporting(E_ALL);
/*** new variables to INSERT ***/
$animal_name = 'sheila';
$animal_type = 'dingo';
try
{
/*** create the database file in /tmp ***/
$dbh = new PDO("sqlite:/tmp/animals.sqlite");
/*** set all errors to excptions ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO animals
(animal_name, animal_type, last_updated)
VALUES
(:animal_name, :animal_type, DATETIME('NOW'))";
/*** prepare the statement ***/
$stmt = $dbh->prepare($sql);
/*** bind the params ***/
$stmt->bindParam(':animal_name', $animal_name, PDO::PARAM_STR);
$stmt->bindParam(':animal_type', $animal_type, PDO::PARAM_STR);
/*** run the sql statement ***/
$stmt->execute();
echo 'done';
}
catch(Exception $e)
{
echo $e->getMessage();
}