不同的函数
和MySQL API一样，PHP的SQLite API为你提供了多种方法。例如，你可以使用sqlite_fetch_object()方法来获取每行并将其做为一个对象，然后通过使用字段名作为对象属性来访问字段值。下面是一个例子：
<html> <head></head> <body> <?php // set path of database file $db = $_SERVER['DOCUMENT_ROOT']."/../library.db"; // open database file $handle = sqlite_open($db) or die("Could not open database"); // generate query string $query = "SELECT * FROM books"; // execute query $result = sqlite_query($handle, $query) or die("Error in query:
".sqlite_error_string(sqlite_last_error($handle))); // if rows exist if (sqlite_num_rows($result) > 0) { // get each row as an object // print field values as object properties echo "<table cellpadding=10 border=1>"; while($obj = sqlite_fetch_object($result)) { echo "<tr>"; echo "<td>".$obj->id."</td>"; echo "<td>".$obj->title."</td>"; echo "<td>".$obj->author."</td>"; echo "</tr>"; } echo "</table>"; } // all done // close database file sqlite_close($handle); ?> </body> </html>
另外一种选择是使用sqlite_fetch_all()函数一下子获取整个的结果集。该函数以一个数组的数组来获得完整的记录集；外部数组的每个元素都代表一个记录，而且其自身也构造为一个数组，该数组中的元素代表记录中字段。
这里给出一个例子，它可能使得这个更加清楚：
在所有之前的例子中，当处理结果集时数据库保持打开，这是因为记录是使用sqlite_fetch_array()或者sqlite_fetch_object()函数一条一条的来获取的。上述例子是独特的，因为数据库可在处理结果集数组前被关掉。这是因为整个结果集立即被获取而且存储到$data数组中，因此确实不需要在处理它的时候保持数据库打开。<html> <head></head> <body> <?php // set path of database file $db = $_SERVER['DOCUMENT_ROOT']."/../library.db"; // open database file $handle = sqlite_open($db) or die("Could not open database"); // generate query string $query = "SELECT * FROM books"; // execute query $result = sqlite_query($handle, $query) or die("Error in query:
".sqlite_error_string(sqlite_last_error($handle))); // get the complete result set as a series of nested arrays $data = sqlite_fetch_all($result); // all done // close database file sqlite_close($handle); // check the array to see if it contains at least one record if (sizeof($data) > 0) { echo "<table cellpadding=10 border=1>"; // iterate over outer array (rows) // print values for each element of inner array (columns) foreach ($data as $row) { echo "<tr>"; echo "<td>".$row[0]."</td>"; echo "<td>".$row[1]."</td>"; echo "<td>".$row[2]."</td>"; echo "</tr>"; } echo "</table>"; } ?> </body> </html>
如果你的结果集只包含一个字段，那么使用sqlite_fetch_single()函数，该函数取得一行记录第一个字段的值。PHP手册上说得好：“当你只对一列数据有兴趣时，这是最优化的获取数据的方法”。请看：
<html> <head></head> <body> <?php // set path of database file $db = $_SERVER['DOCUMENT_ROOT']."/../library.db"; // open database file $handle = sqlite_open($db) or die("Could not open database"); // generate query string // this query returns only a single record with a single field $query = "SELECT author FROM books WHERE title = 'A Study In Scarlet'"; // execute query $result = sqlite_query($handle, $query) or
die("Error in query: ".sqlite_error_string(sqlite_last_error($handle))); // if a row exists if (sqlite_num_rows($result) > 0) { // get the value of the first field of the first row echo sqlite_fetch_single($result); } // all done // close database file sqlite_close($handle); ?> </body> </html>
你甚至可以结合while()循环来使用sqlite_fetch_single()函数而对包含很多记录但只有一个字段的结果集进行重复。也请注意检查下一行是否存在的sqlite_has_more()函数的我的用法。
当然，你可以使用PHP 5中的对象符号来做同样的事情。然而，你需要知道sqlite_has_more()是一个确实不需翻译为其对象方法名称的函数；在一个面向对象的脚本中，你会需要调用$result->valid()；下面的脚本是上一个脚本的面向对象的等价物：<html> <head></head> <body> <?php // set path of database file $db = $_SERVER['DOCUMENT_ROOT']."/../library.db"; // open database file $handle = sqlite_open($db) or die("Could not open database"); // generate query string $query = "SELECT DISTINCT author FROM books"; // execute query $result = sqlite_query($handle, $query) or
die("Error in query: ".sqlite_error_string(sqlite_last_error($handle))); // if rows exist if (sqlite_num_rows($result) > 0) { echo "<table cellpadding=10 border=1>"; // check for more rows while (sqlite_has_more($result)) { // get first field from each row // print values $row = sqlite_fetch_single($result); echo "<tr>"; echo "<td>".$row."</td>"; echo "</tr>"; } echo "</table>"; } // all done // close database file sqlite_close($handle); ?> </body> </html>
<html> <head></head> <body> <?php // set path of database file $file = $_SERVER['DOCUMENT_ROOT']."/../library.db"; // create database object $db = new SQLiteDatabase($file) or die("Could not open database"); // generate query string $query = "SELECT DISTINCT author FROM books"; // execute query $result = $db->query($query) or die("Error in query"); // if rows exist if ($result->numRows() > 0) { echo "<table cellpadding=10 border=1>"; // check for more rows while ($result->valid()) { // get first field from each row // print values $row = $result->fetchSingle(); echo "<tr>"; echo "<td>".$row."</td>"; echo "</tr>"; } echo "</table>"; } // all done // destroy database object unset($db); ?> </body> </html>
