Requesting assistance with MySQL query

article --> categories --> related articles

[SixTimesEight]

I'm working on a simple news publishing project using PHP/MySQL.

I have table 'articles' and table 'tags'.

I would like to display a list of related articles on each individual article page.

I have it working now by using a foreach on the tags of the currently viewed article and listing all of the articles which share any of those tags. Unfortunately this produces duplicate results since some articles share several tags.

I'd like to whittle it down to one SQL query that can fetch all articles which share any of the tags of the currently viewed article. Then I could just use DISTINCT on the article_id to prevent dupes.

The 'tags' table has no unique value. It contains 'tag_article_id', 'tag_name' and 'tag_phrase'. The 'tag_name' is used in URLs (http://example.tld/news/tags/tag_name) and the 'tag_phrase' is the display version of the tag.

Any suggestions aside from restructuring the database?

Thanks for looking.

[darrenG]

select * from table where tag_phrase = 'phrase1' or tag_phrase = 'phrase2' (and so on for each tag)

I think thats what you're looking for

[SixTimesEight]

That would be one simple way of doing it.

But the query would have to be built dynamically based on the tags for the individual article currently being viewed (which I could easily do I suppose) and would produce some very long queries as some of the articles have over a dozen tags associated with them.

[darrenG]

Yes that is true.

Another way would be to use a full-text search using all the tags as one 'search term', but im not really sure if you would see any performance benefit that way, you'd have to do some tests.