I have a custom working blog. Everything works fine I was just wondering if anyone knows of a better way to select articles containing tags from a mysql database.
Currently the tags are stored in the "blog" table, under "blog_tags" and the articles are retreaved using LIKE '%tag%'. I this worked fine until I had 2 tags containing the same word e.g. website and website design.
Using the above method by searching for tags like website I would get the results containing the website tag and website design.
To fix the solution I added "*" at the beginning and end of each tag so blog_tags now looks like *tag 1*, *tag 2*, *tag 3*, *And so on...
Now I just search for articles with blog_tags like '%*tag here*%'. This works perfectly but there is probably a better way. Any ideas?
The query currently used is:
"SELECT * FROM `blog` WHERE `active` = 'y' AND `blog_tags` LIKE '%*" . str_replace("-", " ", $tag) . "*%' ORDER BY `blog_created` DESC LIMIT " . $startfrom . "," . $limit
Thank in advance!
You should not have multivalued fields in a database. Given your example, it seems that you have blogs that have many tags and tags that correspond to many blogs. This is a many-to-many relationship.
As a rule of thumb, many-to-many relationships generate a new table. So the database schema you should be using would be something like:
Blogs(*Id*, activ, date_created) Tags(*Id*, text) Blog_Tag(*BlogId*, *TagId*)
Primary keys are displayed between asterisks.
So, if you want to search all blogs tagged with 'mysql' you would do something like:
select * from blogs b
join blog_tag bt on b.id = bt.blogid
join tags t on bt.tagid = t.id
where t.text = 'mysql'
Of course, if you know the TagId in advance, you won't need the join on Tags.
Hope this has cleared some doubts.
You have a relational database. It is designed to store structured data. Don't invent your own structures and then store those in fields.
This is a standard many to many relationship.
Create a table "tags" and a association table with two columns which are both foreign keys (tag id and blog entry id).
Then use a JOIN to get the entries you care about.