0

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;
tshepang
  • 12,111
  • 21
  • 91
  • 136
user1260310
  • 2,229
  • 9
  • 49
  • 67

2 Answers2

0

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.

Hituptony
  • 2,740
  • 3
  • 22
  • 44
0

For anyone who finds this question, I went with what seems to be a better approach using Match Against in the SQL query. For the tags or search terms that are similar to the link, ie for Relationship, romance, love etc., just create a string of keywords however long you want, as in $keywords = "romance, love, marriage"; You can add to it as you learn of new terms. Then run a sql query $sql = "SELECT * FROM pages Match (shdescript, longdescript) Against ('$keywords')"; For this to work, you need to create a full text index in MYSQL on the fields you want to search against ie shortdescript longdescript. The nice thing is it actually ranks your search results by relevance as certain MYSQL engines have some fairly robust search engine capability. For example, you can instruct it to weight certain things using mathematical percentages as in Mysql fulltext search relevance across multiple tables or just go with defaults.

Community
  • 1
  • 1
user1260310
  • 2,229
  • 9
  • 49
  • 67