Lefen
I think Clavis wins my heart <3   SSHOLE |
Posts: 896 Registered: 9/16/2003 Offline
|
12/18/2007 at 19:34 |
Hello, Linkswarm
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 tits-up :/
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:
$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.linkswarm.com
tag: clickys
tag: websight
tag: stuff
http://www.megarad.com
tag: RIP :'<
I'm sure I'm doing something really stupid, but just can't see it. Also the internet fails to help.
Any ideas?
____________________
< barfass> hey, fuck your crumpets, postman pat
|
| |
casmhar
Lost In Space  SSHOLEPosts: 22 Registered: 10/1/2007 Offline
|
12/18/2007 at 20:00 |
ORDER BY links.id DESC LIMIT 4; try losing the quote symbol
____________________
Some people are like Slinkies... not really good for anything, but they still bring a smile to your face when you push them down a flight of stairs. |
| |
vasudeva
Bad Taste in your Mouth  SSHOLEPosts: 4413 Registered: 3/8/2002 Offline
|
12/18/2007 at 20:04 |
I think the problem here is that you're not specifying which type of row you want 4 of, so it sucks in tags til it hits 4 rows, then moves on.
I bet your results look like this, right?
http://www.linkswarm.com | clickys
http://www.linkswarm.com | websight
http://www.linkswarm.com | stuff
http://www.linkswarm.com | faggotry
http://www.mecharad.com | dead_as_a_fart
You try a subquery? I don't have time to really stare this down right now, but that's the first thing I'd dink with (being kind of an unlettered idiot).
____________________
mundhra: And its crocobody is made of dile. |
| |
Lefen
I think Clavis wins my heart <3 SSHOLEPosts: 896 Registered: 9/16/2003 Offline
|
5/26/2008 at 16:46 |
So 5 months later and I've finally worked this out (^.^) perhaps it'll be useful to someone:
SELECT group_concat( T.tag ) AS tags, B.title
FROM links AS B
LEFT JOIN linktags AS T ON B.id = T.linkid
GROUP BY B.title
ORDER BY B.id DESC
LIMIT 0 , 30
group_concat() is the magic ingredient and here it's grouping all the tags for a particular bookmark into a single row called 'tags' (the default is a comma separated list).
The result is a two-column table:
http://www.linkswarm.com | clickys,websight,stuff
http://www.megarad.com | RIP,stuff
(^_________^; )
On 2008-05-26 at 11:48:48, Lefen asked to smell your dick
____________________
< barfass> hey, fuck your crumpets, postman pat |
| |
|
