Search notes:
PHP: Accessing an SQLite database with PDO
Some
PHP code snippets that try to demonstrate how an
SQLite can be accessed from PHP and PDO.
Create the database
The first script uses file_exists($dbFile) to check if the SQLite database already exists and, if so, deletes it with unlink($dbFile).
With new PDO("sqlite:$dbFile"), a new and empty SQLite database is created.
<html>
<head><title>PDO - SQLite: create db</title></head>
<body>
<?php
$dbFile = 'the.db';
if (file_exists($dbFile)) {
echo("The file <code>$dbFile</code> exists. It is going to be deleted.<p>");
unlink($dbFile);
}
$db = new PDO("sqlite:$dbFile");
?>
<a href='02_create_table.html'>create a table</a>
</body>
</html>
Create a table
This script uses the
exec method to run a
create table statement.
<html>
<head><title>PDO - SQLite: Create table</title></head>
<body>
<?php
$db = new PDO("sqlite:the.db");
$db -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$db -> exec('create table foo (col_1 number, col_2 varchar2)');
print ("Table foo created");
print ("<p><a href='02_create_table.html'>Create table again</a> and receive an error");
}
catch(PDOException $e) {
print ("exception " . $e->getMessage());
}
print ("<p><a href='03_insert_values.html'>Insert values</a> into table");
?>
</body>
</html>
Insert values
This example uses prepare to prepare an insert statement with parameters (:val_1 and :val_2).
The returned (prepared) statement can then be used (executed) mulitple times with
In order to do so, the statement's bindParam method must be used to associate the statement's placeholders with PHP-variables.
When the statement's execute method is called, the values as set to the variables are then inserted into the table.
<html>
<head><title>PDO - SQLite: Insert values</title></head>
<body>
<?php
$db = new PDO("sqlite:the.db");
$db -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$stmt_h = $db -> prepare ('insert into foo values (:val_1, :val_2)');
$stmt_h -> bindParam(':val_1', $v1);
$stmt_h -> bindParam(':val_2', $v2);
$v1 = 1;
$v2 ='one';
$stmt_h -> execute();
$v1 = 2;
$v2 ='two';
$stmt_h -> execute();
$v1 = 3;
$v2 ='three';
$stmt_h -> execute();
$v1 = 4;
$v2 ='four';
$stmt_h -> execute();
$v1 = 5;
$v2 ='five';
$stmt_h -> execute();
}
catch(PDOException $e) {
print ("exception " . $e->getMessage());
}
print ("<p><a href='04_select_values.html'>Select</a> the inserted values");
?>
</body>
</html>
Select values
This example tries to demonstrate how the inserted values can be queried with a
select statement.
<html>
<head><title>PDO - SQLite: Select values</title></head>
<body>
<?php
$db = new PDO("sqlite:the.db");
$db -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$res = $db -> query('select * from foo order by col_2');
print '<table>';
foreach ($res as $row) {
print '<tr><td>' . $row['col_1'] . '</td><td>' . $row['col_2'] . '</td></tr>';
}
print '</table>';
}
catch(PDOException $e) {
print ("exception " . $e->getMessage());
}
print "<p><a href='05_select_values_with_param.html'>Select</a> values with parameter";
?>
</body>
</html>
Select values with parameters
As in the insert example above, it is also possible to use parameters in select statements.
<html>
<head><title>PDO - SQLite: Select values with params</title></head>
<body>
<?php
$db = new PDO("sqlite:the.db");
$db -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$stmt_h = $db -> prepare('select * from foo where col_1 > :param_1 and col_2 like :param_2');
$stmt_h -> execute(array(1, '%o%'));
$res = $stmt_h -> fetchAll();
print '<table>';
foreach ($res as $row) {
print '<tr><td>' . $row['col_1'] . '</td><td>' . $row['col_2'] . '</td></tr>';
}
print '</table>';
}
catch(PDOException $e) {
print ("exception " . $e->getMessage());
}
print "<p><a href='99_clean_up.html'>clean up</a>";
?>
</body>
</html>
Cleaning up
Delete the database.
<html>
<head><title>PDO - SQLite: Select values with params</title></head>
<body>
<?php
unlink('the.db');
print "Database deleted, <a href='01_create_db.html'>start over</a>.";
?>
</body>
</html>