Sqlite, emulating on duplicate update functionnality

On my last article i’ve spoken about what a common MySQL developer needs to know to use Sqlite. In this article we’ll try to simulate a missing feature which is updating if key exists “on duplicate update on MySQL” using PHP Exceptions.

            try {
                // init database cnx.
                $db = new PDO('sqlite:' . $databasePath);
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $sql = "INSERT INTO table
                        (id, content)
                        (:id, :content)";

                // Prepare statement
                $stmt = $db->prepare($sql);

                // bind the params
                $stmt->bindParam(':id', $id);
                $stmt->bindParam(':content', $content);

            // Exception code for constraint violation is 23000
            // That's why we catch this error code and do update :
            } catch (Exception $e) {
                if ($e->getCode() == 23000) {
                    try {
                        $sql = "UPDATE
                            content = :content
                            id = :id";
                        $stmt = $db->prepare($sql);
                        $stmt->bindParam(':content', $content);
                        $stmt->bindParam(':id', $id);
                    } catch (Exception $e) {
                        echo $e->getMessage();

PHP and SQLite, What you need to know.

If you are a MySQL user,  you have certainly acquired mechanism such as string escaping, database connection function are not the same with SQLITE, here a list of important things to know :

SQLite Version 2 and 3

– This is what we can read on the SQLite official site :

The format used by SQLite database files has been completely revised. The old version 2.1 format and the new 3.0 format are incompatible with one another. Version 2.8 of SQLite will not read a version 3.0 database files and version 3.0 of SQLite will not read a version 2.8 database file.

So if you be careful on your choice of which version you’ll use, or you convert SQLite version 2 to version 3 using shell command :

sqlite OLD.DB .dump | sqlite3 NEW.DB

Another important thing to know is that “SQLiteDatabase” will not work with SQLite Version2 you will have an error saying that database is not found or encrypted and to prevent that use PDO instead.

PDO And SQLite

Here some examples of establishing database connection and simple queries :

# SQLite Database init PDO
$sth = new PDO('path/to/my/db.sqlite');

# Insert

# Close connection
$sth = null;

Escaping Strings

Common used function with MySQL is PHP addslashes to escape quotes (simple and double) this will not work with SQLite because escaping is not the same.

To escape quotes (‘) you have to double em (same thing for double quotes), if you want to insert “l’homme” string you have to do like this :

insert into .. values ('l''homme', ..)

But the same advise here, use PDO :

$sting = $sbh->quote($sting_to_quote);

SQLite Tools

SQLite Manager Firefox Extension

Manage any SQLite database on your computer.
An intuitive heirarchical tree showing database objects.
Helpful dialogs to manage tables, indexes, views and triggers.
You can browse and search the tables, as well as add, edit, delete and duplicate the records.
Facility to execute any sql query.

Navicat for SQLite

Navicat for SQLite is a powerful Database administration and development tool for SQLite. It works with SQLite version 2 and 3 and supports most of the SQLite features including Trigger, Index, View, and so on. Features in Navicat are sophisticated enough to provide professional developers for all their specific needs, yet easy to learn for users who are new to SQLite.