{"id":1822,"date":"2020-08-30T11:47:47","date_gmt":"2020-08-30T09:47:47","guid":{"rendered":"https:\/\/www.speich.net\/articles\/?p=1822"},"modified":"2021-08-21T15:37:22","modified_gmt":"2021-08-21T13:37:22","slug":"php-and-sqlite-fts4-how-to-process-the-matchinfo-function-2","status":"publish","type":"post","link":"https:\/\/www.speich.net\/articles\/en\/2020\/08\/30\/php-and-sqlite-fts4-how-to-process-the-matchinfo-function-2\/","title":{"rendered":"PHP and SQLite FTS4: How to process the matchinfo function part II"},"content":{"rendered":"\n

Understanding the output from the matchinfo() function<\/h2>\n\n\n\n

Part I: Reading the binary output from the matchinfo() function<\/a><\/p>\n\n\n\n

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<\/a>, 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:<\/p>\n\n\n\n

\/\/ create the example database\ntry {\n  $db = new PDO('sqlite:example.sqlite');\n} catch (PDOException $error) {\n  echo $error->getMessage();\n}\n\n\/\/ create a virtual fts4 table and populate it with example data\ntry {\n  $db->exec(\"CREATE VIRTUAL TABLE images USING fts4(imgId, title, description, species, speciesEn);\n    INSERT INTO images VALUES(1, 'Great Spotted Woodpecker', 'A great spotted woodpecker with a caterpillar in its beak.', 'Dendrocopos major', 'Great Spotted woodpecker');\n    INSERT INTO images VALUES(2, 'Woodpecker at the pond', 'A green woodpecker drinks water.', 'Picus viridis', 'Green Woodpecker');\n    INSERT INTO images VALUES(3, 'Woodpecker', 'A middle spotted woodpecker is looking for food on an oak tree.', 'Dendrocopos medius', 'Middle Spotted Woodpecker');\n    INSERT INTO images VALUES(4, 'Woodpecker', 'A lesser yellownape showing its green wings.', 'Picus chlorolophus', 'Lesser Yellownape');\");\n} catch (PDOException $error) {\n  echo $error->getMessage().'<br>';\n}<\/code><\/pre>\n\n\n\n\n\n\n\n
Next we perform a FTS4 SELECT query to search for green woodpecker<\/em> using the matchinfo()<\/code> function. We also use a helper function to format the output visually in groups of three integers (for the sake of this example). Note that the query uses an implicit AND operator. If explicit AND queries are supported (e.g. green AND woodpecker<\/em>) depends on whether enhanced query syntax<\/a> is available or not. Wrap words in double quotes to search for the whole phrase, e.g “green woodpecker”<\/em>.<\/p>\n\n\n\n
\/\/ use matchinfo when searching for green woodpecker using an implicit AND operator\n$data = $db->query(\"SELECT imgId, MATCHINFO(images) info FROM images WHERE images MATCH 'green woodpecker'\");\n\n\/\/ convert the binary output to integers and format integers in groups of three\nwhile ($row = $data->fetch(PDO::FETCH_ASSOC<\/em>)) {\n  \/\/ matchinfo returns a blob of 32-bit unsigned integers in machine byte-order\n  $arrInt32 = unpack<\/em>('L*', $row['info']);\n  echo 'row '.$row['imgId'].': '.formatOutput($arrInt32).'<br>';\n}\n\n\/**\n * Format the array of integers with spaces between according to the example in the SQLite FTS documentation\n * @param array $arrInt32\n *\/\nfunction formatOutput($arrInt32)\n{\n  $str = '' ;\n  foreach ($arrInt32 as $i => $int) {\n    $str.= ($i % 3 === 0 ? ' ' : '').$int;\n  }\n  \n  return $str;\n}<\/code><\/pre>\n\n\n\n
Our FTS4 search query will match records with id 2 and id 4. These are the only two rows of our table, where both words green<\/em> and woodpecker<\/em> occur. The converted and formatted output of the matchinfo function looks like the following:<\/p>\n\n\n\n
row 2: 25 000 000 232 000 111 000 144 133 000 133<\/code>
row 4: 25 000 000 132 000 011 000 144 033 000 033<\/code><\/p>\n\n\n\n
The first two integers are simply the total number of search words of the query (green<\/em> + woodpecker<\/em> = 2)  and the total number of columns in the table (id<\/em> + title<\/em> + description<\/em> + species<\/em> + speciesEn<\/em> = 5).<\/p>\n\n\n\n
Next, follow ten groups of three integers each. Each group represents a combination of the search words with a column (e.g. 2 * 5 = 10).<\/p>\n\n\n\n
The first integer in the group gives the number of times the word (or phrase) matched within the content of the column of the current row. The second gives the number of times the word (or phrase) matched in the column of all rows in the table. The last number gives the number of rows where the word (or phrase) matched at least once.<\/p>\n\n\n\n
For example, let’s look at the third group of integers from the output of matchinfo for row 2, e.g. 232<\/code>. It represents the combination of the word green<\/em> by the table column description<\/em>:<\/p>\n\n\n\n
\n            \n            \n                \n                \n            \n                \n                
green<\/th>\n                woodpecker<\/th>\n            <\/tr>\n            
id<\/th>\n                title<\/th>\n                description<\/th>\n                species<\/th>\n                speciesEn<\/th>\n                id<\/th>\n                title<\/th>\n                description<\/th>\n                species<\/th>\n                speciesEn<\/th>\n            <\/tr>\n            <\/thead>\n            
000<\/td>\n                000<\/td>\n                2<\/span>3<\/span>2<\/span><\/td>\n                000<\/td>\n                111<\/td>\n                000<\/td>\n                144<\/td>\n                133<\/td>\n                000<\/td>\n                133<\/td>\n            <\/tr>\n            
000<\/td>\n                000<\/td>\n                132<\/td>\n                000<\/td>\n                011<\/td>\n                000<\/td>FTS4\n                144<\/td>\n                033<\/td>\n                000<\/td>\n                033<\/td>\n            <\/tr>\n            <\/tbody>\n        <\/table>
Matrix of the search words green woodpecker<\/em> by table columns as returned by the matchinfo function. See text to understand the meaning of the color coding<\/figcaption><\/figure>\n\n\n\n
The word green<\/em> matched in the column description<\/em> twice<\/strong><\/span> within the content of the current row, a total of three<\/strong><\/span> times in all rows and it matched two<\/strong><\/span> rows overall:<\/p>\n\n\n\n
id<\/th>title<\/th>description<\/th>species<\/th>speciesEn<\/th><\/tr><\/thead>
1<\/td>Great Spotted Woodpecker<\/td>A great spotted woodpecker drinks water.<\/td>Dendrocopos major<\/td>Great Spotted Woodpecker<\/td><\/tr>
2<\/td>A Woodpecker at the pond<\/td>A green<\/span><\/span> woodpecker with a green<\/span><\/span> caterpillar in its beak.<\/td>Picus viridis<\/td>Green Woodpecker<\/td><\/tr>
3<\/td>Woodpecker<\/td>A middle spotted woodpecker is looking for food on an oak tree.<\/td>Dendrocopos medius<\/td>Middle Spotted Woodpecker<\/td><\/tr>
4<\/td>Woodpecker<\/td>A lesser yellownape showing its green<\/span> wings.<\/td>Picus chlorolophus<\/td>Lesser Yellownape<\/td><\/tr><\/tbody><\/table>
Tabular representation of our example data in the virtual table images containing four records about photos with birds. The color coding shows the different number of times the word green<\/em> matched within the column description<\/em> of the search query green woodpecker<\/em>.<\/figcaption><\/figure>\n\n\n\n
To complete the second part of this series, we will interpret another integer triplet: integers 144<\/code> of the seventh group representing woodpecker<\/em> by title<\/em> of either the first or second result of the search query. The word woodpecker<\/em> matched in the column title<\/em> once within the content of the current row, four times in the content of all rows and it matched within four rows overall.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … <\/p>\n
Continue reading “PHP and SQLite FTS4: How to process the matchinfo function part II”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[285,284,73,82],"class_list":["post-1822","post","type-post","status-publish","format-standard","hentry","category-php","tag-fts3","tag-fts4","tag-php","tag-sqlite","entry"],"_links":{"self":[{"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/posts\/1822","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/comments?post=1822"}],"version-history":[{"count":83,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/posts\/1822\/revisions"}],"predecessor-version":[{"id":2028,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/posts\/1822\/revisions\/2028"}],"wp:attachment":[{"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/media?parent=1822"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/categories?post=1822"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/tags?post=1822"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}