PHP

Retrieving Results of a Query to SQLite

$result = sqlite_query($db, 'SELECT * FROM quotes');
sqlite_fetch_array($result);

The function sqlite_exec() from the previous coding is very quick performing; however, it is not possible to access return values from the SQL statement sent with it. For this, sqlite_query() must be used. This function returns a handle of the resultset of the query. The following functions can then be used to iterate over the resultset:

  • sqlite_fetch_arrray() returns the current row in the resultset as an associative array (field names become keys) and moves farther to the next row.

  • sqlite_fetch_object() returns the current row in the resultset as an object (field names become properties) and moves farther to the next row.

  • sqlite_fetch_all() returns the complete resultset as an array of associative arrays.

Retrieving Data from SQLite
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year<
   /th></tr>
<?php
  if ($db = @sqlite_open('quotes.db', 0666, $error))
    {
    $result = sqlite_query($db, 'SELECT * FROM
      quotes');
    while ($row = sqlite_fetch_array($result)) {
      printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></
   tr>',
        htmlspecialchars($row['id']),
        htmlspecialchars($row['quote']),
        htmlspecialchars($row['author']),
        htmlspecialchars($row['year'])
      );
    }
    sqlite_close($db);
  } else {
    printf('<tr><td colspan="4">Connection failed:
      %s</td></tr>',
      htmlspecialchars($error));
  }
?>
</table>

The preceding listing shows how to access all data within the resultset using sqlite_fetch_array(). A while loop calls this function as long as it returns something other than false (which means that there is no data left).

Using sqlite_fetch_all() reads the whole resultset into memory at once. So, if you do not have much data, this is the best-performing method. If you have more data, an iterative approach using sqlite_fetch_array() and sqlite_fetch_object() might be better.

by BrainBellupdated
Advertisement: