CHAPTER 6 – SQLite – Homegrown Iteration

To more clearly see how the iterator inter- nally works, you can also do it manually (without foreach doing all the magic), as is shown here in the second part of the script: $details_query = " SELECT document_id, substr(doc.body, position - 20, 100) FROM dictionary d, lookup l, document doc WHERE d.id = l.word_id AND word in ('$words') AND document_id IN ($doc_ids) AND document_id = doc.id GROUP BY document_id, doc.body "; $result = $db->unbufferedQuery($details_query, SQLITE_NUM); while ($result->valid()) { $record = $result->current(); $list[$record[0]] = $record[1]; $result->next(); } By default, the $result points to the first row when iterating, and the current() method returns the current record (indexed in the way indicated by the second parameter to unbufferedQuery()). With the next() method, you can advance to the next record in the result set. There are a few more methods that you can use; the next table shows which ones, and also lists the proce- dural functions for them. The first parameter to the procedural interface func- tions is always the result handle, and this one is not listed in Table 6.12. Table 6.12 Result Set Navigation Functions and Methods Method Name Description $result->seek() Seeks to a row in the result set. The only parameter is the sqlite_seek() zero-based record number in the set. This function can only be used for buffered result sets. $result->rewind() Rewinds the result pointer to the first record in the result sqlite_rewind() set. This function can only be used for buffered result sets. $result->next() Advances to the next record in the result set. sqlite_next() $result->prev() Moves the result pointer back to the previous record in sqlite_prev() the result set. This function can only be used for buffered result sets. $result->valid() Returns whether more record are available in the result set. sqlite_valid() sqlite_has_more() $result->hasPrev() Returns whether a previous record is available. This sqlite_has_prev() function can not be used in unbuffered queries. Now, only the last part of our search script follows--the part where we actually output the results: foreach ($rank as $record) { echo $record[0], "n====n...", $list[$record[0]], "...n---------n"; } ?> Here, we just reiterate over our first query result and use the message ID as key to the result set to display the relevant parts of the emails found.