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

Using the seek() and lastInsertRowid() methods - 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.

TABLE OF CONTENTS:
  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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

}

// move pointer to second row

$result->seek(1);

while($row=$result->fetch(SQLITE_ASSOC)){

    // display row

    echo $row['id'].' '.$row['name'].' '.$row['email'].'<br />';

}

/*

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.

To see how these new methods will be implemented, click on the link below and keep reading.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- 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: