CODEWITHSUNDEEP
phpmysqlsql
SeinopSys
SeinopSys

Reputation: 8937

Where Like statement to select records with specific tags

My table looks something like this:

id | title         | tags
1  | I like things | 9,3,7,10
2  | Stuff about i | 12,3,7,10
3  | Overly loaded | 1,3,4,5
4  | Never ever AT | 12,10
4  | Forever alone | 9

Note: the names were made up, they're only for illustration purposes

Things about the tags:

I want to add a filter feature, which allows the user to filter the list using these tags. Let's ignore the client-side part, and only care about what we recieve.

Let's say, the script receives the following input (which is always sorted): "3,4,9"

Then, I would like to pass this to a MySQL query in order to retrieve the matching items. The site also has a pagination feature, but I'm only saying this because it may affect the order of the statements, it's not important otherwise. It just adds a LIMIT statement at the end.

Putting all escaping and what not aside, the query I tried to use was 

SELECT * FROM `$tablename` WHERE tags LIKE '%$input%' ORDER BY title ASC LIMIT 0,10

But this - of course - didn't work. I gave me this nice little error message, saying:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%3,4,9%' ORDER BY title ASC LIMIT 0,10' at line 1 in script.php on line n

I would like to know how to alter this query in order to be able to properly filter the results, even if it takes an extra function to append multiple LIKE statements together.

Upvotes: 2

Views: 86

Answers (2)

lwitzel
lwitzel

Reputation: 591

I know this won't be the accepted answer, but because there may be cases where changing the database structure may not be possible, I thought I'd put this out there as a precise answer to the question.

Given the constraints you've stated, and assuming PHP is your control language (since the question is tagged with PHP), I would build the SQL statement using PHP:

<?php
// assuming $tags contains your comma-delimited tags
$tags_array = explode(',',$tags);
$where = 'where true';
for($i=0;$i<sizeof($tags_array);$i++) {
  switch($tags_array[$i]) {
    case '1':
      $where .= " and tags LIKE '1%' and tags NOT LIKE '12%'";
      break;
    case '9':
    case '12':
      $where .= " and tags LIKE '" . $tags_array[$i] . "%'";
      break;
    default:
      $where .= " and tags LIKE '%," . $tags_array[$i] . "%'";
      break;
  }
}
$sql = "SELECT * FROM `$tablename` " . $where . " ORDER BY title ASC LIMIT 0,10";
?>

Having said that, I agree with everyone commenting above: it's much better to get the data model right in the database.

Upvotes: 2

Hart CO
Hart CO

Reputation: 34784

WHERE tags LIKE '%$input%'

Will only work when the list of input tags is found within the tags string. "3,4,9" is not like "3,4,5,9"

You have a denormalized structure, which is causing you problems, one row per tag value is what you should implement.

The alternative is to break apart the input string and use multiple OR LIKE statements:

WHERE tags LIKE '%input1%'
   OR tags LIKE '%input2%'
    ....

Then you have to deal with the issue of 1 matching to 11, which means concatenating commas on the front and back of each side of the LIKE statement, or padding with some value. The workaround becomes an ugly beast that's far less efficient than fixing the underlying design problem.

Upvotes: 3

Related Questions