FULLTEXT search - getting part of a line

Hi,

Not really sure if this is possible.

I am using a fulltext search to search through very large entries. Basically
an entry might contain 200,000 words, which are searched for exact matches.

What I want to do is SELECT, say, the 300 words surrounding the exact match
from the 200,000 word entry and pass it to a PHP script, rather than
passing the entire 200,000 words to the script.

Am I explaining myself clearly? In other words I want to limit the amount of
text SELECTed, so that I am not wasting memory, etc, by passing huge
strings to PHP.

Is it possible? I've done a lot of looking, but the MySQL documentation is
pretty limited on fulltext. If it is possible, can anyone give me a rough
idea of how to go about it?

Many thanks

BMA

"Roland Tanner" <roland@tannerritchie.com> wrote in message
news:0NE9h.14897$tP.10831@read2.cgocable.net...
> Hi,
>
> Not really sure if this is possible.
>
> I am using a fulltext search to search through very large entries.
> Basically
> an entry might contain 200,000 words, which are searched for exact
> matches.
>
> What I want to do is SELECT, say, the 300 words surrounding the exact
> match
> from the 200,000 word entry and pass it to a PHP script, rather than
> passing the entire 200,000 words to the script.
>
> Am I explaining myself clearly? In other words I want to limit the amount
> of
> text SELECTed, so that I am not wasting memory, etc, by passing huge
> strings to PHP.
>
> Is it possible? I've done a lot of looking, but the MySQL documentation is
> pretty limited on fulltext. If it is possible, can anyone give me a rough
> idea of how to go about it?
>
> Many thanks
>
> BMA
>
>


[in my examples, I have a simple table (Called DAYS) with 7 rows, stating
"It is Monday", etc.,]

You can establish the position (start point) of a match with
SELECT instr(day_name, 'day') from days

Span some text so that I get two characters before the start and extend to a
total of four in length
SELECT substring(day_name, instr(day_name, 'day') - 2, 4) from days

That "could" get you started, but I immediately two areas of concern:

1: In my example; if the field was "Day is Monday" then we have two matching
instances for 'day' and my example would only return information for the
first instance.

2: In my example; if the fields was "Day is Monday" then it's no possile to
obtain the characters before 'day', as there aren't any. In VB I would get
round this by padding the whole data with more than enough spaces, that are
trimmed. If I wanted 100 characters each side...

trim(substring(space(110) & day_name & space(110), instr(day_name,
'day') - 100, 200))

[the above line is written in MySQL style, but is based on my VB knowledge
and is untested in MySQL]

Hope that gets you started in the right direction....

Sean