PDO or SQLite Functions?

[Angry Coder]

Hi,

Can you please tell me whether it's better to use SQLite functions (http://ca.php.net/manual/en/ref.sqlite.php) or PDO functions (http://www.litewebsite.com/?c=49#sqlite3).

Please advise. Thanks.

[Jake Arkinstall]

I'd say PDO.

This is because you can simply change the DSN String to connect to another database, and you wouldn't need to change any more code.

That is, of course, if you use queries and methods that work with both drivers.

[Angry Coder]

I'm 100% sure I'll not use other than SQLite.

Should I use SQLite functions in that case? If so, any idea when SQLite3 will be supported?

Thanks.

[Jake Arkinstall]

There are only two things to be 100% sure about - death and taxes.

If you are positive that you'll never need a more powerful engine, sure the SQLite functions could be used, but I'd still use PDO.

That's because PDO has prepared statements - which are fantastic for security and speed reasons.

A prepared statement is where the driver sends MySQL/SQLite a version of the query but with placeholders rather than actual values. MySQL /SQLite then sorts out exactly how it's going to run the query, and PDO then sends the values. This is the same speed as a usual query with one query, but when using the same query for different data say 10 times, it's about 8 times faster than running 10 individual queries

For example:

<?php
$q = $database->prepare("INSERT INTO squares (base, value) VALUES (:i, :j)");
$i = 1;
$j = 1;
$q->bindParam(':i', $i, PDO::PARAM_INT);
$q->bindParam(':j', $j, PDO::PARAM_INT);
for(; $i < 100; $i++){
    $j = pow($i, 2);
    $q->execute();
}

It also means that you don't need to escape data because it isn't inserted directly into the query.

[Angry Coder]

All right, I'll use PDO.


Thanks

[Darren884]

Use PDO I am using it for one of my projects and it makes it very easy to switch database types.

[risoknop]

Yes listen to arkinstall. PDO is good. I have just switched from MySQLi to PDO and I haven't noticed any problems or performance issues. It is true that PDO doesn't offer as many options as other extensions but it is very portable and has 90% of stuff.

[Jake Arkinstall]

The reason it doesn't offer as much is because it can't - thanks to incompatibilities with other drivers.