CHAPTER 6 – CHAPTER 6 – Fetching Data

For the two functions that return handles to the resource, there is a complementary group of functions to actually fetch the data (see Table 6.11).

Table 6.11 Fetching Functions and Methods Function Name Description sqlite_fetch_array() Returns the next row as an array. Parameters: $sqlite->fetch() · Result resource (function only) · Mode (SQLITE_ASSOC, SQLITE_NUM, or SQLITE_BOTH) sqlite_fetch_object() Returns the next row as an object with a chosen $sqlite->fetchObject() class. Parameters: · Result resource (function only) · Class name (string) · Parameters to the constructor (array) sqlite_fetch_single() Returns the first column of the next row. Its sqlite_fetch_string() parameter is the result resource (functions only). $sqlite->fetchSingle() $sqlite->fetchAll() Returns the whole result set as a two- sqlite_fetch_all() dimensional array. Parameters: · Result resource (functions only) · The mode (SQLITE_ASSOC, SQLITE_NUM, or SQLITE_BOTH) The mode parameter determines how a result will be returned. When the SQLITE_ASSOC mode is used, the returned array will have the fields indexed by field name. When the SQLITE_NUM is used, the fields will be indexed by a field number only. When SQLITE_BOTH is used, there will be a numerical index and a field name index for each field in the returned array. One of the more interesting fetch functions is $sqlite->fetchObject(), and thus, we present a small example here (which has nothing to do with our email indexing scripts): <?php $db = new SQLiteDatabase("./crm.db", 0666, &$error) or die("Failed: $error"); class Article { private $id; private $title; public $intro; private $body; private $fromDb; function save($db) { $intro = sqlite_escape_string($this->intro); $db->query( "UPDATE document SET intro = '$intro' ". "WHERE id = {$this->id}"); } } This is our class definition with only two interesting things to mention. The names of the properties are the same as the name of the fields in the data- base. This way, they will be automatically filled in with the property visibility level. As you can see, only the intro field is a public property. The second inter- esting part is the save() method that executes an update query with the new intro data. It uses the stored $id property to update the correct record. $result = $db->query( "SELECT * FROM document WHERE body LIKE '%conf%'"); $obj1 = $result->fetchObject('Article', NULL); Here, we execute our query, fetch the first record as an object of class article, and pass as only a parameter to the constructor of that class the value true (which we don't use, though). $obj1->intro = "This is a changed intro"; $obj1->save($db); ?> This last part of the code changes the intro property of the object and then calls the save() method to save the changed data into the database.