467,867 Members | 1,273 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,867 developers. It's quick & easy.

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

Jul 17 '05 #1
  • viewed: 1170
Share:
3 Replies
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/
Jul 17 '05 #2
On Wed, 18 May 2005 11:49:14 +0200, Ewoud Dronkert
<fi*******@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

Jul 17 '05 #3
Chris <Ch************@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)

Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Adam i Agnieszka Gasiorowski FNORD | last post: by
5 posts views Thread by cassandra.flowers | last post: by
4 posts views Thread by dd | last post: by
5 posts views Thread by rob.guitar.rob | last post: by
13 posts views Thread by Regnab | last post: by
7 posts views Thread by Chris Lasher | last post: by
3 posts views Thread by trashman.horlicks | last post: by
9 posts views Thread by drasko | last post: by
1 post views Thread by jjjones3860 | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.