asked on

Dynamic product menu with categories

I am developing an e-commerce site with a mySQL backend.
The client will manage the db by adding categories and items.
I'd like to be able to create a single include file which will allow for a menu creation regardless of new categories or items added.

The current page is at http://69.195.124.65/~redtrail/

What I want to to replace the text "Products" with "Categories". When you expand categories you'll see what is currently listed. Then I want to be able to expand each category to see a listing of items in that category.

My DB structture is as follows:

I have attached the structure of my Categories and Products tables.
In the products table there is a field called "ProdCategories". This is what category(ies) and item should be under. Where thee is more than one, it is a pipe-separated list (1|3|20).

How can I write a query to group the products by category and then create the menu?
Catogories.JPG
Products.JPG

Ray Paseur

You could probably make this work, but I'd like to suggest a little different design for the data base tables. This is the part that puts "relational" into "relational data base."
http://en.wikipedia.org/wiki/Junction_table
http://en.wikipedia.org/wiki/Many-to-many_%28data_model%29

Each product is a an entity and each category is an entity. The relationships between these entities would be established in a junction table that held the CatID and the ProdID. Once you have this table, it will be very easy to write the query that lists all of the products in a given category and all of the categories that a given product belongs to. I don't have a simple example of this (I teach it, but it takes a morning to build and cross-reference the tables) but if you look at any basic book on PHP and MySQL you will find a lesson about it.

You would want to apply the same principles to CatKeywords, and if it exists to ProdKeywords. Make a junction table for keywords to relate each keyword to the appropriate category or product. You might also want to make a Google search for the exact phrase Should I Normalize My Database and read the very interesting arguments on the different sides of the question.

This will make your data base faster and more flexible, I promise!

dimmergeek

ASKER

I am looking for alternatives which utilize the existing structure.

Ray Paseur

I cannot imagine why you would want to utilize the existing structure, but if you insist on that, then you would want to run a query to CREATE TEMPORARY TABLE for the junction table that relates the categories to products in a one-to-many relationship. You would write a script that teased out the information in the ProdCategories column so that the pipe-separated list is turned into first a PHP array, then into a junction table. Once you have that junction table, you would run the queries normally, just as if you had a correctly structured and normalized data base.

If you're new to PHP and MySQL and want to get a foundation in how these things work together, this book is a good learning resource. Get the latest version that is available.
http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/

dimmergeek

ASKER

... then you would want to run a query to CREATE TEMPORARY TABLE for the junction table that relates the categories to products in a one-to-many relationship. You would write a script that teased out the information in the ProdCategories column so that the pipe-separated list is turned into first a PHP array, then into a junction table. Once you have that junction table, you would run the queries normally, just as if you had a correctly structured and normalized data base.

This sounds like what I am after. Can you help with the SQL?

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial