Accessing SQLite Databases in PHP using PDO

Discussion in 'PHP' started by pradeep, Apr 27, 2013.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    PDO or PHP Data Objects is a data abstraction layer, i.e. it provides uniform methods to access different types of databases, as a result switching between or moving to a different database system is relatively easier. So, in simple language the code to access a SQLite db will also work for MySQL db with minor changes. PDO provides a plethora of database drivers like MySQL, Oracle, Postgre SQL, SQLite, Firebird, etc. In this article we will look at accessing a SQLite db using PDO.

    Installing PDO



    PDO & driver for SQLite is enabled by default from PHP version 5.1.0 onwards, if not so use the following command to install PDO:

    Code:
    $ yum install php-pdo
    
    Windows users please refer to the official PDO installation documentation at http://www.php.net/manual/en/pdo.installation.php.

    Access SQLite DB



    Let's get our hands dirty with a little code, first let's try to list all the different database drivers available to PDO, see the code snippet below:

    PHP:
    <?
    print_r(PDO::getAvailableDrivers());
    ?>
    PDO needs a DSN (Data Source Name) to connect to, for SQLite the DSN is just the path to the database file. Follow the code snippet below:

    PHP:
    <?php

    // DSN
    $dsn 'sqlite:/home/pradeep/test.db';
    // Connect to SQLite database
    $sqlite_db = new PDO($dsn);

    // Create table
    $sqlite_db->exec("CREATE TABLE IF NOT EXISTS books (
                        id INTEGER PRIMARY KEY, 
                        title TEXT, 
                        author TEXT, 
                        entry_time INTEGER)"
    );
    ?>
    Now, we have connected to the database and created a table, next let's look at a few more basic things required to work with databases, like preparing queries, using placeholders, etc.

    [Thread=8074]Prepared queries[/Thread] help optimizing code when the same query is executed multiple times with changes in the values, similarly placeholder help you prevent SQL injection attacks on your code by automatically add quotes to values. Let's look at the code snippet below:

    PHP:
    <?
    // normal prepared query & placeholder
    $prepare1 $sqlite_db->prepare('INSERT INTO books(id, title, author, entry_time) VALUES(?,?,?,?)');

    // execute
    $prepare1->execute(1,'Oath Of The Vayuputras','Amish','2013-04-22 00:00:00');

    // named placeholder prepared query
    $prepare2 $sqlite_db->prepare('INSERT INTO books(id, title, author, entry_time) VALUES(:id,:title,:author,:time)');

    // execute
    $prepare2->execute(array(':id' => 2':title' => 'Twenties Girl'':author' => 'Sophie Kinsella'':time' => '2013-04-25 00:00:00'));

    // fetching data
    $sth $dbh->prepare'SELECT * FROM books', array( PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY ) );
    $sth->execute();
    $books $sth->fetchAll();
    ?>
    That's all you need to get started.
     
    coderzone and shabbir like this.
  2. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    736
    Likes Received:
    38
    Trophy Points:
    28
    I really like this but one suggestion is you should be using <?php and not <? because these days default PHP installation does not have the shorter version enabled by default.
     
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice