Using the createFunction() method - PHP

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'].'<br />';

}

/* 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'].'<br />';

}

/*

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.

Therefore, be a bit more patient and read the last lines of this tutorial.