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

Example Code Output

Share this post


  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. :)

      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

    • Hi JavierCane,

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

      • Yes, you’re right. I’m referring to the bindParam() calls since they use the variables $title, $message and $time which are not set in that moment.
        I really think that I’m skipping out something, but at a first sight is what I can see :-/

        • Hi again JavierCane,

          Actually bindParam calls uses reference (for second parameter $variable):

          So those variables are automatically created and assigned to null.

          I wrote quick example class to demonstrate this:

          And output is:

          Simple command line example:

          • I see JR, thanks a lot for your explanation and the detailed examples!!

  8. Thanks very interesting blog!

  9. Thank you. I knew in-memory SQLite databases were possible, but getting through’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.

  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 –

    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

    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:

    • Thank you for this note it saved me some time figuring out what I would need to define as those constants!

  13. Thanks for this – a really helpful article!!

  14. Thanks for a great tutorial. My question is when should one create a file database and memory database? which is beneficial to which circumstances. Also, will mysqli be able to connect to a sqlite3 created database and how?

    • Hello anafa david mudi,

      You are welcome!

      When you want to use persistent storage, then you should use file database and when you want query very quickly (not so valuable data), then you can use memory database.

      So memory database is lightning fast, but data disappear example when you boot server/machine. So this is useful example for cache.

      And mysqli can’t connect to sqlite3 databases. Actually I’m not sure why you want to use MySQL Improved Extension to connect sqlite3 database?

      • Thanks for the reply.
        On mysqli and sqlite, thanks for the clarification but my reason was i loved using mysqli prepared statement for my CRUD but when i try (tho new to using sqlite3), i get a lot of errors, doing a search for on the errors and how to properly prepare statments in sqlite3 was not help hence my trying to use mysqli prepared statement to try and connect and use them on an sqlite3 created database.

        Please can you add some sqlite3 prepared statement examples for the CRUD and also using fetcharray(assoc) etc

        i hope it makes sense thanks

        • You have to remember that mysql and sqlite3 are totally different products.

          But yes you can use prepared statements, check this part:

          And another simple example:

          • Thank you. will test the above code and modify appropriately.

  15. Hey there I am running into an issue with your code above, this is the second time I have seen this error. :(

    SQLSTATE[HY000] [14] unable to open database file

    I am using the latest php5, sqlite3 & nginx.
    Do I need to manually create a file.db to get past this issue?
    Any help would be grateful here.
    Thanks Keffle.

    • Hi Keffle,

      This looks like a permission problem. You have to set read and write permissions to your php-fpm user if you are using nginx+php-fpm. So check your php-fpm config and change permissions.


Submit a Comment

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