CI & MySQL; using LIKE on a delimited string

Maybe LIKE isn't the proper solution for what I'm attempting to do; but what I'm looking to achieve is to take a string retrieved from the database. (i.e. tags [ tag1,tag2,tag3,tag4 ]) - then compare each value (delimited by the comma) and return like rows a开发者_运维百科ccordingly.

<?
$string = 'tag1,tag2,tag3,tag4';
$tags = explode(',', $string);
foreach ( $tags as $tag )
{
    $this->db->limit(2); $this->db->like('Tags', $tag);
    $result = $this->db->get('table');
    $recommend[] = $result;
}

This is the best I could understand how to do what I'm looking for, but I'm quite confused on the LIKE method in general of CI's Active Record Class.

Is performing searches on delimited string in a database even really entirely wise?

Thanks for the input, and ideas you two. MySQL goes a bit over my head, but after reading a bit about the things possible with relational databases; I'm going to attempt to learn this process so I can build my database more efficient.

My database is going to be a catalog of video games and files, so obviously that means it's going to reach insane amounts of entries - so attempting to look through any sort of string for possible returned rows would be by far the dumbest thing I could do in the long run.

Again, thanks for the information and ideas.

If you have a properly normalized database, tags should be split up to a new table as this is a one-to-many (or maybe even a many-to-many) relationship.

However, the easiest way to achieve this is to also place a comma before the first tag, and after the last, and simply search for:

SELECT * FROM table WHERE Tags LIKE "%,coleslaw,%";

But yes, this is bad, and can be slow if your table grows as you will not be able to utilize indexes.

There is a FIELD command in MySQL that you might be able to take advantage of. You can read up on it here.

But in general, performing searches on delimited strings, or substrings in general, in a database will kill performance as you will lose any indexing when matching on the substring. If you have a thousand rows, MySQL will have to scan through all one thousand rows before discovering that there is no match. This applies to both Like and Substring appearing in a where clause. Many database implementations specifically provide a BEGINS WITH operator that can still take advantage of indexing, but I'm not aware of this in MySQL.

Consider instead breaking the string up during insertion, and storing each word seperately in a different table. If you find that you do this a lot, consider an engine built for text searching, such as Lucene. You can use PHP with Lucene through SOLR. While I throw that out there, it may very well be complete overkill for your project.

I know this probably isn't what you want to hear, but your gut is correct on this one.