Tag search algorithm?

Question

PHP

If the database table has a tags string field that contains tags separated by commas, how best to implement search results? Let's say the first result is an entry with a match of 3\3, then 2\3 and 1\3.

Answer 1

SELECT record.id, count(*) 
FROM record 
   JOIN record_tags ON (record_tags.record_id = record.id)
WHERE record_tags.tag_id IN (1,2,3) GROUP BY 1 ORDER BY 2 DESC

In a many-to-many data structure, get all records associated with the tags you are looking for, and sorting will return the number of matches (one record with different tags)

Answer 2

No way. You cannot store tags in a text field with tags written in a row. To do this, they make a many-to-many relationship, where the tags are in a separate table, the records are separate and there is a table of tag links to records.