Using sqlite efficiently with php

Question

I'm porting over a small php app to use sqlite, and this is the first time that I use sqlite with php and its been a while since i last coded something with php(it was php4 by the time). The trouble i'm having right now is that apparently with sqlite you need to call the sqlite3 object several times since it apparently doesnt establish a permanent connection with the database, so my code right now is filled with

$db = new SQLite3('test.db');

in every single function in the code. is this efficient? is there a better and cleaner way of doing this?

the other issue that i'm having is if i use sqlite inside say include/functions.php it tries to look for test.db inside this include/ dir when it should be using the app root. how could i fix this efficiently?

"since it apparently doesn't establish a permanent connection with the database" - Why would you ever think this? — Dark Falcon
– Dark Falcon, Commented Dec 5, 2010 at 23:59
@Dark Falcon: since the $db object has to be called several times in the code. i remember with mysql you would just connect once and the session was active until you closed it. — zafrada
– zafrada, Commented Dec 6, 2010 at 0:01
And so it should also be with Sqlite. Sure you're not doing something wrong? — Dark Falcon
– Dark Falcon, Commented Dec 6, 2010 at 0:07
@Dark Falcon: well, the thing is i don't know how to make the $db object to be seen by all the functions and php files in my project. i could make it a global variable, but i've read thats not safe and a good practice with php, so im curious to listening to a more efficent way of resolving this. — zafrada
– zafrada, Commented Dec 6, 2010 at 0:11
There is nothing wrong with using a global variable for something like a database connection. Perhaps what you've heard as being dangerous is using register_globals, which is indeed unwise. — Dark Falcon
– Dark Falcon, Commented Dec 6, 2010 at 0:13

deceze · Accepted Answer · 2010-12-06 02:39:14Z

1

You only have to establish a connection again if your $db variable goes out of scope or is otherwise destroyed. If you create the variable inside a function, it will go out of scope as soon as the function exits, forcing you to recreate the variable/connection in the next function.

As such, in some form or another, you will need to create the variable in a scope that is accessible everywhere a database connection is required. The easiest but ugliest is to create it in the global scope. A better way would be to create a static class that holds the connection and can return the connection handler on request. Another way would be to use dependency injection, basically meaning you pass the connection handle into every function or object where it is needed.

The right answer depends on your existing architecture.

answered Dec 6, 2010 at 2:39

deceze♦

525k89 gold badges806 silver badges954 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alfred · Accepted Answer · 2010-12-06 03:03:29Z

Code

<?php
class SQL {
    private $db;

    function __construct() {
        try {
           /*** connect to SQLite database ***/
            $db = new PDO("sqlite::memory:");
            $db->exec("CREATE TABLE blog (Id INTEGER PRIMARY KEY, text TEXT)");   
            $this->db = $db;
            /*** a little message to say we did it ***/
            echo 'database created in memory';
        } catch(PDOException $e) {
            echo $e->getMessage();
        }
    }

    function add($text) {
        $this->db->exec("INSERT INTO blog(text) VALUES ('$text')");
    }

    function get() {
        $res = array();
        $result = $this->db->query('SELECT text FROM blog');
        foreach ($result as $row) {
            $res[] = $row['text'];
        }

        return $res;
    }
}
?>

<?php
$sql = new SQL();
$sql->add('hello');
print_r($sql->get());
?>

<?php
$sql = new SQL();
$sql->add('hello');
$sql->add('world');
print_r($sql->get());
?>

Output:

php sql.inc 

database created in memoryArray
(
    [0] => hello
)

database created in memoryArray
(
    [0] => hello
    [1] => world
)

As you can see no sprinkling off $db =.

P.S: You should use PDO because it is superior to just calling new SQLite3

Collectives™ on Stack Overflow

Using sqlite efficiently with php

2 Answers 2

Comments

Code

Output:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Code

Output:

Comments

Your Answer

Sign up or log in

Post as a guest

Related