Solved

Dynamic product menu with categories

Posted on 2014-04-04
5
350 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 110

Expert Comment

by:Ray Paseur
ID: 39979255
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
ID: 40013345
I am looking for alternatives which utilize the existing structure.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40013418
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
ID: 40059786
... 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 110

Accepted Solution

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

730 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