Welcome to the concluding part of the series “Using SQLite with PHP 5.” As you’ll possibly know, PHP 5 comes equipped with a fully-featured RDBMS called SQLite that definitely can make your life as a PHP developer much easier. It's particularly helpful if you want to get rid of MySQL at least for a time while maintaining the data layer of your application completely isolated from the business logic.
Over the course of the second tutorial, I explained several methods that come packaged with the SQLite library and perform different tasks, such as counting the number of rows and fields contained within a specific result set, fetching one row at a time, working with unbuffered queries, and so forth.
If you’ve read the two previous articles that belong to this series (as I suppose you have), then I’m sure that you realize the great capabilities offered by SQLite. It's especially useful in those cases where you need to work with a decent RDBMS but don't need to appeal directly to the features offered by the popular MySQL server.
Well, at this point you may be thinking that you’ve learned everything about the cool methods included with SQLite, since the material that I provided you during the previous articles has certainly been abundant.
However, if you think that way, I’m afraid that you’re wrong. SQLite has some other methods that can be useful for performing all sort of clever tasks, including the definition of custom functions, finding the IDs of inserted rows, the creation of memory-based databases, and so on.
As you can see, the list of additional features offered by SQLite is really impressive. Therefore, in this last article of the series, I’ll be taking an in-depth look at them. This will complete our analysis of this excellent RDBMS integrated with PHP 5.
Are you ready to go over the last miles of this learning journey? Fine, let’s get started!
Using the seek() and lastInsertRowid() methods
Moving back and forward across a specified result set is a task that can be performed with minor difficulties when using SQLite, since the library has been equipped with the intuitive “seek()” method, which does exactly this.
With reference to this method in particular, below I developed a simple example that shows how it works. Look at the corresponding code listing, please:
// example using the 'seek()' method
// create new database using the OOP approximation
$db=new SQLiteDatabase("db.sqlite");
// create table 'USERS' and insert sample data
$db->query("BEGIN;
CREATE TABLE users (id INTEGER(4) UNSIGNED PRIMARY KEY,
name CHAR(255), email CHAR(255));
INSERT INTO users (id,name,email) VALUES
(NULL,'User1','user1@domain.com');
INSERT INTO users (id,name,email) VALUES
(NULL,'User2','user2@domain.com');
INSERT INTO users (id,name,email) VALUES
(NULL,'User3','user3@domain.com');
COMMIT;");
// fetch rows from the 'USERS' database table
$result=$db->query("SELECT * FROM users");
// loop over rows of database table
while($row=$result->fetch(SQLITE_ASSOC)){
// display row
echo $row['id'].' '.$row['name'].' '.$row['email'].'
';
}
// move pointer to second row
$result->seek(1);
while($row=$result->fetch(SQLITE_ASSOC)){
// display row
echo $row['id'].' '.$row['name'].' '.$row['email'].'
';
}
/*
displays the following
2 User2 user2@domain.com
3 User3 user3@domain.com
*/
As you can see, the snippet listed above shows a simple yet effective implementation of the referenced “seek()” method. First, the script obtains a result set via the respective “query()” method, and then it moves the pointer to the first row. Finally, after doing this, the remaining records are displayed on the browser. Quite intuitive, right?
Okay, now that you hopefully understand how the previous methods do their thing, take a look at the following one, which determines the ID of the last inserted row. One possible usage of this method is demonstrated by the example below:
// example using the 'lastInsertRowid()' method
// create new database using the OOP approximation
$db=new SQLiteDatabase("db.sqlite");
// insert new row into 'USERS' database table
$db->query("INSERT INTO users (id,name,email) VALUES
(NULL,'User4','user1@domain.com')");
echo 'ID of last inserted row is '.$db->lastInsertRowid();
/*
// displays the following
ID of last inserted row is 4
*/
As shown above, the “lastInsertRowid()” method is extremely useful for doing what it clearly suggests: finding the ID of the last inserted row. Indeed, if you’re anything like me and work intensively with DML statements, you’ll find the previous method really handy.
So far, the couple of methods that I covered are pretty straightforward, since they're very similar to some of the MySQL-related PHP functions that you’ve used probably hundreds of times.
However, there’s still more valuable material to review here concerning SQLite's capabilities. Therefore, in the next few lines I’ll explain two more methods. The first one can be used for running queries instead of using the previous “query()” method. The second one is handy for counting the number of rows affected after performing a DML operation.
Using the changes() and queryExec() methods
As I mentioned at the end of the previous section, I’m going to show you a couple of additional methods bundled with the SQLite library that can be valuable. They work well for those situations where you want to use an alternative way to run queries, and for determining the number of rows affected after performing a DML statement.
The first method that I’ll teach you is “queryExec().” It consists of a simple replacement of the “query()” method that you learned before. Here’s how to use it:
// example using the 'queryExec()' method
// create new database using the OOP approximation
$db=new SQLiteDatabase("db.sqlite");
$query="INSERT INTO users (id,name,email) VALUES(NULL,'John
Doe','john@domain.com')";
if(!$db->queryExec($query)){
trigger_error('Error performing
query'.$query,E_USER_ERROR);
}
If you take some time and examine the above short example, you’ll understand why I said the “queryExec()” method can be used as an alternative to the previously reviewed “query().” In this case, the example speaks for itself, therefore I suggest you pay attention to the following code sample. It is much more useful, since it illustrates a basic application of the brand new “changes()” method.
The script listed below shows precisely how you can use this method to calculate the number of affected row after running a DML statement:
// example using the 'changes()' method
// create new database using the OOP approximation
$db=new SQLiteDatabase("db.sqlite");
// insert new row into 'USERS' database table
$db->query("INSERT INTO users (id,name,email) VALUES
(NULL,'User4','user1@domain.com')");
echo 'Number of rows modified after the insertion '.$db->changes();
/*
// displays the following
Number of rows modified after the insertion 1
As you can see in the above example, the “changes()” method can be really helpful if you want to know how many rows were affected after inserting, updating or deleting records of a particular database. Of course, this method is closely similar to the PHP “mysql_affected_rows()” function, therefore you shouldn’t have too many problems understanding how it works.
All right, at this stage I believe that you’ve been provided with a neat set of SQLite methods which can be used for tackling different tasks. However, we’ve not come to the end of the tutorial yet, since there are a few more methods that remain uncovered.
Speaking of that, in the following section, I’ll teach you how to use iterators to traverse different result sets, and how to define custom functions with SQLite as well.
Using the create Function() method
As I explained in the previous section, the SQL library has been provided with the ability to work with iterators to traverse a specific result set by using only a typical “foreach” language construct. Certainly, you’ll have to agree with me that this feature is really handy, since there’s no need to write custom code for iterating over data sets.
To learn more about the use of iterators with SQLite, please have a look at the following example, which shows a simple implementation of this neat concept:
// example of 'SQLite' iterators
// create new database using the OOP approximation
$db=new SQLiteDatabase("db.sqlite");
// fetch rows from the 'USERS' database table
$result=$db->unbufferedQuery("SELECT * FROM users");
// use 'foreach' loop to traverse result set
foreach($result as $row){
echo 'ID: '.$row['id'].' Name :'.$row['name'].' Email :'.$row
['email'].'
';
}
/* displays the following:
ID: 1 Name :User1 Email :user1@domain.com
ID: 2 Name :User2 Email :user2@domain.com
ID: 3 Name :User3 Email :user3@domain.com
*/
As you can see, the above code snippet demonstrates how a specified result set can be traversed by using a simple iterator. In this case, the script first obtains the mentioned data set via the “unbufferedQuery()” method you learned before and finally uses a common “foreach” loop to traverse the data structure in question. Simple and efficient, isn’t it?
Now that you hopefully grasped the concept that stands behind using iterators with SQLite, it’s time to look at another useful method which I’m certain you’ll find very handy. In this case I’m talking about the “createFunction()” method. As the name clearly suggests, it's really helpful for creating user-defined functions that can be tied to a particular result set or as part of a WHERE clause.
With reference to this excellent capability, below I coded a basic example of how to use a custom function with SQLite. Take a look a the corresponding code sample:
// example of custom functions
// create custom function
function getRandomID($id){
return rand($id,5);
}
// create new database using the OOP approximation
$db=new SQLiteDatabase("db.sqlite");
$db->createFunction('getRandomID','getRandomID',1);
// fetch rows from the 'USERS' database table
$result=$db->query("SELECT * FROM users WHERE id==getRandomId(2)");
foreach($result as $row){
echo 'ID: '.$row['id'].' Name :'.$row['name'].'
Email :'.$row['email'].'
';
}
/*
displays the following
ID: 2 Name :User2 Email :user2@domain.com
*/
As you’ll realize, the above example begins creating the custom “getRandom()” function, which obviously returns a random integer between 1 and 5. After this function has been created, it’s used as part of the corresponding SELECT statement to fetch a random row from the respective database table.
Of course, this is only a basic application of using custom functions with SQLite, which means that you can experiment by defining your own, certainly more useful functions.
So far, I covered the most important methods that come bundled with the SQLite library. But I must say I’m not finished yet, since I’d like to teach you another cool feature included with this tight RDBMS.
Remember that in the beginning of this series I mentioned the capability offered by SQLite for working with memory-based databases? I hope you do, because in the last section of this article, I’ll show you how to implement this characteristic in your own database-driven PHP applications.
Creating databases in server memory
The last SQLite feature that I plan to cover here concerns specifically the creation of databases in server memory, instead of using the conventional file system. As you can imagine, this type of database can be used (among other situations) in those cases where you need to have at your disposal a fully-structured database relational system, but your data will be rather temporary, at least during the execution of your application.
That being said, defining a memory-based database with SQLite is reduced to code something as simple as this:
// example using memory-based database
// create a new memory-based database
$db = new SQLiteDatabase(":memory:");
// create table 'USERS' and insert some data
$db->query("BEGIN;
CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR
(255),email VARCHAR(255));
INSERT INTO users (id,name,email) VALUES
(NULL,'User1','user1@domain.com');
INSERT INTO users (id,name,email) VALUES
(NULL,'User2','user2@domain.com');
COMMIT;");
// display number of affected rows after the insertion
echo $db->changes().' rows affected by the insertion
';
// display ID of last inserted row
echo "ID of last inserted row is: ".$db->lastInsertRowid();
/*
displays the following
2 rows affected by the insertion
ID of last inserted row is: 2
*/
As shown above, a new database has been created in memory by simply specifying the “:memory” argument for the corresponding SQLite constructor. After this process has been performed, I defined a “USERS” table, in addition to inserting some trivial data, and finally displayed the ID that corresponds to the last inserted row.
As I always suggest, try creating different memory-based databases and watch what happens in each case. The process is truly educational.
Final thoughts
We’ve come to the end of this series. In these three consecutive tutorials, I took an in-depth look at the most relevant methods that come with the SQLite RDBMS, which has been included with PHP 5.
As you learned here, if your database-driven application doesn’t require all the features offered by MySQL, or another RDBMS, then this tight yet powerful library is worth considering.
