I have a table where I store products, and in the same table I store the ID of the category I want the product to be in.
My product table looks like this:
id | product_name | category_id | price
And then I have my category table:
id | category
My problem is to know how I can insert multiple categories into my category_id, and if it is possible.
If not, whats the best way that I could do it?
It is possible but you really don't want to go there. Storing multiple values in a single datarow column is a terrible idea in 99.99999% of the cases.
For more information, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
What you want to do is to add another table to store the relationship between the products and the categories. This is referred to as a many to many relationship.
This new table should hold the product id in one column and the category id in the other one, and have a composite primary key that is the combination of both these columns.
This way, you can have many products in the same category, and many categories for the same product.
You need a many to many relationship, which mean you need a third table let's call it ProductCategories, in this case you will have
Products
id | product_name | category_id | price
Category
id | category
ProductCategories
PruductID | CategoryID (combined PK)
