Creating a related articles query in PHP

Question

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.

Answer 1

Use FullText search -- docs

SELECT * FROM `posts` WHERE MATCH(post_tags) AGAINST('$post_tags' IN BOOLEAN MODE)

Live demo

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`)

A better, faster approach

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.

Even 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

Answer 2

Well instead of "LIKE" you could use the "IN" clause.

$Results = join("','",$post_tags);   
$SQLQuery = "SELECT * FROM galleries WHERE id IN ('$Results')";

Example: Passing an array to a query using a WHERE clause