If Not True Then False

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...
Categories:
Tagged with:

29 Comments

Leave a Comment

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

Input your comment.
help

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=""> <s> <strike> <strong>

Input your name.

carla

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

reply Reply
JR

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

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

reply Reply
nicole

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

reply Reply
Suki

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

reply Reply
JavierCane

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

Hi JavierCane,

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

reply Reply
JavierCane

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

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

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 Reply
Pescar Hoinar

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 Reply
el mato

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 Reply
John Lane

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

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

reply Reply
anafa david mudi

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

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 Reply
anafa david mudi

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

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

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

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 Reply