Category

[arunpatal]

Hi,

 

I want to make category for my products...

This is the product table look like now

 

$sqlCommand = "CREATE TABLE product (
 id int(11) NOT NULL auto_increment,
 product_name varchar(255) NOT NULL,
 price varchar(16) NOT NULL,
 details text NOT NULL,
 img1 text NOT NULL,
 img2 text NOT NULL,
 img3 text NOT NULL,
 img4 text NOT NULL,
 date_added date NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY product_name (product_name)
 ) ";

 

Now i will add one more field call category like this

 

category text NOT NULL,

 

now i want to make one page which shows product accordingly to category.

for example when i click on page computer.php then it shows only product

which have entry computer in category text

 

How can i do this??

Edited December 9, 2012 by arunpatal

[Andy123]

What you should ideally do is to keep your categories in another table and then make a foreign key from your product table to this table.

 

CREATE TABLE product (
id int(11) NOT NULL auto_increment,
-- other columns here,
category_id int NOT NULL

PRIMARY KEY (id),
CREATE INDEX idx_product_category ON product (category_id),
FOREIGN KEY category_id REFERENCES category (id)
)

 

You probably cannot create the index like that, but I forgot exactly where to put it. The idea is that you should add an index to the foreign key column and then the foreign key constraint. Make sure that your tables are using the InnoDB storage engine, because otherwise foreign keys will not be supported. Then, you can show computer products like this:

 

SELECT p.*, c.* FROM product p, category c WHERE p.id = c.id AND c.categoryName = 'computer'

 

If you really want to keep things simple and not use foreign keys or even a secondary table, it is slightly simpler to do. Be aware, though, that it is a poor approach and can give you headaches in the future depending on how large your project will be and how much maintenance will have to be done. I would discourage this approach because you never know which things will change in the future, and a good foundation is essential. Anyways...

 

Adding a simple text/VARCHAR column to your product table with the category name will enable you to simply do like this:

 

SELECT * FROM product WHERE category = 'computer'

 

This gives a lot of redundancy in the category column and you will also face a problem if you want a product to belong to more than one category. In this case, it would save you many headaches to use a secondary table, because then you could use a relation table (many-to-many) to connect a product to one or more categories. Such a table would have a foreign key to a product and one to a category, and one row would exist for each category a product is "connected to".

 

Hope it helps!

[arunpatal]

What you should ideally do is to keep your categories in another table and then make a foreign key from your product table to this table.

 

CREATE TABLE product (
id int(11) NOT NULL auto_increment,
-- other columns here,
category_id int NOT NULL

PRIMARY KEY (id),
CREATE INDEX idx_product_category ON product (category_id),
FOREIGN KEY category_id REFERENCES category (id)
)

 

You probably cannot create the index like that, but I forgot exactly where to put it. The idea is that you should add an index to the foreign key column and then the foreign key constraint. Make sure that your tables are using the InnoDB storage engine, because otherwise foreign keys will not be supported. Then, you can show computer products like this:

 

SELECT p.*, c.* FROM product p, category c WHERE p.id = c.id AND c.categoryName = 'computer'

 

If you really want to keep things simple and not use foreign keys or even a secondary table, it is slightly simpler to do. Be aware, though, that it is a poor approach and can give you headaches in the future depending on how large your project will be and how much maintenance will have to be done. I would discourage this approach because you never know which things will change in the future, and a good foundation is essential. Anyways...

 

Adding a simple text/VARCHAR column to your product table with the category name will enable you to simply do like this:

 

SELECT * FROM product WHERE category = 'computer'

 

This gives a lot of redundancy in the category column and you will also face a problem if you want a product to belong to more than one category. In this case, it would save you many headaches to use a secondary table, because then you could use a relation table (many-to-many) to connect a product to one or more categories. Such a table would have a foreign key to a product and one to a category, and one row would exist for each category a product is "connected to".

 

Hope it helps!

 

 

Thanks for showing the track...

I will be trying both and see which is easy for me to work with....

Edited December 9, 2012 by arunpatal