PHP / MySQL Query Help - JOIN?

[John_A]

As a learning project (with a view to a site I have in mind for the future) I'm building a site which has dynamically generated navigation links down one side. In the code a variable ($pagetype) is defined for which type of page is being viewed - e.g. infopages, class, colour, buyby or product.

Links on infopages are specific to the page and these are held in a separate table, which isn't a problem.

class is either: fruit or vegetable
colour: red, yellow, orange or green
buyby: single, box or bunch

Data for each product is stored in table "products" for example: -
[code]
|  product  | prod_url  | fruit | vegetable | red | yellow | orange | green | single | box | bunch |
-----------------------------------------------------------------------------------------------------
| Apples    | apple.php  |  1  |    0    |  1  |  0    |  0    |  1  |  1    |  1  |  0  |
| Oranges  | orange.php |  1  |    0    |  0  |  0    |  1    |  0  |  1    |  1  |  0  |
| Bananas  | banana.php |  1  |    0    |  0  |  1    |  0    |  0  |  1    |  0  |  1  |
| Lemons    | lemon.php  |  1  |    0    |  0  |  1    |  0    |  0  |  1    |  1  |  0  |
| Carrots  | carrot.php |  0  |    1    |  0  |  0    |  1    |  0  |  1    |  0  |  1  |
| Tomatoes  | tomato.php |  1  |    1    |  1  |  1    |  1    |  1  |  1    |  1  |  0  |
[/code]

When on a class, colour or buyby page, listing links to relevant products isn't a problem, I just pull everything direct from the "products" table. In the following example $pageid would be set to the products to list, say "fruit" : -
[code]if ($pagetype=='class' || $pagetype=='colour' || $pagetype=='buyby'){ // We're on a product listing page
//build and issue the query
$sql ="SELECT * FROM products WHERE $pageid = '1' ORDER BY product";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($menuitem = mysql_fetch_assoc($result)) {
echo '<a href="'.$menuitem['prod_url'].'" target="_self">'.$menuitem['product'].'</a>';
}
}[/code]

This works fine, when $pageid='fruit' the links shown are for Apples, Oranges, Bananas Lemons and (controversially) Tomatoes! Likewise for the other headings, for example I can list "Red Stuff", or "Available by the Box" etc.

However, I've hit a stumbling block when I want to generate the links when viewing a product page - I want the links to be for all the criteria pages we could possibly have come from (i.e. up one level). Apples for example, would show the following links: -

Fruit
Red Stuff
Green Stuff
Available Singly
Available by the Box

I've created another table which translates the column headings from the "products" table into the links to show, I've called this table "criteria" and it looks like this: -
[code]
| heading  | criteria |        linktext        |    linkurl    |
-----------------------------------------------------------------
| fruit    |  class  | Fruit                  | fruit.php    |
| vegetable |  class  | Vegetables            | vegetable.php |
| red      |  colour  | Red Stuff              | red.php      |
| yellow    |  colour  | Yellow Stuff          | yellow.php    |
| orange    |  colour  | Orange Stuff          | orange.php    |
| green    |  colour  | Green Stuff            | green.php    |
| single    |  buyby  | Available Singly      | single.php    |
| box      |  buyby  | Available by the Box  | box.php      |
| bunch    |  buyby  | Available by the Bunch | bunch.php    |
[/code]

So, basically, what I want to do is when I'm on the "Apples" page, make a query which, for each "1" found in the "Apples" row of table "Products", displays the link from the "criteria" table from the row with the matching heading! But I don't have a clue where to start, although I think I might need the word "JOIN" in there somewhere!

[fenway]

This is going to complicated because of the way you're designed the products table.  By classifying in columns, not rows, you can't join in the same row multiple times easily, which is where you're having the problem.  With your current design, the only way would be to get back all of the matching products, pull back the criteria table and store it in PHP, and then do the translation yourself.

[John_A]

[quote author=fenway link=topic=114732.msg466938#msg466938 date=1163359251]
This is going to complicated because of the way you're designed the products table.  By classifying in columns, not rows, you can't join in the same row multiple times easily, which is where you're having the problem. [/quote]
Nothing's set in stone, the products table can be changed as long as I can still list products by any criteria. But I can't see how it can be done any other way?

[quote]With your current design, the only way would be to get back all of the matching products, pull back the criteria table and store it in PHP, and then do the translation yourself.
[/quote]
I was thinking in the worst case, keeping the product table as it is and hard code a series of if statements, for example: -

if (fruit==1) echo 'fruit link'
if (vegetable==1) echo 'veg link'
etc. etc. for all the criteria (this is obviously just an example, the one I'm working in has nearly 40).

This would work but it's rather clumsy, is there a better way?

[fenway]

Why not have a table that stores a lookup table between classifications and products?

So instead of:
[code]
|  product  | prod_url  | fruit | vegetable | red | yellow | orange | green | single | box | bunch |
-----------------------------------------------------------------------------------------------------
| Apples    | apple.php  |  1  |    0    |  1  |  0    |  0    |  1  |  1    |  1  |  0  |
[/code]

You have just the first two columns in the 'products' table, and then a 'product_classifications' table with a like to product_uid (which is missing), the name of the classification (or classification_uid, if you want another table).  One row, one classification.

[John_A]

Thanks a lot for the ideas :)