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