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
CREATE
d 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.
Get Upgrading to PHP 5 now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.