Porto

By Kevin Waterson

Contents

  1. Abstract
  2. The Database
  3. Creating Records
  4. Viewing Records
  5. Add Duplicate Record
  6. Credits

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_ERRMODEPDO::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_ERRMODEPDO::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_ERRMODEPDO::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 IDAnimal NameAnimal TypeLast 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_ERRMODEPDO::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_namePDO::PARAM_STR);
        
$stmt->bindParam(':animal_type'$animal_typePDO::PARAM_STR);

        
/*** run the sql statement ***/
        
$stmt->execute();

        echo 
'done';
    }
    catch(
Exception $e)
    {
        echo 
$e->getMessage();
    }

When the above code is run, an exception is thrown and caught in the catch block. The exception looks like this.

SQLSTATE[23000]: Integrity constraint violation: 19 column animal_type is not unique

This tells us that an attempt has been made to duplicate an existing entry in the animal_type table that is not unique. What the exception message also provides is the SQLSTATE[23000]. This is the error code and can be caught but using getCode() instead of getMessage() within the catch block. Once the error message is identified, an update can be performed in the catch block.


<?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_ERRMODEPDO::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_namePDO::PARAM_STR);
        
$stmt->bindParam(':animal_type'$animal_typePDO::PARAM_STR);

        
/*** run the sql statement ***/
        
$stmt->execute();

        echo 
'done';
    }
    catch(
Exception $e)
    {
        if(
$e->getCode() == 23000)
        {
            try
            {
                
$sql "UPDATE
                animals
                SET
                animal_name = :animal_name,
                last_updated = DATETIME('NOW')
                WHERE
                animal_type = :animal_type"
;
                
$stmt $dbh->prepare($sql);
                
$stmt->bindParam(':animal_name'$animal_namePDO::PARAM_STR);
                
$stmt->bindParam(':animal_type'$animal_typePDO::PARAM_STR);
                
$stmt->execute();
            }
            catch(
Exception $e)
            {
                echo 
$e->getMessage();
            }
        }
    }
?>

Now when the table is displayed when using the script from earlier to create a HTML table, the results look like this.

Animal IDAnimal NameAnimal TypeLast Updated
1 sheila dingo 2008-11-09 04:28:21
2 bruce wombat 2008-11-09 03:40:41
3 bruce koala 2008-11-09 03:40:41

Note that the first record now has a dingo named "sheila" and that the timestamp of the last_updated fields has been changed to the time of INSERT

Credits

Thanks to Duckman for his concise and insightful views on the human condition.