Page 1 of 1

a tricky situation

Posted: Wed Sep 26, 2007 12:58 pm
by thewebdrivers
hi all

I am stuck into a tricky situation and i need your help to get out of that.

I am working on a system which has a multicategories system and each article can be assigned to more than one category. For that I am storing all category ids separated by commas against articleid like below:-

artcleid | categoryids
1 | 45,56,78
2 | 67,98,34

I am updating current system which used to have one on one relationship between categories and articles.

Now there are some queries that need updating. one such query is "select * from articles where categoryids < 100"

Now I am quiet confused on how should i update this query so that it fits current db structure. Basically i should check all category ids in categoryids field which have atleast one value which is less than 100.

I hope you understand my problem. any help will be highly appreciated.

Regards

Mike

Posted: Wed Sep 26, 2007 1:42 pm
by Begby
Do not store your categories like this. You want to have another table for the join with a separate record for each relationship

Articles Table

articleID | articleText | articleTitle


Categories Table

categoryID | categoryName


ArticleCategories Table

articleID | categoryID


So for the examples you posted, you would have this in the ArticleCategories Tabe.

articleID | categoryID
1 | 45
1 | 56
1 | 78
2 | 67
2 | 98
2 | 34

Posted: Wed Sep 26, 2007 1:46 pm
by thewebdrivers
yeah i know, that would have been the best but there are some other restrictions that i cannot use the structure you suggested. Its a very big portal and we do not have time to implement the structure you suggested. Do you have any idea what can we do using existing structure? Can we use any stored procedure for this purpose?

Posted: Wed Sep 26, 2007 2:25 pm
by feyd
But you have the time to code all the manipulations and lookups needed to work with the far less flexible comma separation? Also note that the number of categories one may associate with an article will vary depending on the numeric length of the category ID.

FIELD() or IN() may work, but I can't recall specifically if they would or not in this case. You may need to use a regular expression.

Posted: Wed Sep 26, 2007 3:00 pm
by thewebdrivers
i have never heard of field function. what does it do? did you check that example query above with condition categoryids < 91. do you think we have a way of implementing that with current structure in place?

Posted: Wed Sep 26, 2007 3:48 pm
by feyd
It takes more work in our queries and code, but it's technically possible.

Posted: Thu Sep 27, 2007 1:11 am
by thewebdrivers
ok , i think i will ge coding a stored procedure for this. I do not have experience with stored procedures and i do not have access to mysql console. Phpmyadmin sql window gives me sql syntax errors. Is there any other way to create procedures?

Posted: Thu Sep 27, 2007 8:13 am
by feyd
Through code.

Posted: Thu Sep 27, 2007 8:21 am
by thewebdrivers
ok so i can create a stored procedure using php code and mysql_query function?