MySQL sadness

From: CHYRON (DSMITHHFX)24 Mar 2011 02:02
To: Peter (BOUGHTONP) 10 of 17
You need help...
From: Peter (BOUGHTONP)24 Mar 2011 02:05
To: CHYRON (DSMITHHFX) 11 of 17
It's taken you this long to realise?
From: CHYRON (DSMITHHFX)24 Mar 2011 02:27
To: Peter (BOUGHTONP) 12 of 17
:-@
From: 99% of gargoyles look like (MR_BASTARD)24 Mar 2011 10:26
To: steve 13 of 17
Your tags should be in a separate table, with a cross-table between the two. I haven't tested this (and don't have the means to ATM), so would likely need 'tweaking' (possibly needs JOIN LEFT):

TABLE 1
code:
ImageID	Image
1	cat.jpg
2	mouse.jpg
3	hamster.jpg
4	dog.jpg

TABLE 2
code:
TagID	Tag
1	nice
2	bitey
3	fuzzy

TABLE 3
code:
ImageID	TagID
1	1
4	1
2	2
3	2
1	3
2	3
3	3
4	3

SQL (pronounced 'sequel')
sql code:
SELECT t1.* FROM images t1
JOIN crosstab t2 ON t1.ImageID=t2.ImageID
JOIN tags t3 ON t2.TagID=t3.TagID
WHERE t3.Tag='fuzzy' AND t3.Tag='bitey';
EDITED: 24 Mar 2011 10:28 by MR_BASTARD
From: Peter (BOUGHTONP)24 Mar 2011 14:45
To: 99% of gargoyles look like (MR_BASTARD) 14 of 17
t3.Tag can't be two things at once.
From: 99% of gargoyles look like (MR_BASTARD)24 Mar 2011 15:13
To: Peter (BOUGHTONP) 15 of 17

Maybe the 'AND' should be 'OR'
<shrug>

From: Peter (BOUGHTONP)24 Mar 2011 15:20
To: 99% of gargoyles look like (MR_BASTARD) 16 of 17
And then he'd need the group by/having again, to restrict it to items with both.
From: steve24 Mar 2011 20:07
To: 99% of gargoyles look like (MR_BASTARD) 17 of 17
I realised it'd be better to have the tags work like that, but like I said - it's a very small-scale script and there are benefits to doing it this way (when the last image tagged as something has it's tag removed, that option vanishes altogether.etc).