[RESOLVED] MySQL tagging system
I've created a tagging system to relate one or more rows in one table to multiple rows in another table. the only problem that I can see is that I'm using a stored function (undesirable), and I have repetitive SQL that I don't like. plus, I have to hardcode the number of tags to find, I can't just .. find all tags, or something.
I can easily accomplish what I'm trying to do using a mix of server-side programming with SQL, but I'd prefer to keep this on the database-side of things if possible. The stored function I'm using is simply a string delimiting function (used in the fashion: STRSPLIT(string, delimiter, position)):
Code:
CREATE FUNCTION `STRSPLIT`(x varchar(255), delim varchar(12), pos int) RETURNS varchar(255) CHARSET latin1
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '')
which I can use with the following SQL to bring up 10 rows from tagTable.
Code:
SELECT * FROM `tagTable` WHERE shortName IN (
(SELECT STRSPLIT(`tag`, ',', 1) FROM `mainTable` WHERE shortName='abcde'),
(SELECT STRSPLIT(`tag`, ',', 2) FROM `mainTable` WHERE shortName='abcde'),
(SELECT STRSPLIT(`tag`, ',', 3) FROM `mainTable` WHERE shortName='abcde'),
(SELECT STRSPLIT(`tag`, ',', 4) FROM `mainTable` WHERE shortName='abcde'),
(SELECT STRSPLIT(`tag`, ',', 5) FROM `mainTable` WHERE shortName='abcde'),
(SELECT STRSPLIT(`tag`, ',', 6) FROM `mainTable` WHERE shortName='abcde'),
(SELECT STRSPLIT(`tag`, ',', 7) FROM `mainTable` WHERE shortName='abcde'),
(SELECT STRSPLIT(`tag`, ',', 8) FROM `mainTable` WHERE shortName='abcde'),
(SELECT STRSPLIT(`tag`, ',', 9) FROM `mainTable` WHERE shortName='abcde'),
(SELECT STRSPLIT(`tag`, ',', 10) FROM `mainTable` WHERE shortName='abcde')
);
as you can see, this is kind of ridiculous. using 10 sub-queries is probably just a little inefficient, too. the SQL would be generated dynamically, but it's still kind of an ugly block of code that I wouldn't want to put into my application unless needed. my alternative solution would be to fetch the tag field from mainTable, and build a string server-side instead, which would produce SQL similar to:
Code:
SELECT * FROM `is_career` WHERE shortName IN ('value1', 'value2', 'value3', 'value4');
so, SQL gurus, is there a better way that I could maybe go about this? any and all suggestions are welcome.