Using SQLite with PHP
The basics of SQLite and, indeed, of SQL itself, are simple. It’s a repetitive routine of creating tables with a column for each data element in the table. You then add rows to each table with a value for each column. You can retrieve data from a single table with a basic SELECT statement. Join two tables by using a WHERE clause to match a value in one table with a corresponding value in another table (or in the same table in the case of a self-join).
The complexity of SQL and all of its implementations including SQLite comes from combinations of these basic building blocks. In addition, the fact that we are often dealing with very large tables (millions and even billions of rows) are factors that can add complexity to the mix, but the basics are simple.
Note There is a SQLite limit of 140 terabytes per database, and that limit would tend to be reached before the number of rows becomes an issue. sqlite.org has a page on limits at www.sqlite.org/limits.html. It notes that these limits are for the most part untested in part due to SQLite developers not having access to hardware capable of managing those amounts of data. If you really are contemplating managing that amount of data, you may want to do a serious analysis of hardware and software solutions, but remember that whatever solution you end up with, SQLite can make a perfect prototyping tool.
SQLite itself is very lightweight: it imposes a minimal burden on users and their devices in many cases. That combination of a simple structure and a lightweight implementation makes SQLite a popular tool for use in web sites. Because it is a single-user implementation, it is often used for a single user’s data, but, as noted previously, it also can be used to prototype an implementation of a much larger system using a more powerful SQL implementation of any sort.
Whether you are working with a single-user project or a prototype for something larger, SQLite is a good choice, and it fits nicely with PHP. This chapter provides a brief overview (or refresher) of PHP; it then moves on to show you how the basic tables shown in Chapters 3 and 4 can be implemented on web pages with PHP.
Note This chapter focuses on the basics of PHP/SQLite integration. For an example of how they can be used on a series of web pages, see Chapter 11. In order to test the code in this chapter, you will need a basic text editor—preferably one such as BBEdit, which highlights syntax for you so you can spot minor typos easily. You also need a server to which you can upload your PHP files as well as a SQLite database. If you are maintaining a web site, you have access to such a server in order to post your HTML pages. It may or may not have PHP installed. Most servers do, but for security reasons some limit access. If you have any difficulties, check with your server administrator.
DEFINING “SINGLE USER” FOR PHP
SQLite is designed for a single user. It is often used together with PHP to implement a website. Does that mean that your PHP/SQLite web site is designed for a single user?
The answer is “yes,” but the details may surprise you. When you use PHP in a web page, there is a single user—it is the web server. Depending on your app, SQLite may or may not be a good choice (it almost always is a good choice for prototyping and testing). Static databases work well most of the time if they are not enormous. Apps that need to update a database can quickly run into trouble if you use SQLite and there are more than a handful of users (it also depends on how frequently your users are updating the data).
Putting PHP and SQLite Together: The Basics
PHP is perhaps the most widely used server-side programming language. Strictly speaking, it is a scripting language which means that it is interpreted at runtime (in practical terms, that usually means web page load time). The output from the PHP script is typically a web page in this scenario. As the PHP script is running, it can do a wide variety of things including communicating with other processes and devices, and in the SQLite context, it can communicate with a SQLite database that resides on the web server.
Verifying PHP in Your Environment
Start by verifying that you have PHP installed on your server and that you can access it. (If you already are using PHP for web sites or other files, you can skip this step.)
Here is the code for a simple PHP-based web page. It simply requests a display of the current PHP status information on the server. If it doesn’t work, check with your administrator because you won’t be able to experiment with SQLite and PHP if the PHP piece is missing. In addition to PHP, you may also have to verify that SQLite is available on the server, but in many (probably most) cases it is.
Caution Unlike SQLite, PHP is case-sensitive, so be careful with your capitalization. This is a good reason for standardizing on case-sensitive names for column names in your SQLite code even though it doesn’t matter to SQLite. When you start manipulating the data with PHP, it will matter.
This is the sample code to use to test PHP. The code in boldface is the PHP code inside the HTML code—it is delimited by <?PHP and ?>. It simply calls the phpinfo() function. Place this code in a file on your server. This file should have the php extension (it is usually lower-case, but on many servers the case won’t matter). If you name this test.php and upload it to the root of your web site (next to index.html or index.php in most cases) you can access it—including running the embedded PHP code—by going to http://mywebsite/test.php.
<!DOCTYPE html>
<html>
<head>
<title>Chapter6</title>
<meta name="generator" content="BBEdit 11.1" />
</head>
<body>
<?PHP
phpinfo();
?>
</body>
</html>
Preparing the SQLite Database
You can use almost any of the SQLite commands with PHP, but, in practice, the most common commands you use are those that manage data in a table that already exists. In some environments, it may be an empty table that is populated by your web site as it is visited by users.
Here is the code to prepare the pair of databases used in Chapter 4. They will be used again in this chapter. Several sections are worth paying particular attention to. They are shown in bold.
- In this example, sqlite3 is launched using a database called sqlitephp.sqlite. If it doesn’t exist, sqlite3 creates it. Normally, sqlite or db is used as the extension. (If you use your own extension, you may have issues in uploading it to your web site.)
- The Users table is created. Note that the Name column cannot be null—you must have a name. Data already used in Chapter 4 is filled in. (It is shown here as Table 6-1.)
Table 6-1. Users Table
rowid
Name
E-mail
1
Rex
2
Anni
3
Toby
- After creating the table and inserting the data, the data is shown with a SELECT statement. Note that the rowid is specifically requested: The typical SELECT * shows all columns except a hidden column such as rowid.
- You need to see the rowid values in order to insert them into Scores. The insert statement inserts the rowid values into Scores so that the relationship can be used. Table 6-2 shows the Scores table.
Table 6-2. Scores Table
userid
Score
1
10
2
20
3
30
- Finally, a SELECT statement shows the name and score using data from both Users and Scores that is related using rowid (users) to userid (scores). Table 6-3 shows the data.
Table 6-3. Related Users and Scores Tables
Name
Score
Rex
10
Anni
20
Toby
30
The entire sqlite3 code is shown here.
Jesses-Mac-Pro:~ jessefeiler$ sqlite3 sqlitephp.sqlite
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> create table users (Name char (128) not null, email char (128));
sqlite> insert into users (Name, email) VALUES ("Rex", "[email protected]");
sqlite> insert into users (Name, email) VALUES ("Anni", "[email protected]");
sqlite> insert into users (Name, email) VALUES ("Toby", "[email protected]");
sqlite> select rowid, name from users;
1|Rex
2|Anni
3|Toby
sqlite> create table scores (userid integer, score integer);
sqlite> insert into scores (userid, score) VALUES (1, 10);
sqlite> insert into scores (userid, score) VALUES (2, 20);
sqlite> insert into scores (userid, score) VALUES (3, 30);
sqlite> select name, score from users, scores where userid = users.rowid;
Rex|10
Anni|20
Toby|30
sqlite>
Upload your database to your web server. It should be visible on your desktop in the Finder (OS X) or Start (Windows). In this example, it is uploaded to the root of your web site.
PDO VS. OLDER PHP/SQLITE DATABASE CONNECTIONS
There have been two main ways of connecting SQLite to your database. The older one is a procedural style, and the newer one (since that long-ago year of 2005) is object-based. The PDO extension (PHP Data Object) is more robust and a better choice in every case except when you are tasked with modifying old PHP code that accesses SQLite databases in the older style.
If you are leary of using object-oriented programming, rest assured that PDO is indeed object-oriented, but it works very well with standard PHP code. You don’t have to convert all of your code to objects and classes: you can use PDO for your SQLite database and, unless you really know what you’re looking for, you may not even realize that you’re using an object.
PDO is an extension to SQLite, but the extension is built into the current versions; in fact, you need to make an effort to not have it available. It is described in this section. Although you may find sample code in various places on the Web, remember that PDO is now over ten years old, and it’s the current version. You may have difficulties opening older SQLite databases with the older style of code. (If you find sqlite_open in code you’re looking at, that’s the older—non-PDO—style.)
Connecting to Your SQLite Database
There are five steps involved and we will discuss them in detail shortly.
- Create a new PDO instance. It contains the name of the database to which you want to connect.
- Create a query. It typically is placed in a variable called $query, but you can use any name you want. The query is created by calling the prepare method on your PDO instance. If it succeeds, it returns a PDOStatement object.
- Execute the query. You do this by calling the execute method on the query (PDOStatement) that you have just created as in $query->execute(); It returns TRUE or FALSE but it does not return any values to you (yet).
- Fetch the results from the executed query. (Now you have the data.) The data is typically stored in an array variable called $results. You can use $query->fetch(); to fetch the next row of results. You can use $query->fetchAll(); to get an array of all results. Which method you choose is up to you and depends on your specific needs (and how much data you expect to retrieve). By default, the fetched results are in an associative array with elements identified by SQL column names.
- Use the results of the query that you prepared, executed, and then fetched.
Here are the details of each step.
You start with a PHP file that you want to use to access your SQLite with the five steps shown here. If you name this file phpsql.php and upload it to your web site, you can access it with http://mywebsite/phpsql.php. The full file is shown later in this chapter. Here are the relevant components in detail.
1. Create a New PDO Object
Create a new PDO object and place it in a local variable. Often, that variable is named $sqlite, but you can use any name you want. When you create the new PDO object, you must pass in the name of the SQLite file, including any paths relative to the location on the server where your PHP file is. The quoted name starts with the data source name (DSN) sqlite:. This is part of the quoted string that includes the file’s location.
Following is an example:
$sqlite = new PDO(’sqlite:sqlitephp.sqlite’);
There is another style you can use. It may or may not be clearer for you (and the people who will revise your code in the future). These are interchangeable.
$db = ’sqlite:sqlitephp.sqlphpproject’;
$sqlite = new PDO($db);
Note There are other DSN strings including mysql, pgsql (Postgres), msql, sybase, or dblib (SQLServer or Sybase), odbc, ibm (IBM DB2), informix, firebird (Firebird or Interbase).
HANDLE ERRORS PROPERLY
It’s a good practice for your users (and for yourself when debugging) to handle errors neatly. The “or die” clause does that in a very basic level. You provide a string that is displayed. In the case of PHP code generating a web page, the quoted string can include HTML formatting as in this example:
$sqlite = new PDO(’sqlite:sqlitephp.sqlite’)
or die ("<h1>Can’t open database</h1>");
For debugging purposes you may want to provide a diagnostic message when or die doesn’t fail. You can always take these messages out before moving to production but they’ll help you follow along with each step to see whether or not it succeeds.
echo "<p>Success on PDO</p>";
It’s better ultimately to use a try/catch block, but or die is more concise and is used to simplify the code in this book.
2. Create and Prepare the Query
You use the same queries that you use directly in an interactive editor like sqlite3 in any other environment. The only difference with PDO is that you create the query in a single step before you use it.
Here is an example of creating a query, storing it in a variable, and catching failures as well as logging successes (the latter only for debugging). Note that you have to use the prepare method on the instance of PDO that you have created — in this case it’s $sqlite.
$query = $sqlite->prepare ("select Name from users;")
or die ("<h1>Can’t prepare</h1>");
echo "<p>Success on prepare</p>";
3. Execute the Query
Now that you have created a query, with the prepare method of your PDO instance, call the execute method on that query to actually run it. As usual, catch errors and, for debugging, print out successes as well. Here is the code. Note in this example that the or die clause doesn’t use any specific HTML formatting. That is only to show you that it can be done either way.
$query->execute() or die ("Can’t execute");
echo "<p>Success on execute</p>";
4. Fetch the Results
The execute method returns TRUE or FALSE, but the data isn’t available until you fetch it. (If you’ve used other databases before, you’ll recognize this pattern.) You can get all of the rows at once with fetchAll, or you can get each one separately with fetch.
The fetch version gets one row at a time. By default, it returns the next row of the results set as an array indexed by column name as well as by a zero-relative column number reflecting the items in your SQLite SELECT statement. There is also an optional $cursor_offset parameter which lets you jump around in the results set.
The fetchAll version gets all of the data at once as an array. You’ll see both fetchAll and fetch in the following step.
5. Use the Results
You loop through the returned array elements in your code. In either case, you echo the result value(s) in your PHP code, combining the returned value with any HTML you want to use. For example, in the fetchAll scenario, you loop through each returned row, placing each row into a local variable (typically called $row, but you can use any name you want).
Here is the relevant fetching and looping code. (It refers to the Users table shown previously in Table 6-1.)
$result = $query->fetchAll() or die ("Can’t fetchAll");
echo "Success on fetchAll "; // for debugging
foreach ($result as $row) {
echo "<p>Name: {$row[’Name’]}</p>";
}
Putting together all of the steps in this chapter you can get a PHP file such as the following. Remember to customize it for your database name and the column name(s) you want to display. The code to customize is shown in bold.
Note Customize the HTML for the page you want to create, but also see Chapter 11, which adds more typically used HTML code that works well with SQLite.
<html>
<head>
</head>
<body>
<?php
$sqlite = new PDO(’sqlite:sqlitephp.sqlite’)
or die ("<h1>Can’t open database</h1>");
echo "<p>Success on PDO</p>";
$query = $sqlite->prepare ("select Name from users;")
or die ("<h1>Can’t prepare</h1>");
echo "<p>Success on prepare</p>";
$query->execute() or die ("Can’t execute");
echo "<p>Success on execute</p>";
$result = $query->fetchAll() or die ("Can’t fetchAll");
echo "Success on fetchAll ";
foreach ($result as $row) {
echo "<p>".$row[’Name’]."</p>";
}
?>
</body>
</html>
Summary
This chapter shows the basic steps involved in connecting to a SQLite database.
- Specify the database to use.
- Create a query to use in retrieving data.
- Run the query.
- Fetch data from the query results.
- Use the results in your code.
These steps apply to SQLite, but they also apply (sometimes with slightly different terminology and definitely different syntax) to the other languages and frameworks you use to retrieve SQLite data. Furthermore, these same basic steps apply to other databases than SQLite. This is the basic paradigm for retrieving data from a database.
The following chapters show you how to use other languages than PHP to make your SQLite database connections.