Counting matches

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


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



Re: Counting matches

On Wed, 18 May 2005 10:29:15 +0100, Chris wrote:
Quoted text here. Click to load it

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 - /
Thunderbird E-mail and Newsgroups - /

Re: Counting matches

On Wed, 18 May 2005 11:49:14 +0200, Ewoud Dronkert

Quoted text here. Click to load it

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


Re: Counting matches

Quoted text here. Click to load it

Ehhhh, it looks to me like you are trying to reinvent full text
searches (

Site Timeline