If I am creating a blog system and I would like to incorporate 'tags' into my blogposts. These tags function in a way that is similar to what you see here, as they help to categorize posts with similar topics.
My plan is to store the tags in the database as a comma-separated string of words (words without spaces). However, I am unsure about how I would search for all posts that contain both tag A and tag B.
I prefer not to use a simple solution that works well with a small database by retrieving all data and scanning it using a PHP loop. This method would not be suitable for a large database with hundreds or thousands of posts. Even though I do not anticipate having many blogposts, I want the system to be robust and efficient, saving processing time on PHP scripts by obtaining accurate results directly from the database.
Imagine if my table had a structure like this (it's actually slightly more complex)
blogposts:
id | title | content_html | tags
0 | "hello world" | "<em>hello world!</em>" | "hello,world,tag0"
1 | "bye world" | "<strong>bye world!</strong>" | "bye,world,tag1,tag2"
2 | "hello you" | "hello you! :>" | "hello,tag3,you"
How could I select all posts that have both "hello" and "world" within their tags? I am aware of the LIKE statement, which allows you to search for substrings, but can it be used effectively with multiple substrings?