SQLite Component

Create an SQLite FTS4 virtual table for fulltext searching.

@param $table

The database table name.

@param $fields

An array($field, ...) of names to create, or just a string (eg. 'search') if there is only one field name.

@param $tokenize

Either 'simple', or 'porter' (the default).

@return

Either false if the table has already been created, or true if the table has been created anew.

@example

if ($db->created) {

    $db->fts->create('results', 'search');

    // You can insert, update, and query an FTS table the same as any other.
    if ($stmt = $db->insert('results', array('docid', 'search'))) {
        $db->insert($stmt, array(100, 'Fisherman never die, they just get reel tired.'));
        $db->insert($stmt, array(101, 'If wishes were fishes, we\'d have a fish fry.'));
        $db->insert($stmt, array(102, 'Women want me, fish fear me.'));
        $db->insert($stmt, array(103, 'Good things come to those who bait.'));
        $db->insert($stmt, array(104, 'A reel expert can tackle anything.'));
    }

}

Get the total number of search results.

@param $table

The database table name.

@param $search

The search term(s) to 'MATCH'.

@param $where

An additional string of restrictions you would like to place. If you don't include 'WHERE' we will add it for you. If you are combining tables to deliver results then put your 'INNER JOIN ... WHERE' clause here, and prefix the search $table and fields with 's.' eg. INNER JOIN my_table AS my ON s.docid = my.id WHERE my.field = ...

@return

The total count.

@example

echo $db->fts->count('results', 'fish'); // 2

Queries an FTS $table for the relevant $search word(s) found within.

@param $table

The database table name.

@param $search

The search term(s) to 'MATCH'.

@param $limit

If you are not paginating results and only want the top whatever, then this is an integer. Otherwise it is an SQL ' LIMIT offset, length' clause.

@param $where

An additional string of restrictions you would like to place. If you don't include 'WHERE' we will add it for you. If you are combining tables to deliver results then put your 'INNER JOIN ... WHERE' clause here, and prefix the search $table and fields with 's.' eg. INNER JOIN my_table AS my ON s.docid = my.id WHERE my.field = ...

@param $fields

An array('s.field', ...) of additional fields you would like to include in the search results. Remember to specify the table prefixes if needed.

@param $weights

An array of importance that you would like to place on the $table fields searched in whatever order you placed them originally. The default weights are 1 for each field, meaning they are all of equal importance. If you want to make one field more relevant than another, then make this an array($weight, ...) of importance to place on each corresponding $table field. Even if you place an importance of 0 on a field it will still be included among the search results, it will just have a lower rank (possibly 0). All of this assumes you have more than one field in your $table, otherwise this will make no difference whatsoever.

@return

An associative array of results.

@example

var_export($db->fts->search('results', 'fish'));
array(
    array(
        'docid' => 101,
        'snippet' => "If wishes were <b>fishes</b>, we'd have a <b>fish</b> fry.",
        'offsets' => '0 0 15 6 0 0 35 4',
        'rank' => 1.333,
    ),
    array(
        'docid' => 102,
        'snippet' => 'Women want me, <b>fish</b> fear me.',
        'offsets' => '0 0 15 4',
        'rank' => .666,
    ),
);

Get the words that made your $search relevant for $docid.

@param $table

The database table name.

@param $search

The search term(s) to 'MATCH'.

@param $docid

The $table row's docid.

@return

The unique words found which made the $search relevant.

@example

echo implode(', ', $db->fts->words('results', 'fish', 101)); // fishes, fish

Sorts through the $row['offsets'] integers, and retrieves the words they reference.

@param $row

An associative array of each $fields value, including an 'offsets' key.

@param $fields

An array of field names in the same order as they are found in the database search table.

@return

The words that made this row relevant.

@example

print_r($db->fts->offset(array(
    'search' => "If wishes were fishes, we'd have a fish fry.",
    'offsets' => '0 0 15 6 0 0 35 4',
), array('search'))); // array('fishes', 'fish');

@link

• https://www.sqlite.org/fts3.html#offsets