CHAPTER 6 – SQLite – Iterators

There is another way to navigate through a result set, and that is with an iterator. Using an iterator to iterate over the result set does not involve calling any functions, so it is therefore a bit faster than when you would use one of the fetch functions. In this example, we present the search.php script to find an email matching certain words:  <?php $db = new SQLiteDatabase("./crm.db", 0666, &$error) or die("Failed: $error"); if ($argc < 2) { echo "Usage:ntphp search.php <search words>nn"; return; } function escape_word(&$value) { $value = sqlite_escape_string($value); } $search_words = array_splice($argv, 1); array_walk($search_words, 'escape_word'); $words = implode("', '", $search_words);; The parameters that are passed to the script are the search words, which we, of course, need to escape with the sqlite_escape_string() function. In the previous example, we use the array_walk() function to iterate over the array and escape the words. After they are escaped, we construct a list of them to use in the queries with the implode() function.

$search_query = " SELECT document_id, COUNT(*) AS cnt FROM dictionary d, lookup l WHERE d.id = l.word_id AND word IN ('$words') GROUP BY document_id ORDER BY cnt DESC LIMIT 10 "; $doc_ids = array(); $rank = $db->query($search_query, SQLITE_NUM); foreach ($rank as $key => $row) { $doc_ids[$key] = $row[0]; } $doc_ids = implode(", ", $doc_ids); ... Next, we execute the query with the query() method that returns a result handle. With the foreach loop, we iterate over the result just as we would iter- ate over an array, except that we don't actually create an array first. The itera- tor tied to the SQLite buffered query object fetches the data for us row by row. In the most ideal case, we would use an unbuffered query here, but we can't do that because we need to reuse this result set; reusing result sets is not possible with an unbuffered query because the data is not buffered, of course.