I've tried very hard to have a normalized database and currently have three tables like so -
What I'm trying to do here is select some rows and return them with all the tags that are associated with it. At the moment, it only returns one tag for some reason.
Here is the code I'm using:
SELECT *,
GROUP_CONCAT(t.tag) AS tags
FROM content AS c
LEFT JOIN category AS ca ON c.cid = ca.cid
LEFT JOIN contenttags AS ct ON c.smid = ct.smid
LEFT JOIN tags AS t ON ct.tid = t.tid
LEFT JOIN users AS u ON c.uid = u.uid
WHERE (t.tag IN ('tag1', 'tag2', 'tag3'))
GROUP BY c.smid
It returns something like this:
Do you mean to find all contents and all their tags, where at least one of their tag is in $tagArray ?
SELECT c.*, ca.*, u.*,
GROUP_CONCAT(t.tag) AS tags
FROM content AS c
LEFT JOIN category AS ca ON c.cid = ca.cid
JOIN contenttags AS ct ON c.smid = ct.smid
JOIN tags AS t ON ct.tid = t.tid
LEFT JOIN users AS u ON c.uid = u.uid
WHERE EXISTS ( SELECT *
FROM contenttags ct2
JOIN tag t2
ON t2.tid = ct2.tid
WHERE ct2.smid = c.smid
AND ct2.tag IN ('$tagArray')
)
GROUP BY c.smid
And those LEFT JOIN can be probably turned into inner JOINs, too, without losing any results. Try it. Unless category.cid or category.uid fields can have NULL values. In that case, keep the related LEFT JOIN.
I believe you will need to do a second join on the tags table--one for the WHERE clause, and one to return all related results. Untested, but try this:
SELECT c.*,t2.*,ca.*,ct.*,u.* GROUP_CONCAT(t.tag) AS tags
FROM content AS c
LEFT JOIN category AS ca ON c.cid = ca.cid
LEFT JOIN contenttags AS ct ON c.smid = ct.smid
LEFT JOIN tags AS t2 ON ct.tid = t2.tid
LEFT JOIN users AS u ON c.uid = u.uid
JOIN tags AS t ON ct.tid = t.tid
WHERE (t.tag IN ('$tagArray'))
GROUP BY c.smid
This will return a separate row for every related tag, so you'll have to merge those results in your client code, or perhaps use GROUP_CONCAT() if you want all the tags on a single row (as an array)
This is your problem.
WHERE (t.tag IN ('$tagArray'))
Consider the following two expression do different things:
WHERE (t.tag IN (1,2,3,4))
WHERE (t.tag IN ('1,2,3,4'))
The quoted string, in spite of appearing to be a list of multiple integers, is in fact a single value. In an integer context, MySQL reads the leading digits ('1' in this case) and ignores anything following once it finds a non-digit.
What I'm saying is that the quotes on either side of your query are confusing. Is the value of $tagArray literally tag1','tag2','tag3? Or is it 'tag1','tag2','tag3'?
In general, troubleshoot SQL by looking at SQL, not by looking at PHP code that is producing SQL. And please show the final SQL query in your question, don't make us guess what the value of $tagArray is.
PS: This is probably not related to your question, but you're using LEFT JOIN where you need INNER JOIN.