• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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
0
dimmergeek
Asked:
dimmergeek
  • 3
  • 2
1 Solution
 
Ray PaseurCommented:
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!
0
 
dimmergeekAuthor Commented:
I am looking for alternatives which utilize the existing structure.
0
 
Ray PaseurCommented:
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/
0
 
dimmergeekAuthor Commented:
... 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?
0
 
Ray PaseurCommented:
Sorry - this is a lot of work, and it's work that is required because the data base is mis-designed.  This would be a good point to hire a professional developer.  Show your developer this question and answer.  The developer will, for a price, be happy to build this for you, or perhaps even help you get the data base redesigned.  But it's not really a question with an answer -- it's a requirement for systems analysis, data analysis and application development.

Best of luck with it, ~Ray
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now