PHP and SQLite
Posted under » PHP on 24 April 2015
MySQL can be an overkill when all you want is a simple table and not a relational database. SQLite does more than that. It runs on all, I mean all platform and is particularly found in mobile applications.
On the desktop there are free tools you can use. I recommend the cross platform DB Browser for SQLite. Alternatively in Ubuntu, you can use SQLiteman.
For PHP, you must have it installed first.
Basic Queries with PDO
<¿php
try {
$dbh = new PDO("sqlite:main.db");
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$dbh->beginTransaction();
$sql = 'select * from umah limit 10';
foreach ($dbh->query($sql) as $row) {
print $row['tetel'] . " - ";
print $row['date'] . "<br>";
}
$dbh->rollback();
?>
PDO is made up of two basic classes, PDO and PDOStatement. PDO represents a connection, which internally contains a sqlite3 structure, and PDOStatement represents a statement handle, which internally contains a sqlite3_stmt structure. The query methods in the PDO class closely follow the methods in the SQLite C API. There are three ways to execute queries:
- exec(): Executes queries that don't return any data. Returns the number of affected rows, or FALSE if there is an error. This mirrors sqlite3_exec().
- query(): Executes a query and returns a PDOStatement object representing the result set, or FALSE if there is an error.
- prepare(): Compiles a query and returns a PDOStatement object or FALSE if there is an error. This offers better performance than query() for statements that need to be executed multiple times, because it can be reset, avoiding the need to recompile the query.
Additionally, transaction management in PDO can be performed through a method invocation using beginTransaction(), commit(), and rollback().
