SQLite PHP tutorial

bawltherapistΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 7 μήνες)

90 εμφανίσεις

5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
1/10
Home
SQLite PHP tutorial
This is a PHP programming tutorial for the SQLite database. It covers the basics of SQLite
programming with PHP language. There are two ways to code PHP scripts with SQLite library. We
can use procedural functions or OOP objects and methods. In this tutorial, we use the classical
procedural style. You might also want to check the
PHP tutorial
,
SQLite tutorial
or
SQLite Perl
tutorial
on ZetCode.
Installation
To work with this tutorial, you must install several packages. The apache2, libapache2-mod-php5,
php5-sqlite packages. sqlite command line tool is optional, but recommended.
The document root directory is a directory, where you place your html and php files. It is a place,
where the apache server looks for the files, that make up the web site.
The document root for apache2 server can be changed at /etc/apache2/sites-available/default file.
This is for Ubuntu.
DocumentRoot /var/www/
This is a portion of the above mention configuration file. The default document root directory is
/var/www.
We should also edit the php.ini file to turn the magic quotes off. Since PHP 5.3.0 they are off by
default. On my system, I have currently PHP 5.2.6 so I had to edit the php.ini file. It is located at
/etc/php5/apache2/php.ini on my system.
Magic Quotes is a process that automatically escapes incoming data to the PHP script. It's preferred
to code with magic quotes off and to instead escape the data at runtime, as needed. We are going to
use
sqlite_escape_string()
function to escape strings if necessary.
; Magic quotes
;
; Magic quotes for incoming GET/POST/Cookie data.
magic_quotes_gpc = Off
; Magic quotes for runtime-generated data, e.g. data from SQL, from exec(), etc.
magic_quotes_runtime = Off
; Use Sybase-style magic quotes (escape ' with '' instead of \').
magic_quotes_sybase = Off
This is a portion of the php.ini file. Magic quotes are off. If you edited the file while apache was
running, you must restart the apache server.
We are also going to create a directory, where we will have our sqlite database files. In the document
root directory, /var/www on my Ubuntu system, we create a directory called db.
$ pwd
/var/www
$ ls -ld db
drwxrwxrwx 2 root root 4096 2009-12-01 22:04 db
5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
2/10
A web server must have a write & execute access to the directory. It is convenient to have a read
access too.
$ pwd
/var/www/db
$ ls -l test.db
-rw-rw-rw- 1 root root 6144 2009-12-01 22:04 test.db
Inside the db directory, we create a test.db file with read and write access rights.
SQLite database is called a zero configuration database. The only problems that could arise are
insufficient access rights.
First example
Our first example will test the version of the SQLite library and the version of the PHP language. If
it works, we have all installed correctly.
We create a simple PHP script and give it a name version.php. We place it into the document root
directory. It is /var/www on my system. Ensure, that the apache server is running.
$ /etc/init.d/apache2 status
* Apache is running (pid 22965).
We check, if the apache server is running. To start the server, we can use the /etc/init.d/apache2
start command.
<?php
echo sqlite_libversion();
echo "<br>";
echo phpversion();
?>
Now we start the browser and locate to http://localhost/version.php
The PHP code shows 2.8.17 and 5.2.6-2ubuntu4.5 strings on my system. You should get something
similar.
Figure: First example
Creating a table
In the following PHP code, we will create a database table.
<?php
$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);
$stm = "CREATE TABLE Friends(Id integer PRIMARY KEY," .
"Name text UNIQUE NOT NULL, Sex text CHECK(Sex IN ('M', 'F')))";
$ok = sqlite_exec($dbhandle, $stm, $error);
if (!$ok)
die("Cannot execute query. $error");
echo "Database Friends created successfully";
sqlite_close($dbhandle);
5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
3/10
?>
Besides creating a database table, we do some error checking.
$dbhandle = sqlite_open('db/test.db', 0666, $error);
The
sqlite_open()
function opens a SQLite database. The function has three parameters. The first
parameter is the filename of the database. According to the documentation, the second parameter is
ignored currently. The 0666 is the recommended value. If we cannot open the database, an error
message is put into the
$error
variable.
if (!$dbhandle) die ($error);
The
sqlite_open()
function returns a database handle on success or FALSE on error. The
die()
function outputs an error message and terminates the script.
$stm = "CREATE TABLE Friends(Id integer PRIMARY KEY," .
"Name text UNIQUE NOT NULL, Sex text CHECK(Sex IN ('M', 'F')))";
The
$stm
variable holds the SQL statement to create a Friends database table. Note that there are
two strings concatenated with the dot operator.
$ok = sqlite_exec($dbhandle, $stm, $error);
The
sqlite_exec()
executes a result-less statement against the database. The first parameter is
the database handle, that we obtained with the
sqlite_open()
function. The second parameter is
the statement, that we are about to execute. And the last parameter is the possible error message.
This is usually due to a syntax error. The function returns TRUE for success or FALSE for failure.
if (!$ok)
die("Cannot execute query. $error");
We check for possible errors. There could be two types of errors. SQL syntax error or insufficient
permissions.
echo "Database Friends created successfully";
If all went OK, we print a message 'Database Friends created successfully'. If there is some error,
this message is not printed, because the
die()
function terminates the execution of the PHP script.
sqlite_close($dbhandle);
We close the database handle. It is not necessary to do it explicitly. PHP language does it
automatically. But it is a good programming practice to do it.
Inserting data
In the following example, we will insert some data into the Friends database.
<?php

