Using LIKE in MySQL Query

[new7media]

I've put together a content management tool that allows my users to add topic information about an article. In the article entry form, users can select as many topics as they need in order to describe their article. They choose their topic or topics from a select pull-down menu set to allow for multiple selections. The name I use in the select field is name="topics[]". Everything here works fine.

In my PHP code, I then take their multiple selections and use "implode" to insert commas (no spaces) between each one-word topic. The comma-separated topic data is then inserted into the database along with the rest of the article entry. Everything here works fine.

I've set up the pages on the site so that the topic data is sent in the URL string (http://www.thesite.com?section=Articles&article_id=n&topic=whatever+the+user+inserted). I've set up a little area on the page that tracks the topic and queries the database for other related articles, based on that topic and then displays the list of related articles.

Here's my problem: What does my select query need to look like to get an accurate list of related articles out of the dbase??? I've tried MATCH/AGAINST which didn't work at all. I've had more success with LIKE but can't get the right results. Here's where things stand:

SELECT title, article_id, date_added, section, topic FROM archive WHERE topic LIKE '%$topic%' AND article_id != $article_id ORDER BY date_added ASC

Does anyone know how to deal with the comma-separated topic data so that it's processed by LIKE correctly? Should it look like:

...topic LIKE ('environment' OR 'economy' OR 'crime')

OR is it:

...topic = 'environment' OR topic = 'economy' OR topic = 'crime'

OR ???

One other thing that just occured to me (dread) -- we do have a few topics that are more than one word. For instance, "arts and culture" or "race and ethnicity". Maybe the easiest way around this one is to shorten these to just one word? Or is there a way to allow a LIKE search on 'arts and culture'?

Any help is SO appreciated!!!

[new7media]

This article gives you all of the answers. Short and to the point:

http://www.zend.com/zend/tut/tutorial-ferrara1.php

[[deleted]]

Unfortunately MySQL's FULLTEXT indexes are not very usefull as they only match on whole words, and generally have a lot of restrictions that make them.. well... useless.

As with so many things in mysql, they get an A for ideas, and a F- for implementation.

THe correct syntax if you are looking for substrings is:

WHERE topic LIKE '%yourtopichere%'
OR topic LIKE '%yourothertopichere%'
OR topic LIKE ... and so on.

If your topics are pre-defined and must match completely, then you can do the IN method:

WHERE topic IN ('topichere', 'othertopichere', and so on);

Also, I suggest you read the 'slapping together a search-engine' article on this site, it may give you some new ideas on creating a 'good' search method.