4.1 SQLite Basics

It's easy to get up and running with SQLite. Its design eliminates the need for any configuration variables, such as a database server name or a database username and password. All you need is the name of a file where the data is stored:

$db = sqlite_open('/www/support/users.db');

sqlite_query($db, 'CREATE TABLE users(username VARCHAR(100),

                                      password VARCHAR(100))');

This creates a users table stored in the database file located at /www/support/users.db. When you try to open a database file that doesn't already exist, SQLite automatically creates it for you; you don't need to execute a special command to initialize a new database.

If you cannot seem to get SQLite to work, make sure you have both read and write permission for the location on the filesystem where you're trying to create the database.

SQLite has even fewer data types than PHP?everything's a string. While you can define a column as INTEGER, SQLite won't complain if you then INSERT the string PHP into that column. This feature (the SQLite manual declares this a feature, not a bug) is unusual in a database, but PHP programmers frequently use this to their advantage in their scripts, so it's not a completely crazy idea. A column's type matters only when SQLite sorts its records (what comes first: 2 or 10?) and when you enforce UNIQUEness (0 and 0.0 are different strings, but the same integer).

The table created in this example has two columns: username and password. The columns' fields are all declared as VARCHARs because they're supposed to hold text. Although it doesn't really matter what type you declare your fields, it can be easier to remember what they're supposed to hold if you give them explicit types.

4.1.1 Inserting Data

Add new rows to the database using INSERT and sqlite_db_query( ):

$username = sqlite_escape_string($username);

$password = sqlite_escape_string($password);



sqlite_query($db, "INSERT INTO users VALUES ('$username', '$password')");

You must call sqlite_escape_string( ) to avoid the usual set of problems with single quotes and other special characters. Otherwise, a password of abc'123 will cause a parser error. Don't use addslashes( ) instead of sqlite_escape_string( ), because the two functions are not equivalent.

4.1.2 Retrieving Data

To retrieve data from an SQLite database, call sqlite_query( ) with your SELECT statement and iterate through the results:

$r = sqlite_query($db, 'SELECT username FROM users');

while ($row = sqlite_fetch_array($r)) {

    // do something with $row

}

By default, sqlite_fetch_array( ) returns an array with the fields indexed as both a numeric array and an associative array. For example, if this query returned one row with a username of rasmus, the preceding code would print:

Array (

    [0] => rasmus

    [username] => rasmus

)

As you can see, sqlite_fetch_array( ) works like mysqli_fetch_array( ).

When you're using user-entered data in a WHERE clause, in addition to calling sqlite_escape_string( ), you must filter out SQL wildcard characters. The easiest way to do this is with strtr( ):

$username = sqlite_escape_string($_GET['username']);

$username = strtr($username, array('_' => '\_', '%' => '\%'));



$r = sqlite_query($db, 

                  "SELECT * FROM users WHERE username LIKE '$username'");

Use sqlite_num_rows( ) to find the total number of rows returned by your query without iterating through the results and counting them yourself:

$count = sqlite_num_rows($r);

You can call sqlite_num_rows( ) without retrieving the results from SQLite. Remember, this function takes the query result handle, like sqlite_fetch_array( ).

If speed is a concern, use sqlite_array_query( ). This retrieves all the data and puts it into an array in a single request:

$r = sqlite_array_query($db, 'SELECT * FROM users');

foreach ($r as $row) {

    // do something with $row

}

However, if you have more than 50 rows and only need sequential access to the data, use sqlite_unbuffered_query( ):

$r = sqlite_unbuffered_query($db, 'SELECT * FROM users');

while ($row = sqlite_fetch_array($r)) {

    // do something with $row

}

This is the most efficient way to print items in an XML feed or rows in an HTML table because the data flows directly from SQLite to your PHP script without any overhead tracking behind the scenes. However, you can't use it with sqlite_num_row( ) or any function that needs to know the "current" location within the result set.

When you are done with the connection, call sqlite_close( ) to clean up:

sqlite_close($db);

Technically, this is not necessary, since PHP will clean up when your script finishes. However, if you open many SQLite connections, calling sqlite_close( ) when you're finished reduces memory usage.

4.1.3 SQLite Versus MySQL

The SQLite function names are similar to the MySQL functions, but not identical. Table 4-1 provides a side-by-side comparison of the two.

Table 4-1. Comparison of major MySQL and SQLite function names
MySQL	SQLite
`mysqli_connect( )`	`sqlite_connect( )`
`mysqli_close( )`	`sqlite_close( )`
`mysqli_query( )`	`sqlite_query( )`
`mysqli_fetch_row( )`	`sqlite_fetch_array( )`
`mysqli_fetch_assoc( )`	`sqlite_fetch_array( )`
`mysqli_num_rows( )`	`sqlite_num_rows( )`
`mysqli_insert_id( )`	`sqlite_last_insert_rowid( )`
`mysqli_real_escape_string( )`	`sqlite_escape_string( )`