a tricky situation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thewebdrivers
Forum Commoner
Posts: 41
Joined: Fri Aug 17, 2007 3:32 pm
Location: india
Contact:

a tricky situation

Post 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
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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
thewebdrivers
Forum Commoner
Posts: 41
Joined: Fri Aug 17, 2007 3:32 pm
Location: india
Contact:

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
thewebdrivers
Forum Commoner
Posts: 41
Joined: Fri Aug 17, 2007 3:32 pm
Location: india
Contact:

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It takes more work in our queries and code, but it's technically possible.
thewebdrivers
Forum Commoner
Posts: 41
Joined: Fri Aug 17, 2007 3:32 pm
Location: india
Contact:

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Through code.
thewebdrivers
Forum Commoner
Posts: 41
Joined: Fri Aug 17, 2007 3:32 pm
Location: india
Contact:

Post by thewebdrivers »

ok so i can create a stored procedure using php code and mysql_query function?
Post Reply