Subject Re: [firebird-php] full text search
Author Milan Babuskov
nn33dl3 wrote:
> Does anybody knows how to do a full text search on a firebird database
> are there some open source scripts?

You could probably do it with triggers, but it takes some work. Example:

create table a
id integer not null,
textfield varchar(50)

and you want to do a fulltext search on "textfield".

Now, create a table:

create table a_fts
id integer not null references a(id) on delete cascade,
word varchar(50)

And create a trigger that will insert/update/delete from that table when table "a" is changing. For

insert into a values (1, 'This is complex');

Should also do:

insert into a_fts values (1, 'this');
insert into a_fts values (1, 'is');
insert into a_fts values (1, 'complex');

You may need to use some UDF or stored procedure for that trigger, I leave it for your homework ;)

Anyway, that's all the hard work. Later you just query like:

select id from a_fts where word = 'complex';


/* OR type of query */
select id, count(*)
from a_fts
where word in ('complex', 'query')
group by id
order by 2 desc


/* AND type of query */
select id
from a_fts a1
join a_fts a2 on = and a2.word = 'query'
where a1.word = 'complex';

You also need to put indices on tables to make it work fast.

Milan Babuskov