mysql string comma separated like %which one caused result?%

Question

I have a very complex issue that Me and my coworker have been discussing for a long time today and we just can't seem to get to the answer, wondering if anyone here has a bright idea we have missed.

$tags=mysql_real_escape_string($_GET["tags"]);
$tags="SELECT * FROM i WHERE tip LIKE '%".$tags;
$tags=str_replace(",","%' OR tip LIKE '%",$tags);
$tags=$tags."%'";
$qtags = mysql_query($tags) or die(mysql_error());

example: $tags='word,phrase,term,foo,bar,anything'

tags are pulled from a paragraph written by a user and then compared with paragraphs stored in 'i' (sql database).

the problem we are facing is how to identify what/which 'tag' (out of the tags string) caused the result.

example: the resulting paragraph was like the tag 'foo' from the string $tags which was 'word,phrase,term,foo,bar,anything' how can we identify foo as the cause?

Answer 1

You could try something like this:

SELECT
    CASE WHEN tip LIKE '%foo%' then 'foo'
         WHEN tip LIKE '%bar%' then 'bar'
         WHEN tip LIKE '%anything%' then 'anything'
    END as MatchedTag,
    i.*
FROM i
WHERE tip LIKE '%foo%'
   OR tip LIKE '%bar%'
   OR tip LIKE '%anything%'

It would need to be generated from your $tags variable like your current query.