9.7.4 Advanced functions: sqlite_last_insert_rowid(), sqlite_fetch_single(), and sqlite_array_query()This is NOT the latest copy of this book; click here for the latest version.
int sqlite_last_insert_rowid ( resource db_handle)
string sqlite_fetch_single ( resource result [, int result_type [, bool decode_binary]])
array sqlite_array_query ( resource db_handle, string query [, int result_type [, bool decode_binary]])
There are three extra functions for SQLite that you are likely to find helpful. Firstly, the equivalent function of mysql_insert_id() is sqlite_last_insert_rowid(), and requires the connection resource as its only parameter. Remember that creating auto-incrementing fields in SQLite requires you to declare them as "INTEGER PRIMARY KEY" - the AUTO_INCREMENT keyword is not required. Sqlite_last_insert_rowid() will return the auto-increment ID number that was used for the last INSERT query you sent.
Secondly, the functional equivalent of PEAR::DB's getOne() is sqlite_fetch_single(). This will return the first column of the first row of the result of your query, and you pass the return value of sqlite_query() into sqlite_fetch_single() as its only parameter.
Finally, the function sqlite_array_query() is a very powerful function that returns an array of all the rows returned. Consider the following script:
<?php
$dbconn = sqlite_open('phpdb');
if ($dbconn) {
// this assumes you created the dogbreeds table using the previous script!
sqlite_query($dbconn, "INSERT INTO dogbreeds VALUES ('Poodle', 14)");
sqlite_query($dbconn, "INSERT INTO dogbreeds VALUES ('Jack Russell', 16)");
sqlite_query($dbconn, "INSERT INTO dogbreeds VALUES ('Yorkshire Terrier', 13)");
var_dump(sqlite_array_query($dbconn, "SELECT * FROM dogbreeds", SQLITE_ASSOC));
} else {
print "Connection to database failed!\n";
}
?>
The first three INSERT queries are just in there to make the data more interesting. The key line is where sqlite_array_query() is called. The function basically works as a combination of sqlite_query() and repeated calls to sqlite_fetch_array(), so it requires the database connection as parameter one, and the query to execute as parameter two. In the example, SQLITE_ASSOC is also passed in, as we would normally do when calling sqlite_fetch_array().
Here is the output that script generates, when used immediately after the script that created that dogbreeds table:
array(4) {
[0]=>
array(2) {
["Name"]=>
string(8) "Doberman"
["MaxAge"]=>
string(2) "15"
}
[1]=>
array(2) {
["Name"]=>
string(6) "Poodle"
["MaxAge"]=>
string(2) "14"
}
[2]=>
array(2) {
["Name"]=>
string(12) "Jack Russell"
["MaxAge"]=>
string(2) "16"
}
[3]=>
array(2) {
["Name"]=>
string(17) "Yorkshire Terrier"
["MaxAge"]=>
string(2) "13"
}
}
As you can see, each row in the table became an element in the returned array value, and each element was in fact an array in its own right, containing the names and values of each of the fields of that array. Using sqlite_array_query() is a very fast, very optimised way to extract lots of data from your database with just one call.
While it is undoubtedly true to say that one advantage to using PHP is that you can switch across databases without needing to learn how to access the data differently for each database, it should be clear that to get the most performance, as well as the most functionality, out of your code, you should try to take advantage of database specific features such as sqlite_array_query(). Trying to emulate sqlite_array_query() by using a traditional fetch_array() loop would have resulted in code that was a great deal slower!
|
Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
