display results from two tables based on each other

[phpstarter123]

Ok, so I need to know how to query my database using php joining together some columns.  Table "products" has 3 columns I need, product, category, subcategory.  Table"categories" has 2 that I need, category, subcategory.

I would like for table "products" to display all the results for the specified category from "categories"

 

here is a visual representation

 

table"products"

product              Category            Subcategory

T-Shirt              retail                  none

water                retail                  none

uniform              retail                  uniforms

seminar            events                none

 

 

 

table"categories"

category            subcategory

Retail                none

retail                uniforms

events              none

 

 

so when it displays, they will display in buttons

there will be two main category buttons: retail and events

then there is one subcategory for retail: uniforms, as the table shows

then there will be the two products that have a category of retail that show under the retail button along with the subcategory.  then under the subcategory uniforms, the uniform would show

and under the category of events, seminar would show.  so in the categories table, if the subcategory is none, it ignores it, but if the subcategory is not none, then it looks to the category as to where it should be placed.  does this make some sense as to what I would like to do?

[phpstarter123]

I think what I need to use is some kind of join statement...

[btherl]

Yes you would use a join for this.  Are the values really in mixed case (sometimes "Retail", sometimes "retail") as you have there in your question?  If they are then that'll be a problem, but I'll assume they aren't.

 

SELECT p.product, p.category, p.subcategory
FROM products p
JOIN categories c ON (c.category = p.category AND c.subcategory = p.subcategory)

 

That is a standard join on two columns.  The results will include data from both tables, but only where both those columns match.

 

One thing I don't get - if you only want product, category and subcategory, these are all in the products table already.  There is no need to join with categories table unless you need additional data from the categories table.

[phpstarter123]

my finger hits the shift key sometimes, haha.  also, the categories made it easier so that only one of each was displayed.  I am going to add more products with the same categories and I thought maybe it would be easier to use a separate table so that only one had to be pulled, and there wouldn't be any multiple results.  does this makes sense?  how could I do it with only one lets say retail showing up while there are two retail items.  How could I do this?

[btherl]

I'm not sure if I understand you correctly but are you looking for this:

 

SELECT p.product
FROM products p
WHERE p.category = 'retail'
AND p.subcategory = 'none'

 

Plus you want the subcategories listed:

 

SELECT c.subcategory
FROM categories c
WHERE c.category = 'retail'
AND c.subcategory != 'none'

[phpstarter123]

I will message you an example page.

[phpstarter123]

actually I will just post a picture.

 

there are a lot more products than what i listed.  the red are main categories, blue is subcategories, and the green are the products.

this is using spry tabbed panels to do it, I don't want to do that, I would like for it to be buttons using show and hide stuff.  does this make more sense as to how it needs to appear.

[phpstarter123]

and I want it being dynamic, ie, the categories add themselves as more are added, currently, I have to manually go in and add the categories which is very time consuming, especially if I have to add many of them....

[phpstarter123]

so the categories table was for those main red categories, but if I could only use one table and not display duplicates, that would also be awesome!

[phpstarter123]

I guess this is a way I could kinda revise the first post maybe:

Ok, so I would like to find a way to dynamically display how the attachment looks:

 

Currently, I use spry tabbed panels to display this, and I have to manually edit the code to add categories. i would like it if I could dynamically have php make them for me. the red are categories, blue are subcategories, and green are products. I need the categories to display, then sub categories, then the products. I use tables "products" with columns product, price, category, subcategory, city. They city is for which store would have the specific products, all stores in the same city have the same products, that is why I used this. but if the category is hidden, I don't want category or product to display. if anyone can make something like this work with spry tabbed pannels, I will take it, or I can also make the categories buttons that will show and hide divs with the proper products, spry tabs may be harder to code with, but they are much easier with showing/ hiding(in my opinion). any help is appreciated!

[btherl]

What happened when you tried the queries I posted earlier?