Simple databases for websites using SQLite with PHP5 and PDO

by David Halls


Screenshot of database display

Click image to see working database display

In a previous article, I described how SQLite could be used in HTML programs in VBScript with an ODBC driver. SQLite support is now part of PHP so I was keen to try SQLite with a simple database on one of my websites on a Linux server.

You will find on the SQLite website (www.sqlite.org) a number of advantages to using SQLite. Here are some points that I thought were useful:-

Setting up the database

There are several programs to help with setting up a SQLite database. Two freeware programs for Windows that I have tried and found very useful are SQLite Administrator and SQLite2007 Pro Enterprise Manager. Both allow conversion of a database from MS Access to SQLite.

If your database is for display of data only and you are the only one who is updating the data, why not do all of your updating offline? The amendments can be made with one of these excellent programs and the amended file can be uploaded as you would any other file. It saves the time in developing a web interface for amendment.

I see SQLite with PHP as particularly suited for simple databases, e.g. contact details of members or staff, where the expense of updating to a more expensive webhosting package with MySQL or SQL Server is not justifiable. This may be a little unfair to SQLite which can probably handle complicated databases very well, but I see its application to this kind of small database as a niche for which it is suited. The example shown here is not of member details as I did not want to use personal details. This article uses a database of classical music on a CD collection. All fields are simple text fields.

DATABASE FIELDS

Field NameTypeOther Properties
IDINTEGERPRIMARY KEY
ComposerTEXT 
Work TEXT 
Orchestra TEXT 
Conductor TEXT 
Soloist1 TEXT 
Instrument1 TEXT 
Soloist2 TEXT 
Instrument2 TEXT 
Soloist3 TEXT 
Instrument3 TEXT 
Recorded INTEGER 

PHP

I admire the ability of PHP to provide very concise code and I like the many useful functions. However, I struggle with the programming, especially with all that punctuation. It is the brackets in particular that give me trouble - ordinary brackets ( ), curly brackets { } and square brackets [ ]. You need good eyesight to spot the difference between an ordinary bracket ( ) and a curly bracket { }. As PHP has developed, even more punctuation has been introduced and we now have the double colon ::

There seem to be three main approaches to using PHP5 with SQLite databases:

  1. Using a set of specialised functions for interaction. These follow the pattern so successfully used with MySQL.

  2. An object-orientated approach which begins with making an object of the database:

    $db = new SQLiteDatabase (cds.db3);
    
    The brackets contain the path to the database. In this case, the database "cds.db3" 
    is in the same directory as the program.
    

    The database object then has a series of methods and properties which are based on the functions available in the first approach.

  3. Using PDO. PDO stands for PHP Data Objects and is a unified interface for accessing databases in PHP. It requires that a PDO driver for a particular database be installed and can be seen as a PHP equivalent of the ODBC system in Windows.

My webhosting company does not have a full implementation of PHP5 and so approaches 1 and 2 failed. It does have implementation of PDO and its SQLite Driver, so it was necessary to get familiar with this method.

Programming with PDO

We start by making an object of the database using the code:

$db = new PDO('sqlite:cds.db3');

This is for a database "cds.db3" is in the same directory as the program. If it is not, use the relative path.

We now need to get the recordset from the table, "discs". As this is not a big database we will get all records in one go, using the method "query" on the object $db.

$result = $db->query('SELECT * FROM discs');

Beyond this, I initially ran into difficulties. I was looking for methods and properties that are a part of approaches 1 and 2 discussed above; they are not there and you are left to work things out for yourself. I particularly wanted a generic display that would retrieve the field names, but had problems finding a method that would give these. After rereading the documentation on the www.php.net website, the penny dropped.

PDO has a number of FETCH methods and, of these, two seemed particularly useful:

$row = $result->fetch(PDO::FETCH_ASSOC);
$rowarray = $result->fetchall(PDO::FETCH_ASSOC);

The first will return an array of the next row in the recordset. Note the double colon. The attribute FETCH_ASSOC means that the array returned is indexed by field name. Other attributes are available to retrieve other kinds of arrays from the recordset, but this is particularly suited as it enables the field names to be recalled.

The second method ('fetchall') retrieves all rows and is an array of arrays. We will use this as our database is relatively small and then we can use array procedures to get the data.

Populating the dropdown box

We will use a dropdown menu like this to select records:-

The menu will be populated by key information from the database, in this case the composer and the work. Frequently, you find a button to the right of the menu to activate the selection. However, it is simpler if you can just click on the option to activate the process of finding the record. This we can do if we use the event onChange which activates the submission of the form using Javscript and recalls the same file.

<div class = "controlbox">

<form action = "<?php echo $_SERVER['PHP_SELF']; ?>" method = "get">
<select name = "rowno" onchange = "javascript:document.forms[0].submit();">
  <option>--Select a work--</option>

<?php
try
{$db = new PDO('sqlite:cds.db3');}
catch( PDOException $e )
{die( $e->getMessage() );}

$sql = 'SELECT * FROM discs';
$result = $db->query($sql);
$rowarray = $result->fetchall(PDO::FETCH_ASSOC);
$rowno = 0;

foreach($rowarray as $row)
{
echo "<option value = $rowno >$row[Composer] $row[Work]</option>";
$rowno++;
}
?>
</select>
</form>
</div>

To populate the dropdown menu, we get the complete set of results, as shown above, in the array of arrays $rowarray. This array is then parsed as its constituent row arrays ($row) using a foreach loop. A counter, $rowno, is used to assign a value to the options in the menu. The values for the fields, Composer and Work, are displayed in the dropdown menu.

Displaying the record

The record will be displayed as a table of field names in the first column and the values in the second column. This is a generic routine which does not require prior knowledge of the field names. The headings are not generic and display the composer and the work.

<?php

if ($_GET[rowno])
{
$rowno = $_GET[rowno];}
else
{$rowno = 0;}

$row = $rowarray[$rowno];
echo "<h2>$row[Composer]</h2>";
echo "<h3>$row[Work]</h3>";
echo "<table id='otable'>";
foreach($row as $key=>$val)
{echo "<tr>";
 echo("<td class = 'field'>$key</td><td>$val</td>
 echo "</tr>";
}

echo "</table>";
?>

First, it should be established whether a submission has taken place. If there has, there will be a value for $_GET[rowno] and we give its value to $rowno. If there was no submission, the value of $rowno is set to 0 in order to display the first record in the database. The chosen record in the recordset is retrieved as $rowarray[$rowno]. The headings for Composer and Work are displayed and then the table is generated using a foreach loop to display key/value pairs retrieved from the record, $row.

Conclusion

You can see this database working here. If you are intending using this approach on a website, check with the information from your webhosting company on the requirements for PHP5. My former web host required the file to end with the suffix ".php5" for PHP5 to be invoked. This will not work with the current host;".php" suffices.

This article has shown how it is possible to use SQLite for a simple web database and to control display and retrieval of records using PHP5 with PDO. The database can simply be uploaded as a normal file and is compact (in this case, 6.1 kB with the display program of only 2.6 kB). The program shown offers record retrieval through a dropdown menu and a display of the record as a table. The basics shown here can be expanded to provide customised solutions for other purposes.

References