show related articles by title and tag using php/mysql

Question

I have an articles and tag tables like this

 1. articles
|itemid   |title            |tag
1         |my first post    |hello, world
2         |my second        |world war post
3         |my third post    |testing

the tag is separate either by space or comma

2. tagterm
tag_id| tagterm
1     | hello
2     | world
3     | war
4     | post
5     | testing


3. tag_link
id| tag_id| tag_itemid
1 | 1     | 1
2 | 2     | 1
3 | 2     | 2
and so on

i would like to show all related articles by the tag field and also title if thats possible

what i tried so far... does not work please help, im a newbie

SELECT a1.itemid, GROUP_CONCAT(DISTINCT a2.itemid) AS related_articles 
FROM articles AS a1 
JOIN tag_link AS t1 ON a1.itemid = t1.tag_itemid 
JOIN articles AS a2 ON a2.itemid = t1.tag_itemid GROUP BY a1.itemid

"does not work" is not a good problem description. What result do you get? And what result do you expect? — Paul Spiegel, Feb 16 '19 at 19:03

score 1 · Accepted Answer · answered Feb 16 '19 at 20:39

Try it:

SELECT a1.itemid, a1.title, group_concat(DISTINCT a2.itemid, CONCAT('@',a2.title) SEPARATOR "||") as related_articles
FROM articles AS a1 
JOIN tag_link AS t1 ON a1.itemid = t1.tag_itemid 
JOIN 
    (SELECT a1.itemid as itemid, a1.title as title, t1.tag_id as tag_id
        FROM articles AS a1 
        JOIN tag_link AS t1 ON a1.itemid = t1.tag_itemid) as `a2`
    ON a2.tag_id=t1.tag_id
WHERE a2.itemid != a1.itemid
GROUP BY a1.itemid

Live DEMO

show related articles by title and tag using php/mysql

1 Answers1