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. The 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 is 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 we edited the file while Apache was running, we have to 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

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. We 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 our system.

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);
?>

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 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.");

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.

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.

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 Friendstable.

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);

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.

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.

$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 theadd.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'];
$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);

The SQL statement is executed.

The image is a screenshot from the application running on localhost.

This was the SQLite PHP tutorial. We covered some basics of programming SQLite with PHP language. We used procedural style of code.

PHP Tutorial

Thursday, April 9, 2015