Counting matches

Hi,

This is really more of a MySQL question than PHP per se, but the only
MySQL group my newserver carries seems to be pretty quiet, so I
thought I'd ask here.

What I am trying to do is determine the number of terms which match a
field in the database where an initial string of multiple words has
been broken up into single terms and the SQL statement is built by
looping through each term separately, something like this:

$original_string = "this is a test string"

// code to remove stop words/common terms leaves

$trimmed = "this test string"

// this is then exploded at the space into an array of its constituent
words. This array is then looped over to pass each word to an SQL
query thus

$SQL = "select * from main where"

while(there are terms){

$SQL .= "Title like '%" . $term . "%' OR"

}

// this would lead, using the above example, to an SQL query which
looks like this:

$SQL = "SELECT * FROM main WHERE Title like '%this%' OR Title like
'%test%' OR Title like '%string%'

What I need to do is to check how many of the terms actually match
something. Is there any way, either in MySQL or with the result array
in PHP, to determine this?

Am I on completely the wrong course (as it seems to me at the
moment...)

tia

Chris

On Wed, 18 May 2005 10:29:15 +0100, Chris wrote:
> What I need to do is to check how many of the terms actually match
> something.

I think the best way might be to maintain a separate index table with
terms. New search terms will be a bit slower. Alternatively, you could
just, in addition to your original query, have separate queries for each
term (using count() or using the rowcount of each result).


--
Firefox Web Browser - Rediscover the web - http://getffox.com/
Thunderbird E-mail and Newsgroups - http://gettbird.com/

On Wed, 18 May 2005 11:49:14 +0200, Ewoud Dronkert
<firstname@lastname.net.invalid> wrote:

>have separate queries for each
>term (using count() or using the rowcount of each result).


Hmmmm, that might just work, thanks! I think I've been staring at this
problem for so long that I missed the obvious!

Thanks again

C

Chris <Chris.Stephens@nez.oc.ku> wrote:
[snip]
> What I need to do is to check how many of the terms actually match
> something. Is there any way, either in MySQL or with the result array
> in PHP, to determine this?
>
> Am I on completely the wrong course (as it seems to me at the
> moment...)

Ehhhh, it looks to me like you are trying to reinvent full text
searches (http://dev.mysql.com/doc/mysql/en/fulltext-search.html)