$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);

$stm1 = "INSERT INTO Friends VALUES(1,'Jane', 'F')";
$stm2 = "INSERT INTO Friends VALUES(2,'Thomas', 'M')";
$stm3 = "INSERT INTO Friends VALUES(3,'Franklin', 'M')";
$ok1 = sqlite_exec($dbhandle, $stm1);
if (!$ok1) die("Cannot execute statement.");
$ok2 = sqlite_exec($dbhandle, $stm2);
if (!$ok2) die("Cannot execute statement.");
$ok3 = sqlite_exec($dbhandle, $stm3);
if (!$ok3) die("Cannot execute statement.");
5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
4/10
echo "Data inserted successfully";
sqlite_close($dbhandle);
?>
We insert some data. We don't retrieve any data. Therefore we use again the
sqlite_exec()
function.
$stm1 = "INSERT INTO Friends VALUES(1,'Jane', 'F')";
$stm2 = "INSERT INTO Friends VALUES(2,'Thomas', 'M')";
$stm3 = "INSERT INTO Friends VALUES(3,'Franklin', 'M')";
Here we have three statements that will insert three rows into the Friends database.
$ok1 = sqlite_exec($dbhandle, $stm1);
if (!$ok1) die("Cannot execute statement.");
We execute the first statement. If something goes wrong, the script is terminated.
What if we wanted to add a name like O'Neil? The single quote ' character belongs to some unsafe
characters. Using them could lead to problems. We must properly escape them. The single quote
character is escaped by using another single quote character. '' Note that it is easily confused with a
double quote character.
<?php

$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);
$name = "O'Neill";
$name_es = sqlite_escape_string($name);
$stm = "INSERT INTO Friends VALUES(4,'$name_es', 'M')";
$ok1 = sqlite_exec($dbhandle, $stm);
if (!$ok1) die("Cannot execute statement.");
echo "Data inserted successfully";
sqlite_close($dbhandle);
?>
In this code example, we add a fourth row to the Friends table.
$name = "O'Neil";
We have a name with a single quote character in it.
$name_es = sqlite_escape_string($name);
To escape the string, we use the
sqlite_escape_string()
function. The returned string is O''Neill.
$stm = "INSERT INTO Friends VALUES(4,'$name_es', 'M')";
We build the SQL statement with the
$name_es
variable.
sqlite> SELECT * FROM Friends;
Id Name Sex
---------- ---------- ----------
1 Jane F
2 Thomas M
3 Franklin M
4 O'Neil M
We look with the sqlite command line tool, what we have in the table. All is OK.
5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
5/10
Retrieving data
There are multiple ways, how we can retrieve data from a table.
<?php

$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);

