PHP / PDO / SQLite3 Example - Comment Page: 1

This 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...

29 comments on “PHP / PDO / SQLite3 Example - Comment Page: 1

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

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

      Reply
      • 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.

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

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

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

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

      Reply
    5. Thank you very much !!!! very helpful !

      Reply
    6. 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();
      }

      Reply
      • Hi JavierCane,

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

        Reply
        • 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 :-/

          Reply
          • Hi again JavierCane,

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

            
            public bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )
            

            So those variables are automatically created and assigned to null.

            I wrote quick example class to demonstrate this:

            
            < ?php
            
                class Test {
            
                    private $var;
            
                    public function __construct() {}
            
                    public function bind(&$var) {
                        $this->var = &$var;
                    }
            
                    public function dump() {
                        var_dump($this->var);
                    }
            
                }
            
                $test = new Test();
            
                $test->bind($example);
            
                $test->dump();
            
                $example = 'Some real value';
            
                $test->dump();
            
                $example = 'Another value';
                
                $test->dump();
            

            And output is:

            NULL
            string(15) "Some real value"
            string(13) "Another value"
            

            Simple command line example:

            
            php -r '$a = &$b; var_dump($a); $b = 1; var_dump($a);'
            NULL
            int(1)
            
            Reply
            • I see JR, thanks a lot for your explanation and the detailed examples!!

              Reply
    7. Thanks very interesting blog!

      Reply
    8. 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!

      Reply
    9. 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.

      Reply
    10. 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()

      Reply
    11. 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

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

        Reply
    12. Thanks for this – a really helpful article!!

      Reply
    13. 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?

      Reply
      • 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?

        Reply
        • 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

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

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

            
                // 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();
                }
            

            And another simple example: http://php.net/manual/en/sqlite3.prepare.php

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

              Reply
    14. 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.

      Reply
      • 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.

        Reply

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Close