Using the seek() and lastInsertRowid() methods - PHP

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.