Automatically generate text excerpt for keyword hits?

[robkar97]

How can you create a query that fetches a sample excerpt string from a long text where the first keyword is found.

I use

"select * from Documents where Contents like '%$keyword%' or Title like '%$keyword%' order by Title asc"

For example, let's say one Document contained

Title='Something really useful'
Content='Bla, bla, bla, bla. Yadda, yadda, yadda. Do backups frequently. Bla, bla, bla. Yadda, yadda, yadda.'.

And query $keyword='backup'

Then it would be nice to have a column returning something like
Excerpt = '...da. Do backups frequently. Bl...'

Any suggestions?

[Vege]

It would be something like this
SELECT MID(kuvaus,(LOCATE('Lakana',kuvaus)-5),(LENGTH('Lakana')+10))
FROM lahjalista where kuvaus like '%Lakana%';

This will fail in some situations. One would be when matched letters dont have 5 extra in both sides. I guess you can IF compare (in mysql query) if there is the needed space in both sides but im not gonna think it any further :pimp:
I think this is easier to do in server side where this imho should be done anyway.

[robkar97]

I was thinking it would look something like that... thanks!

When you say it should be done "server side" - do you mean I might just as well fetch the entire Content and process it with PHP or something similar?

Robert

[Vege]

yes, thats what im thinking, fiquring it in a query is just too time consuming with mysql limited string functions.