Home arrow PHP arrow Page 4 - Additional Methods for Using SQLite with PHP 5

Using the createFunction() method - PHP

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.

  1. Additional Methods for Using SQLite with PHP 5
  2. Using the seek() and lastInsertRowid() methods
  3. Using the changes() and queryExec() methods
  4. Using the createFunction() method
  5. Creating databases in server memory
By: Alejandro Gervasio
Rating: starstarstarstarstar / 2
December 18, 2006

print this article



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");


// 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.

>>> More PHP Articles          >>> More By Alejandro Gervasio

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: