Need help to categorise the data

Hello Friends,

I created a MySQL database containing data as,

product_id with data - 1200, 1201, 1202.....
product_name with data - product1, product2, product3......
all_category_id(varchar75) with data - (3, 2), (1, 15, 2), (3, 7, 9) ....(two or more category id as varchar. since, many products have more than one category)

Every category ID has a name e.g
1 - arts
2 - entertainment
3 - Games
15 - movies

I'm trying to pull the product list category wise e.g.
select * from tablename where category=1(that is arts)

I used "regexp" but not working perfectly, e.g.
when i say,
select * from tablename where field regexp '.1.';

it returns, products from arts and also movies category as it contains "1" in its ID "15" But I want the list of only arts

So can anyone suggest me solution to get the desired result? I don't want to change the database structure

Thanx in Advance

 

The solution is to change the database structure, or parse out the categories within the application and not in the query. Storing data like this is called denormalization, and undermines the reason you would use a relational database.

 

Can you tell in more detail please jestep?

 

I wrote the below answer before I read this line of your post:

After reading that, the answer is simple: No.


~~~~~~

Here's a tutorial on data normalization: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Hopefully what you have now is 2 tables--Products (product_id, product_name, all_category_id) and Categories (category_id, category_name). What you need is to break off all_category_id from the Products table and create a 3rd table that links the ids from both tables. Call this new table ProductCategories and it will have 2 fields (product_id, category_id).

So if you had this record in Products:

product_id, product_name, all_category_id
7, "Sample Product", "1,2,3"

You would remove the all_category_id field and place its data in the ProductCategories table as 3 new records (one for each category). Along with it you would place the value in the product_id field. So the 3 new records in the ProductCategories table looks like this:

product_id, category_id
7,1
7,2
7,3

Using that table you can now link your Products to their many Categories.