Seaching using tags in PHP/MySQL

Question

I am struggling to find a solution to this. I have tags that are inserted into the database in this form

mytags=firsttag, secondtag, thirdtag

On the single page I need to show related items based on these tags aso I am using

SELECT * FROM posts WHERE tags LIKE \"%$mytags%\" ORDER BY views DESC LIMIT 5"

However this doesn't work much as it only searches for posts that have the exact same tagline (firsttag, secondtag, thirdtag) and not the posts that their tag contain one of the tags (firsttag, anothertag, andanothertag).

I thought of exploding mytags and then search using the first tag in the array, but this doesn't work very good.

Does anyone know an effective method of searching?

Clark · Accepted Answer · 2011-09-05 19:53:59Z

2

Why don't you use separate table for post tags keeping? I mean for example table 'posts', table 'tags' and relation table 'posts_tags'. Here is structure of 'posts':

id
title
content

Here is structure of 'tags':

id
tag_name

And here is structure if 'posts_tags':

id
post_id
tag_id

This is scheme of 'many to many' relationship model. And you can now simply find tags or/and posts through JOIN operator.

answered Sep 5, 2011 at 19:53

Clark

2,2136 gold badges35 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

str Over a year ago

The "id" attribute in "posts_tags" is not even necessary.

Clark Over a year ago

Yes but what if you want to delete one of the relationship rules?

str Over a year ago

Whats wrong with DELETE ... WHERE post_id=... AND tag_id=...?

Clark Over a year ago

Nothing. But delete by ID is more beautifull, logic and compact. Also ID's can be indexed if you need it.

str Over a year ago

You can also index post_id and tag_id. And when talking about relations it makes more sense to talk about post_id=5 and tag_id=9 instead of ID=2 what does not relate to anything useful.

|

Rok Kralj · Accepted Answer · 2011-09-05 19:50:42Z

1

This is not a propper way to do it. Your database is not normalised. In short, it is impossible to do what you want, and even if it is, that'd be highly ineffective.

You have to create another table - Tags, and then another one to represent the connection of tags to Posts let's say TagsPosts, everything propperly indexed.

answered Sep 5, 2011 at 19:50

Rok Kralj

49k10 gold badges75 silver badges80 bronze badges

7 Comments

ajreal Over a year ago

is possible:-) but not optimized

Rok Kralj Over a year ago

@ajreal, please post your solution, I guess it would make a nice difference from all the current answers.

ajreal Over a year ago

use OR compare three tags..having count>=1

Rok Kralj Over a year ago

yes, and take care of all the exceptions, like abc matching abcd tag, whitespace, and dynamically generating mysql query. Unable to use any index, making thing slow as hell. :)

ajreal Over a year ago

my purpose is to indicate the possible,not like what you said

|

str · Accepted Answer · 2011-09-05 19:59:42Z

1

You might want to look into find_in_set() but also, I would highly recommend to normalize your database and use a proper structure.

answered Sep 5, 2011 at 19:59

str

45.5k18 gold badges117 silver badges134 bronze badges

3 Comments

Rok Kralj Over a year ago

Nice find! I didn't know that.

Kbr89 Over a year ago

find_in_set() returns an integer no?

str Over a year ago

@Kbr89 Yes it does (or NULL).

Bojangles · Accepted Answer · 2011-09-05 19:58:50Z

0

You should always keep your data normalised (one piece of data in each column). There's a solution to this problem using two tables; one for tags and one for posts and a MySQL LEFT JOIN. Below is a very minimal example.

Table `posts`

id  int(11) Auto Increment   
title   char(255)

Table `tags`

id  int(11)  
postid  int(11)  
tag char(64)

Query to search for posts with tags containing `firstpost`

(important)

SELECT p.title FROM posts p
LEFT JOIN tags t
ON t.postid=p.id
WHERE t.tag LIKE '%firstpost%'

Sample data

INSERT INTO `posts` (`id`, `title`) VALUES
(1, 'Post number 1'),
(2, 'Another post'),
(3, 'Third post');

INSERT INTO `tags` (`id`, `postid`, `tag`) VALUES
(0, 1,  'first_post'),
(0, 1,  'firstpost_secondtag'),
(0, 2,  'secondpost_firsttag');

As I said above, this is a very minimal example. The most important bit here is the use of LEFT JOIN and a separate tags table. It should be obvious to you that you simply replace firstpost in the LIKE statement with a PHP variable.

answered Sep 5, 2011 at 19:58

Bojangles

102k50 gold badges175 silver badges209 bronze badges

2 Comments

Kbr89 Over a year ago

I was thinking of doing this, but I want to keep my program simple. Seems like I don't have any choice. Thanks

Bojangles Over a year ago

My question looks rather long, but this is a simple as it gets; the tags table only needs two fields at minimum; tag text and the post ID that uses that tag. Your posts table doesn't need changing; only the SELECT query, which is pretty short. You can expand this example to use an intermediate table so you have a list of tags, and a sort of "interface" between posts and this tag list.

Collectives™ on Stack Overflow

Seaching using tags in PHP/MySQL

4 Answers 4

6 Comments

7 Comments

3 Comments

Table `posts`

Table `tags`

Query to search for posts with tags containing `firstpost`

Sample data

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

7 Comments

3 Comments

Table posts

Table tags

Query to search for posts with tags containing firstpost

Sample data

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

Table `posts`

Table `tags`

Query to search for posts with tags containing `firstpost`