4.2 Alternate SQLite Result Types

SQLite has many different functions for retrieving data. The ones you've already seen are not the only ones at your disposal, and you can control whether sqlite_fetch_array( ) returns numeric arrays, associative arrays, or both.

By default, when sqlite_fetch_array( ) returns data, it provides you with an array containing numeric and associative keys. This is a good thing, because it lets you refer to a column either by its position in the SELECT or by its name:

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

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

    print "user: $row[username]\n";  // this line and...

    print "user: $row[0]\n";         // this line are equivalent


This is also a bad thing because it can catch you unawares. For example:

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

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

    foreach ($row as $column) {

        print "$column\n";           // print each retrieved column



This actually displays every column twice! First it prints the value stored in $row[0], and then it prints the same value referenced by its column name. If you have a generalized table-printing routine where you don't know the number of fields in advance, you might fall prey to this bug.

Additionally, if you retrieve a large dataset from SQLite, such as an entire web page or an image, then each result takes up twice as much memory because there are two copies stashed in the array.

Therefore, SQLite query functions take an optional parameter that controls the results. Pass SQLITE_ASSOC for only column names, SQLITE_NUM for only column positions, and SQLITE_BOTH for the combination. These arguments are constants, not strings, so you do not place them in quotation marks. For example:

// numeric

$row = sqlite_fetch_array($r, SQLITE_NUM);

// associative

$row = sqlite_fetch_array($r, SQLITE_ASSOC);

// both (the default value)

$row = sqlite_fetch_array($r, SQLITE_BOTH);

SQLite returns column names in the same mixed case as you CREATEd them. This is not true of all databases. Some like to use all uppercase letters; others turn everything into lowercase. When porting applications from one of these databases to SQLite, use the sqlite.assoc_case configuration parameter to maintain compatibility without rewriting your code. The default value is 0, for mixed case; changing it to 1 turns the strings in your associative arrays to uppercase, whereas 2 sets them to lowercase. Modifying the column names slows down SQLite slightly, but PHP's strtolower( ) is significantly worse in this regard.