PHP / PDO / SQLite3 Example

PHP LogoThis is a PHP, PDO and SQLite3 example, which demonstrates the SQLite3 databse usage with PHP-PDO. The PHP / PDO / SQLite3 example code demonstrates following things, and their use:

  • Create / Connect SQLite3 databases
  • Use SQLite3 file and memory databases
  • Create tables in SQLite3 database
  • Use SQLite3 db different datetime formats
  • Insert data to SQLite3 database
  • PDO / SQLite3 prepared statements
  • Bind parameters to statement variables
  • Bind values to statement variables
  • Quote a string for use in a query
  • Update data in SQLite3 database
  • Select / Query from SQLite3 database and print query output
  • Drop SQLite3 table
  • Close SQLite3 connections

PHP / PDO / SQLite3 Example Code

<?php
 
  // Set default timezone
  date_default_timezone_set('UTC');
 
  try {
    /**************************************
    * Create databases and                *
    * open connections                    *
    **************************************/
 
    // Create (connect to) SQLite database in file
    $file_db = new PDO('sqlite:messaging.sqlite3');
    // Set errormode to exceptions
    $file_db->setAttribute(PDO::ATTR_ERRMODE, 
                            PDO::ERRMODE_EXCEPTION);
 
    // Create new database in memory
    $memory_db = new PDO('sqlite::memory:');
    // Set errormode to exceptions
    $memory_db->setAttribute(PDO::ATTR_ERRMODE, 
                              PDO::ERRMODE_EXCEPTION);
 
 
    /**************************************
    * Create tables                       *
    **************************************/
 
    // Create table messages
    $file_db->exec("CREATE TABLE IF NOT EXISTS messages (
                    id INTEGER PRIMARY KEY, 
                    title TEXT, 
                    message TEXT, 
                    time INTEGER)");
 
    // Create table messages with different time format
    $memory_db->exec("CREATE TABLE messages (
                      id INTEGER PRIMARY KEY, 
                      title TEXT, 
                      message TEXT, 
                      time TEXT)");
 
 
    /**************************************
    * Set initial data                    *
    **************************************/
 
    // Array with some test data to insert to database             
    $messages = array(
                  array('title' => 'Hello!',
                        'message' => 'Just testing...',
                        'time' => 1327301464),
                  array('title' => 'Hello again!',
                        'message' => 'More testing...',
                        'time' => 1339428612),
                  array('title' => 'Hi!',
                        'message' => 'SQLite3 is cool...',
                        'time' => 1327214268)
                );
 
 
    /**************************************
    * Play with databases and tables      *
    **************************************/
 
    // Prepare INSERT statement to SQLite3 file db
    $insert = "INSERT INTO messages (title, message, time) 
                VALUES (:title, :message, :time)";
    $stmt = $file_db->prepare($insert);
 
    // Bind parameters to statement variables
    $stmt->bindParam(':title', $title);
    $stmt->bindParam(':message', $message);
    $stmt->bindParam(':time', $time);
 
    // Loop thru all messages and execute prepared insert statement
    foreach ($messages as $m) {
      // Set values to bound variables
      $title = $m['title'];
      $message = $m['message'];
      $time = $m['time'];
 
      // Execute statement
      $stmt->execute();
    }
 
    // Prepare INSERT statement to SQLite3 memory db
    $insert = "INSERT INTO messages (id, title, message, time) 
                VALUES (:id, :title, :message, :time)";
    $stmt = $memory_db->prepare($insert);
 
    // Select all data from file db messages table 
    $result = $file_db->query('SELECT * FROM messages');
 
    // Loop thru all data from messages table 
    // and insert it to file db
    foreach ($result as $m) {
      // Bind values directly to statement variables
      $stmt->bindValue(':id', $m['id'], SQLITE3_INTEGER);
      $stmt->bindValue(':title', $m['title'], SQLITE3_TEXT);
      $stmt->bindValue(':message', $m['message'], SQLITE3_TEXT);
 
      // Format unix time to timestamp
      $formatted_time = date('Y-m-d H:i:s', $m['time']);
      $stmt->bindValue(':time', $formatted_time, SQLITE3_TEXT);
 
      // Execute statement
      $stmt->execute();
    }
 
    // Quote new title
    $new_title = $memory_db->quote("Hi''\'''\\\"\"!'\"");
    // Update old title to new title
    $update = "UPDATE messages SET title = {$new_title} 
                WHERE datetime(time) > 
                datetime('2012-06-01 15:48:07')";
    // Execute update
    $memory_db->exec($update);
 
    // Select all data from memory db messages table 
    $result = $memory_db->query('SELECT * FROM messages');
 
    foreach($result as $row) {
      echo "Id: " . $row['id'] . "\n";
      echo "Title: " . $row['title'] . "\n";
      echo "Message: " . $row['message'] . "\n";
      echo "Time: " . $row['time'] . "\n";
      echo "\n";
    }
 
 
    /**************************************
    * Drop tables                         *
    **************************************/
 
    // Drop table messages from file db
    $file_db->exec("DROP TABLE messages");
    // Drop table messages from memory db
    $memory_db->exec("DROP TABLE messages");
 
 
    /**************************************
    * Close db connections                *
    **************************************/
 
    // Close file db connection
    $file_db = null;
    // Close memory db connection
    $memory_db = null;
  }
  catch(PDOException $e) {
    // Print PDOException message
    echo $e->getMessage();
  }
?>

Example Code Output

Id: 1
Title: Hello!
Message: Just testing...
Time: 2012-01-23 06:51:04
 
Id: 2
Title: Hi''\'''\""!'"
Message: More testing...
Time: 2012-06-11 15:30:12
 
Id: 3
Title: Hi!
Message: SQLite3 is cool...
Time: 2012-01-22 06:37:48
Follow If Not True Then False Updates!

17 Comments

  1. What should I change if I do the same, but with mysql?

  2. very well,thanks

  3. what is this line do $memory_db = new PDO(‘sqlite::memory:’);?
    what for?
    sorry im new to this:(
    please help

    • Hi carla,

      Nice to see you here. :)

      $memory_db = new PDO('sqlite::memory:');

      Line creates new sqlite database in RAM, this database is just temporary. This is very useful if you have to create temporary database which work lightning fast.

      • thanks for the quick reply..
        i find your post really helpful..:)

        • You are welcome! And nice to hear that my post is helpful. :)

  4. how to apply the code on ZendFramework.. I am the beginner of Zend user…

  5. use PDO::PARAM_INT, PDO::PARAM_STR, etc instead of SQLITE3_INTEGER, SQLITE3_TEXT, etc if you only use PDO version of sqlite

  6. Thank you very much !!!! very helpful !

  7. Correct me if I’m wrong, but I think that you should make the bindValue()’s calls inside the foreach loop in order to have available the values to insert:

    foreach ( $result as $m )
    {
    // Bind values directly to statement variables
    $stmt->bindValue( ‘:id’, $m['id'], PDO::PARAM_INT );
    $stmt->bindValue( ‘:title’, $m['title'], PDO::PARAM_STR );
    $stmt->bindValue( ‘:message’, $m['message'], PDO::PARAM_STR );

    // Format unix time to timestamp
    $formatted_time = date( ‘Y-m-d H:i:s’, $m['time'] );
    $stmt->bindValue( ‘:time’, $formatted_time, SQLITE3_TEXT );

    // Execute statement
    $stmt->execute();
    }

    • Hi JavierCane,

      Maybe I miss something, but those bindValue() calls are inside foreach loop? Or did you mean bindParam() calls?

  8. Thanks very interesting blog!

  9. Thank you. I knew in-memory SQLite databases were possible, but getting through PHP.com’s documentation is kinda like wading thru a pool of jello sometimes. The examples are awesome! Thanks, again!

  10. I am planning to build my own CMS based on SQLITE. Till few days ago I was still working on it in sqlite2.
    You changed my mind with this example. I will switch to PDO SQLITE.
    Thanks.

  11. Hi,

    I am attempting to get spatialite working with PDO:Sqlite. How does one load sqlite extensions with PDO? (PHP Version v5.5.9-1ubuntu4.4 pdo_sqlite v3.8.2)

    I have tried the following –


    $db->loadExtension('libspatialite.so');

    Though it fails with –

    PHP Fatal error: Call to undefined method PDO::loadExtension()

  12. Handy little test, thanks. I had to add this just inside the opening <?php..

    // Kludges
    const SQLITE3_TEXT = PDO::PARAM_STR;
    const SQLITE3_INTEGER = PDO::PARAM_INT;

    Also, just an observation – my initial action was to drop this into my htdocs but I needed to change the file_db to a path writable by the browser (/tmp worked for me). However, the output isn't in HTML so you might as well just run it from the command-line.

    $ php dbtest.php

    And, here's a link to download the file: http://bit.ly/sqlitetest

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>