Solved

Dynamic product menu with categories

Posted on 2014-04-04
5
329 Views
Last Modified: 2014-07-09
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
Comment
Question by:dimmergeek
  • 3
  • 2
5 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 7

Author Comment

by:dimmergeek
Comment Utility
I am looking for alternatives which utilize the existing structure.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 7

Author Comment

by:dimmergeek
Comment Utility
... 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
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now