SQLite is a portable, self-contained database that is perfect for small projects that don't want or need the overhead of a dedicated database engine. According to their website, it's also the "most widely deployed database engine in the world". In this tutorial, we'll be using PHP to create, open, modify, and read data from a SQLite database. You might be surprised at how easy it is.
The SQLite libraries are built right in to PHP 5, so that's the version I'll be using for this tutorial. You don't have to worry about licensing issues when using SQLite, since it's in the public domain.
Creating or Opening SQLite Database
Before we can start putting anything into a database or reading anything out of it, we first need to create one. The SQLiteDatabase constructor will do this for us:
{
//create or open the database
$database = new SQLiteDatabase('myDatabase.sqlite', 0666, $error);
}
catch(Exception $e)
{
die($error);
}
The first parameter of the constructor is the database file you'd like to create or open. It doesn't need any particular extension, however I chose .sqlite so I could easily identify SQLite databases using a file explorer. The second parameter is currently ignored by the library, however it will eventually control how the database will be opened (e.g. read-only). The default value is 0666. The last parameter will contain an error message if the database creation/open fails.
According to the documentation, the constructor will return a database handle on success or a boolean (false) on fail. However, when I tested a failure condition, it threw an exception instead - so I surrounded the creation with a try-catch block. I guess that's good advice for any developer - don't always trust the documentation.
Creating New SQLite Table
All right, now that we've successfully opened a SQLite database, let's start putting some data into it. I'm going to begin by creating a new table to hold some information about movies: Title, Director, and Year.
$query = 'CREATE TABLE Movies ' .
'(Title TEXT, Director TEXT, Year INTEGER)';
if(!$database->queryExec($query, $error))
{
die($error);
}
Adding tables is pretty straight forward. Simply build the SQL query then execute it using queryExec. SQLite doesn't quite have as many data types as larger databases (like MySQL), but it has enough to get the job done. You can see all of the supported data types on SQLite's website.
If the query fails for any reason, queryExec will return false and $error will be populated with the error message. A common reason why the query will fail is that the table already exists.
Inserting Data Into SQLite Database
We've got our database, we've got our table, now it's time to push some rows into our Movies table. Inserting data will be done exactly like creating tables, except we'll use a different query.
$query =
'INSERT INTO Movies (Title, Director, Year) ' .
'VALUES ("The Dark Knight", "Christopher Nolan", 2008); ' .
'INSERT INTO Movies (Title, Director, Year) ' .
'VALUES ("Cloverfield", "Matt Reeves", 2008); ' .
'INSERT INTO Movies (Title, Director, YEAR) ' .
'VALUES ("Beverly Hills Chihuahua", "Raja Gosnell", 2008)';
if(!$database->queryExec($query, $error))
{
die($error);
}
SQLite is capable of executing multiple queries at once - separated by semi-colons. Here I added three movies to my database using a single query. Just like before, if the query fails, the function will return false and the error message will be populated.
Reading Data From SQLite Database
The last thing we're going to do today is read the data that we just put into the database back out again.
$query = "SELECT * FROM Movies";
if($result = $database->query($query, SQLITE_BOTH, $error))
{
while($row = $result->fetch())
{
print("Title: {$row['Title']} <br />" .
"Director: {$row['Director']} <br />".
"Year: {$row['Year']} <br /><br />");
}
}
else
{
die($error);
}
First off, you'll need to create the SQL SELECT statement. In this case, I just want everything in the database. Next, we call query. The first parameter is the query we just created. The second parameter tells the SQLite library how to form the output data. SQLITE_NUM will return results as arrays only accessible by index. SQLITE_ASSOC will return arrays only accessible by column name. The default value, SQLITE_BOTH, returns arrays accessible by name or index. The last parameter will be populated with an error message if one occurs.
To get the results, we simply call fetch on the result of the query call. Fetch will return an array for each record returned or false if there are no more. Since we chose SQL_BOTH, we can now reference the arrays by column name to get the values. I simply output some HTML code with the Title, Director, and Year.
That's it for creating, opening, modifying, and reading SQLite databases using PHP. Below is the entire script we created today.
{
//create or open the database
$database = new SQLiteDatabase('myDatabase.sqlite', 0666, $error);
}
catch(Exception $e)
{
die($error);
}
//add Movie table to database
$query = 'CREATE TABLE Movies ' .
'(Title TEXT, Director TEXT, Year INTEGER)';
if(!$database->queryExec($query, $error))
{
die($error);
}
//insert data into database
$query =
'INSERT INTO Movies (Title, Director, Year) ' .
'VALUES ("The Dark Knight", "Christopher Nolan", 2008); ' .
'INSERT INTO Movies (Title, Director, Year) ' .
'VALUES ("Cloverfield", "Matt Reeves", 2008); ' .
'INSERT INTO Movies (Title, Director, YEAR) ' .
'VALUES ("Beverly Hills Chihuahua", "Raja Gosnell", 2008)';
if(!$database->queryExec($query, $error))
{
die($error);
}
//read data from database
$query = "SELECT * FROM Movies";
if($result = $database->query($query, SQLITE_BOTH, $error))
{
while($row = $result->fetch())
{
print("Title: {$row['Title']} <br />" .
"Director: {$row['Director']} <br />".
"Year: {$row['Year']} <br /><br />");
}
}
else
{
die($error);
}
When this script is executed, you should see the following output:
Director: Christopher Nolan
Year: 2008
Title: Cloverfield
Director: Matt Reeves
Year: 2008
Title: Beverly Hills Chihuahua
Director: Raja Gosnell
Year: 2008
If you've got any questions or comments, feel free to leave them below.
11/12/2008 - 11:05
thank for the info.
after i downloaded the file sqlite3.exe, which folder should i place it, so that the above step will work?
11/12/2008 - 16:10
sqlite3.exe is simply a command line tool to let you access the database. It's not used for development - usually just administration and a quick way to work with the database. You don't have to download and install anything to make PHP 5 work with SQLite.
12/24/2008 - 02:38
i have created one table in database using PHP & Mysql. Now i am having problem. i want to insert some of the records from table1 to table 2 so how to retrive from html form & how to insert that records into other table
01/02/2009 - 05:55
Excellent Post, thanx for sharing the same.. Will keep on reading the post :D
Stumbled your post .. cheers
02/01/2009 - 03:23
As a newbie to php, I find I work best with cheat sheets. Since I always have my iPhone with me, I keep them there. The best one I've found so far is from these guys:
http://itunes.apple.com/WebObjects/MZStore.woa/wa/viewSoftware?id=302760278&mt=8
They also have great cheat sheets for CSS and Javascript.
Speaking of which - Anyone know if you can run PHP on the iPhone?
02/01/2009 - 13:54
Well not sure if what you mean but if your iPhone is jail broken you might be able to find an application to run php on it.
02/16/2009 - 22:53
You should change the array column name from 'Title' to 'Name' at:
{
print("Title: {$row['Title']} <br />" .
"Director: {$row['Director']} <br />".
"Year: {$row['Year']} <br /><br />");
}
}
Currently, your code will not print the movie titles in the final stage.
02/16/2009 - 22:58
Good catch! It looks like I switched it up half way through. What I really wanted was 'Title', so I changed the previous instances of 'Name' to 'Title'.
03/18/2009 - 04:34
I'm new to PHP and SQLite. As a test, I copied the script you provided and tried it, both on a localhost (on a Mac) as well as online, after cleaning it up (only changing invisible bullets for spaces).
The localhost returns: "unable to open database [dir/myDatabase.sqlite]".
And online, I get a parse error: "syntax error, unexpected '{' in [dir/filename] on line 3".
What's wrong?
03/18/2009 - 15:03
Problem solved. It was stupidly only a matter of setting the correct permissions. Thanks anyway.
04/08/2009 - 13:19
This is a clear little tutorial and I learned the basics of SQLite/PHP from this tutorial, and I thank you for the nice work.
05/13/2009 - 12:43
Thanks a lot. Well done. Clear and useful.
06/22/2009 - 18:55
Awesome. Simple to understand but very effective!