lefen
February 4th, 2008, 09:16 PM
Hi all,
I hope someone will be able to help with this. I'm trying to LIMIT a LEFT JOIN in MYSQL, but it's all gone horribly wrong.
I have two tables, one called 'links' and the other called 'linktags'.
'linktags' stores tags for URLs in table 'links' and also, the id in table 'links' of said URL.
So the object is to output the URL, with all its associated tags. Here's what I'm using (wrapped in some PHP):
$query = "
SELECT linktags.*, links.* FROM linktags
LEFT JOIN links ON linktags.linkid = links.id
WHERE (links.public='y')
ORDER BY links.id DESC LIMIT 4";
$result = mysql_query($query) or die(mysql_error());
Here, the intent is to limit the output of the query to 4 URLS, but it actually limits the output to 4 tags, like so:
http://www.ubuntuforums.org
tag: linux
tag: ubuntu
tag: forum
http://www.linux.com
tag: linux
I'm sure I'm doing something really stupid, but just can't see it. Also, I have a horrible feeling that I should be doing something clever with subqueries, but I've not had any experience with them and the internet fails to help.
Any ideas? Thanks very much, I know it's a big ask!
I hope someone will be able to help with this. I'm trying to LIMIT a LEFT JOIN in MYSQL, but it's all gone horribly wrong.
I have two tables, one called 'links' and the other called 'linktags'.
'linktags' stores tags for URLs in table 'links' and also, the id in table 'links' of said URL.
So the object is to output the URL, with all its associated tags. Here's what I'm using (wrapped in some PHP):
$query = "
SELECT linktags.*, links.* FROM linktags
LEFT JOIN links ON linktags.linkid = links.id
WHERE (links.public='y')
ORDER BY links.id DESC LIMIT 4";
$result = mysql_query($query) or die(mysql_error());
Here, the intent is to limit the output of the query to 4 URLS, but it actually limits the output to 4 tags, like so:
http://www.ubuntuforums.org
tag: linux
tag: ubuntu
tag: forum
http://www.linux.com
tag: linux
I'm sure I'm doing something really stupid, but just can't see it. Also, I have a horrible feeling that I should be doing something clever with subqueries, but I've not had any experience with them and the internet fails to help.
Any ideas? Thanks very much, I know it's a big ask!