Understanding the output from the matchinfo() function

Part I: Reading the binary output from the matchinfo() function

To analyze what the output of the matchinfo() function means, we’ll use a different example dataset than the one from the SQLite FTS4 documentation. Since I’m a bird photographer, let’s create a virtual FTS4 table holding information about photos of birds. It consists of the five columns id, title, description, species and common name (note: id is the image id and not the FTS4 rowid). Then we fill the table with four records making up our example data:

// create the example database
try {
  $db = new PDO('sqlite:example.sqlite');
} catch (PDOException $error) {
  echo $error->getMessage();
}

// create a virtual fts4 table and populate it with example data
try {
  $db->exec("CREATE VIRTUAL TABLE images USING fts4(imgId, title, description, species, speciesEn);
    INSERT INTO images VALUES(1, 'Great Spotted Woodpecker', 'A great spotted woodpecker with a caterpillar in its beak.', 'Dendrocopos major', 'Great Spotted woodpecker');
    INSERT INTO images VALUES(2, 'Woodpecker at the pond', 'A green woodpecker drinks water.', 'Picus viridis', 'Green Woodpecker');
    INSERT INTO images VALUES(3, 'Woodpecker', 'A middle spotted woodpecker is looking for food on an oak tree.', 'Dendrocopos medius', 'Middle Spotted Woodpecker');
    INSERT INTO images VALUES(4, 'Woodpecker', 'A lesser yellownape showing its green wings.', 'Picus chlorolophus', 'Lesser Yellownape');");
} catch (PDOException $error) {
  echo $error->getMessage().'<br>';
}