$query = "SELECT Name, Sex FROM Friends";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");
$row = sqlite_fetch_array($result, SQLITE_ASSOC);
print_r($row);
echo "<br>";
sqlite_rewind($result);
$row = sqlite_fetch_array($result, SQLITE_NUM);
print_r($row);
echo "<br>";
sqlite_rewind($result);
$row = sqlite_fetch_array($result, SQLITE_BOTH);
print_r($row);
echo "<br>";
sqlite_close($dbhandle);
?>
To fetch data from the table, we can use the
sqlite_fetch_array()
.
$query = "SELECT Name, Sex FROM Friends";
$result = sqlite_query($dbhandle, $query);
We build a SELECT query and execute the query with the
sqlite_query()
function. The function
returns a result set, e.g. all data from the query.
The
sqlite_fetch_array()
does two things. Moves the pointer to the next row and returns that row
from the result set. The row is is an array. We can control how the data is organized in the array, by
using three result type flags.
SQLITE_ASSOC
,
SQLITE_NUM
,
SQLITE_BOTH
. Using the first
flag we will have an associative array. Using the second one, we will have a numerical array. The
third option is the default option also. Using this flag, we will have both arrays with associative
indexes and numerical indexes. The
print_r()
function returns a human readable representation of
a variable. In our case, we can inspect what we have in an array.
$row = sqlite_fetch_array($result, SQLITE_ASSOC);
print_r($row);
echo "<br>";
Here we fetch the first row from the result set. We use the
SQLITE_ASSOC
flag. Which means, we
can access data from the array using string indexes. The indexes are column names of the table.
These are Name and Sex column names. Note that the SQL select statement did not include the id
column.
sqlite_rewind($result);
The
sqlite_rewind()
function makes the pointer point to the first row of the result set. We use this
function because we want to compare three flags on the same row. For the sake of the clarity of the
explanation.
5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
6/10
Figure: Retrieving data
In the following example, we will traverse the data using the associative indexes.
<?php

$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);

$query = "SELECT Name, Sex FROM Friends";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");
while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
echo $row['Name'] . " : " . $row['Sex'];
echo "<br>";
}
sqlite_close($dbhandle);
?>
We traverse all data in our table. More specifically, four rows in the Friends table.
while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
echo $row['Name'] . " : " . $row['Sex'];
echo "<br>";
}
We can use the
while
loop to go through all rows of the result set. The
sqlite_fetch_array()
returns FALSE, if the next position is beyond the final row and the loop stops.
echo $row['Name'] . " : " . $row['Sex'];
We get the data from the array using the string indexes. These are the column names of the Friends
table.
while ($row = sqlite_fetch_array($result, SQLITE_NUM)) {
echo $row[0] . " : " . $row[1];
echo "<br>";
}
Same loop with the
SQLITE_NUM
flag.
Columns & rows
Next, we are going to count the number of rows and columns in our result set.
<?php
$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);

$query = "SELECT * FROM Friends LIMIT 2";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");
$rows = sqlite_num_rows($result);
$cols = sqlite_num_fields($result);
5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
7/10
echo "The result set has $rows rows and
$cols columns";
sqlite_close($dbhandle);
?>
The functions get the numbers from the result set. This means, that the number of rows and
columns calculated depend on the SQL statement, that we use to obtain the data from the database
table.
$query = "SELECT * FROM Friends LIMIT 2";
Here we build the SQL query. We get all columns from the table. And we limit the number of rows
to 2.
$rows = sqlite_num_rows($result);
$cols = sqlite_num_fields($result);
The
sqlite_num_rows()
returns the number of rows in our result set.
sqlite_num_fields()
returns
the number of columns/fields from the result set.
We get this string 'The result set has 2 rows and 3 columns'.
The next PHP script will display the data from the Friends table with the names of the columns.
$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);

$query = "SELECT Name, Sex FROM Friends";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");
$rows = sqlite_num_rows($result);
$field1 = sqlite_field_name($result, 0);
$field2 = sqlite_field_name($result, 1);
echo "<table style='font-size:12;font-family:verdana'>";
echo "<thead><tr>";
echo "<th align='left'>$field1</th>";
echo "<th align='left'>$field2</th>";
echo "</tr></thead>";
for ($i = 0; $i < $rows; $i++) {
$row = sqlite_fetch_array($result, SQLITE_NUM);
echo "<tr>";
echo "<td>$row[0]</td>";
echo "<td>$row[1]</td>";
echo "</tr>";
}
echo "</table>";
sqlite_close($dbhandle);
?>
$field1 = sqlite_field_name($result, 0);
$field2 = sqlite_field_name($result, 1);
The
sqlite_field_name()
returns the name of a particular field. Our SQL query returns two
columns. The first function returns 'Name', the second 'Sex'.
echo "<thead><tr>";
echo "<th align='left'>$field1</th>";
echo "<th align='left'>$field2</th>";
echo "</tr></thead>";
We put the two column names into the html table header.
5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
8/10
for ($i = 0; $i < $rows; $i++) {
$row = sqlite_fetch_array($result, SQLITE_NUM);
echo "<tr>";
echo "<td>$row[0]</td>";
echo "<td>$row[1]</td>";
echo "</tr>";
}
We use yet another way to retrieve data from the result set. We count the number of rows. And use
the
for
cycle to go through the data.
The next PHP script will display column types of the Friends table.
<?php
$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);

$cols = sqlite_fetch_column_types('Friends', $dbhandle, SQLITE_ASSOC);
foreach ($cols as $column => $type) {
echo "Column name: $column Column type: $type";
echo "<br>";
}
sqlite_close($dbhandle);
?>
$cols = sqlite_fetch_column_types('Friends', $dbhandle, SQLITE_ASSOC);
The
sqlite_fetch_column_types()
function returns an array of column types from a particular
table. The table name is the first parameter of the function.
foreach ($cols as $column => $type) {
echo "Column name: $column Column type: $type";
echo "<br>";
}
We go through the array using the
foreach
keyword.
Listing available tables
The next example will list all available tables from the current database.
sqlite> .tables
Books Cars Friends
Using the sqlite3 tool we list the available tables.
<?php
$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);

$query = "SELECT name, sql FROM sqlite_master WHERE type='table'";
$result = sqlite_query($dbhandle, $query, SQLITE_NUM);
if (!$result) die("Cannot execute query.");
while (sqlite_has_more($result)) {
$row = sqlite_fetch_array($result);
echo "table: $row[0], sql: $row[1]";
echo "<br>";
}
sqlite_close($dbhandle);
?>
We use the
sqlite_master
table to obtain the list of tables for the database.
5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
9/10
$query = "SELECT name, sql FROM sqlite_master WHERE type='table'";
This is the query. The name column of the
sqlite_master
table gives us the table name. The sql
column gives us the SQL used to create that table.
while (sqlite_has_more($result)) {
$row = sqlite_fetch_array($result);
echo "table: $row[0], sql: $row[1]";
echo "<br>";
}
The
while
loop goes through the rows of the result set. We use a new function.
sqlite_has_more()
returns TRUE if there are more rows available from the result set, or FALSE otherwise.
Simple form example
In our last example, we will work with a simple html form. Submitting the form, we add a new friend
to the Friends table.
<html>
<head>
<title>SQLite PHP tutorial</title>
</head>
<body style="font-size:12;font-family:verdana">
<form action="add.php" method="post">
<p>
Name: <input type="text" name="name"><br>
Male: <input type="radio" value="M" name="gender"><br>
Female: <input type="radio" value="F" name="gender">
</p>
<p>
<input type="submit">
</p>
</form>
</body>
</html>
In our html form we have one text box and one radio box. We enter a name of a friend in the text
box. The radio box determines the gender. The action property of the html form points to the
add.php script. This means, that upon submitting the form the add.php script will run.
<?php
$gender = $_POST['gender'];
$name = $_POST['name'];
$name_es = sqlite_escape_string($name);
if (!empty($name)) {
$dbhandle = sqlite_open('db/test.db', 0666, $error);
if (!$dbhandle) die ($error);

$stm = "INSERT INTO Friends(Name, Sex) VALUES('$name_es', '$gender')";
$ok = sqlite_exec($dbhandle, $stm, $error);
if (!$ok) die("Error: $error");
echo "Form submitted successfully";
}
?>
This is the add.php script.
$gender = $_POST['gender'];
5/5/13
SQLite PHP tutorial
zetcode.com/db/sqlitephp/
10/10
$name = $_POST['name'];
We retrieve the data from the submitted form.
$name_es = sqlite_escape_string($name);
The data from the text box is potentionally unsafe; 'tainted'. We use the
sqlite_escape_string
. It
escapes a string for use as a query parameter. This is common practice to avoid malicious sql
injection attacks.
$stm = "INSERT INTO Friends(Name, Sex) VALUES('$name_es', '$gender')";
Here we build the SQL statement.
$ok = sqlite_exec($dbhandle, $stm, $error);
Statement is executed.
Figure: Form example
0
This was the SQLite PHP tutorial. We covered some basics of programming SQLite with PHP
language. We used procedural style of code.
Home

Top of Page
ZetCode
last modified December 4, 2009
© 2007 - 2013 Jan Bodnar
SQL Querying Courses
SQL queries for developers, DBA's. Gain competency. Get certification!
www.getcertified.co.za