MYSQL / PHP标签搜索引擎

Question

I am implementing tags for finding pages and have essentially a 3 table structure, tags, pages and tagpages. For the menu system of the site, I'd like to have a small number of links map to tags to make an educated guess about relevance. Not looking for google level search results but something beyond mere search on one term that would exclude many results. For a romance link, for example, I'd like to pick up tags such as love, relationship, etc. in addition to romance. Since tags are so common, is there an established tag engine to pull up results by tags without an exact match? I am thinking of constructing a where clause with multiple search terms in it but it starts to get a bit unwieldy. Is there a better way than below?

Potential approach:

$sql = "SELECT * FROM tagpages WHERE";
switch ($tag)
{
case "romance": 
$where = "tag = 'relationship' || 
tag== 'love' ||
tag== 'breakup";
break;
case "sports":
$where = "tag = 'football' || 
tag== 'baseball' ||
tag== 'hockey";
break;
case "politics":
$where = "tag = 'election' || 
tag== 'presidential'";
break;
default:
$where = "";
}
$sql .= $where;

Answer 1

well to start you are only matching the case when the tag = string, you are stating with the case structure to exclude all other tags.

You can use the "%" modulus sign to match LIKE characters or match characters and then modulus the end of the string meaning the rest of the string looks like whatever, as long as these characters before the modulus sign match. "CHAR%" would return character, charms

You can exclude using <> which means NOT but you may need to switch to an IF...ELSE structure, if you know all the possible matches, I think you can just declare the tags, and then they will be numbered 1...n and you can match on the placeholder number...not sure on that one tho.