Home arrow PHP arrow Page 2 - Introduction to Using SQLite with PHP 5

The basics of SQLite - PHP

If you use PHP 5 to build web-based applications that access a database, you might want to consider SQLite. A "lighter" option than MySQL, this database may still suit your purposes if you don't need all of MySQL's bells and whistles. This three-part series covers many of SQLite's most useful features.

TABLE OF CONTENTS:
  1. Introduction to Using SQLite with PHP 5
  2. The basics of SQLite
  3. Retrieving rows from a database table
  4. Examining a few more row-fetching methods
By: Alejandro Gervasio
Rating: starstarstarstarstar / 26
December 04, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

As I said right at the beginning, SQLite allows you to work with a full-featured relational database mechanism, with an important difference from MySQL. With MySQL, you would normally appeal to a separate server. With SQLite, the library uses the file system to create the corresponding sets of databases and tables. Memory-based databases are also fully supported by SQLite, but this topic will be covered in upcoming articles of the series.

To begin with, let me show you a simple example which demonstrates the complete process. This example is aimed at creating a sample database called “db.sqlite,” defining a “USERS” table, and finally fetching some rows from it using an object-oriented approach.

Here is the example in question, therefore take a look at it: 

// create new database using the OOP approach

$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($result->valid()) {

    // fetch current row

    $row=$result->current();

    print_r($row);

    // move pointer to next row

    $result->next();

}

As you’ll surely realize, the above code listing shows some new and interesting things that need to be properly explained with reference to the basic implementation of SQLite. First, notice how the “db.sqlite” database is created via the respective SQLite constructor, and then some trivial rows are inserted into the sample “USERS” table by the intuitive “query()” method.

You can see that the previous database has been defined on the file system (note the specification of the respective “db.sqlite” file). Additionally, the creation of the “USERS” table, as well as the insertion of sample rows, has been performed in a single step, since SQLite supports the transactional model.

Once the prior database table has been populated with basic data, rows are returned via the proper execution of a regular SELECT statement. Of course, it’s important to notice here how the “query()” methods return a result set object, which is used inside a “while” loop to display the corresponding database rows. These rows are shown below:

Array ( [0] => 1 [id] => 1 [1] => User1 [name] => User1 [2] =>
user1@domain.com [email] => user1@domain.com ) Array ( [0] => 2
[id] => 2 [1] => User2 [name] => User2 [2] => user2@domain.com
[email] => user2@domain.com ) Array ( [0] => 3 [id] => 3 [1] =>
User3 [name] => User3 [2] => user3@domain.com [email] =>
user3@domain.com )

Also, for this example, I used some additional methods, like “valid()”, “current()” and “next()” respectively, to traverse the mentioned result set, as I’d proceed with a regular array structure. Therefore, it’s clear to see here how the implementation of a data set iterator is only a matter of using the correct methods.

Right, at this point you hopefully learned how to create a simple database with SQLite, as well as how to define a database table and run some queries against it. That was really simple, wasn’t it? Now, it’s time to explore a few more handy methods that come embedded with this library.

To learn how these brand new methods will be defined, 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: