Solved

Dynamic product menu with categories

Posted on 2014-04-04
5
346 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 109

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 109

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 109

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

831 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