I'm trying to make something similar to "related articles". This is what I have.
$query = "SELECT * FROM posts WHERE post_tags LIKE '%$post_tags%'";
I want it to 'select all from post where the post tags are similar to the current post's tags'.
Note: The tags look like this in the database: "tech, news, technology, iphone"
I looked into things like
$tags = explode(",", $post_tags );
But I'm not sure.
SELECT * FROM `posts` WHERE MATCH(post_tags) AGAINST('$post_tags' IN BOOLEAN MODE)
The query will require you to add a FULLTEXT index to your post_tags table column (unless you have the older MyISAM table). This query will be a lot faster than your current attempt.
query to add the index
ALTER TABLE `posts` ADD FULLTEXT INDEX `tag_search` (`post_tags`)
Change how you store the post-to-tag relationship in the DB. Your posts table should not be used to store tags because one post has many tags, but each post has only one record in the posts table. Instead, have a two other tables:
tags table
tag_id | name
1 | technology
2 | news
3 | hobbies
post_tags table
post_id | tag_id
1 | 1
1 | 3
2 | 1
Notice it's easy to tell that post_id #1 has the technology and hobbies tags. This will make your queries easier, and faster.
If you do want to store everything in the posts table but have even faster performance, you will need to store your tags as bit flags. For instance, if the following is true in your PHP application:
$techBit = 0b001; // number 1 in binary form
$newsBit = 0b010; // number 2 in binary form
$hobbiesBit = 0b100; // number 4 in binary form
Then it's easy to store tags in one field. A post that has technology and hobbies tag would have a value:
$tag = $techBit | $hobbiesBit; // 1 + 4 = 5
And if you wanted to search for all records with technology or hobbies, you would do:
// means: records where post_tags has either techBit or hobbiesBit turned ON
SELECT * FROM `posts` WHERE (`post_tags` & ($techBit | $hobbiesBit)) > 0