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. You don't have to worry about licensing issues when using SQLite, since it's in the public domain.
Intermediate
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:
try
{
//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.
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.
//add Movie table to database
$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.
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.
//insert data into database
$query =
'INSERT INTO Movies (Title, Director, Year) ' .
'VALUES ("Aiyaary", "Neeraj Pandey", 2018); ' .
'INSERT INTO Movies (Title, Director, Year) ' .
'VALUES ("The Cage Fighter", "Jeff Unay", 2018); ' .
'INSERT INTO Movies (Title, Director, YEAR) ' .
'VALUES ("Winchester", "Michael Spierig", 2018)';
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.
The last thing we're going to do today is read the data that we just put into the database back out again.
//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);
}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.
try
{
//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 ("Aiyaary", "Neeraj Pandey", 2018); ' .
'INSERT INTO Movies (Title, Director, Year) ' .
'VALUES ("The Cage Fighter", "Jeff Unay", 2018); ' .
'INSERT INTO Movies (Title, Director, YEAR) ' .
'VALUES ("Winchester", "Michael Spierig", 2018)';
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:
Title: Aiyaary
Director: Neeraj Pandey
Year: 2018Title: The Cage Fighter
Director: Jeff Unay
Year: 2018Title: Winchester
Director: Michael Spierig
Year: 2018If you've got any questions or comments, feel free to leave them below.