I'm using MySQL / PHP to store my data.
I have a website with posts and I would like to be able to order them by tags. (The posts have multiple tags)
There are 3 tables.
medias (posts)
+----+--------+-----+
| id | title | ... |
+----+--------+-----+
| 1 | hello | ... |
| 2 | hi | ... |
+----+--------+-----+
tags
+----+------+-----+
| id | name | ... |
+----+------+-----+
| 1 | red | ... |
| 2 |square| ... |
+----+------+-----+
medias_tags (In order to link the medias & tags together)
+----+----------+--------+
| id | media_id | tag_id |
+----+----------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
+----+----------+--------+
So I'm using this SQL query to join the tables together and output the post with a certain tag.
$req_mda_list = $bdd->query('SELECT * FROM medias
JOIN medias_tags
ON medias.id=medias_tags.media_id
WHERE medias_tags.tag_id ='.$tag_id.'
ORDER BY date DESC
LIMIT '.$start.','.$limit);
It works however when I make an other SQL query inside in order to get the others tags of each posts (The posts have multiple tags) it doesn't works. I tried to twist the queries but I either get no data from the query or random tags.
$data_mda = $req_mda_list->fetch()){
$req_mda_tags = $bdd->prepare('SELECT * FROM medias_tags WHERE media_id = ?');
$req_mda_tags->execute(array($mda_id));
$mda_tags_list = null;
while ($data_mda_tags = $req_mda_tags->fetch()){
$tag_id = $data_mda_tags['tag_id'];
$req_tag_name = $bdd->prepare('SELECT * FROM tags WHERE id = ?');
$req_tag_name->execute(array($tag_id));
$data_tag_name = $req_tag_name->fetch();
$mda_tags_list .= $data_tag_name['name'].' ';
}
#Show media title
echo $mda_tags_list;
}
So you want to list the names of all of the tags associated to medias?.
MySQL has built-in function called GROUP_CONCAT. You can use that to get all the tag names in one query.
SELECT m.media_id, GROUP_CONCAT(t.name SEPARATOR ' ') AS tag_names
FROM media_tags m
INNER JOIN tags t ON m.tag_id = t.id
GROUP BY m.media_id
If you want to get the list of tags just per media_id, you can just add a WHERE clause.
SELECT m.media_id, GROUP_CONCAT(t.name SEPARATOR ' ') AS tag_names
FROM media_tags m
INNER JOIN tags t ON m.tag_id = t.id
WHERE m.media_id = ?
GROUP BY m.media_id
Is that what you are looking for?
select m.title
,t.name
from medias_tags mt
join media m
on m.media_id = mt.media_id
join tags t
on t.tag_id = mt.tag_id
order by m.title
,t.name
;
or this? -
select m.title
,group_concat(t.name order by t.name) as tags
from medias_tags mt
join media m
on m.media_id = mt.media_id
join tags t
on t.tag_id = mt.tag_id
group by m.title
order by m.title
;