PHP link where one option includes 3 values from the same table column

Report · Nov 06, 2009

What I'm trying to do is set up a series of menu items for categories of merchandise (rugs), with subcategories for size. I've succeeded in making links where the user can choose ALL sizes within a category (by not specifying size as a parameter at all) and I've succeeded in making links where the user can choose a specific size within a specific category, but I'm not able to create links where the user can choose a specific size OR see ALL sizes.

Here's what I've got:

3 tables: products, categories & size
The size table has 2 columns - sizeName, and SizeID - the sizeID column is an integer and it links to products table
The categories table also has 2 columns - categoryName and categoryID - the categoryID column is also an integer which links with the product table

A menu with categories, and each category has a drop down menu where the viewer can choose a size.
Each size link has a parameter which includes the categoryID as well (in following example, all the sizes are within one category):
small - products.php?categoryID=8&sizeID=1
medium - products.php?categoryID=8&sizeID=2
large - products.php?categoryID=8&sizeID=3

On the receiving page (products.php) I created a recordset which looks like this (I'm looking at the recordset dialog box within Dreamweaver CS4):

SELECT price, `description`, categoryID, largeImage, smallImage, sizeID
FROM products
WHERE categoryID = colname AND sizeID = varSize
ORDER BY price ASC

the variables look like this:

Name: colname
type: Integer
default value: -1
Runtime value: $_GET['categoryID'}

Name: varSize
Type: Integer
Default value: 1
Runtime value: $_GET['sizeID']

(Dreamweaver created colname and I created varSize. I'm not really sure why the default values are as they are).

This works. But how do I create a link parameter for all three sizes? And how do I edit my recordset so it has a variable with all 3 sizes and how does that variable get written into the WHERE part of the code? Or, am I making this harder than it should be?

Any help would be greatly appreciated.

Thanks,
Dave

Report · Nov 06, 2009

Create a menu item "All Sizes". In your php script, test the value of the menu and if the selected item is "All Sizes", then don't include size in your where clause:

SELECT price, `description`, categoryID, largeImage, smallImage, sizeID
FROM products
WHERE categoryID = colname
ORDER BY price ASC

Report · Nov 06, 2009

I think I get what you mean - but I want to have both (an "all" option, or particular sizes) going to the same page. Is there a way to have a SELECT like the one I had before (specifying sizes) and a second part of the code that says basically "OR, if no size specified in URL" followed by what you set up:

SELECT price, `description`, categoryID, largeImage, smallImage, sizeID
FROM products
WHERE categoryID = colname
ORDER BY price ASC

How would that be written?

Thanks,
Dave

Report · Nov 06, 2009

>I think I get what you mean - but I want to have both

>(an "all" option, or particular sizes) going to the same page.

Of course.

>Is there a way to have a SELECT like the one I had before (specifying sizes)

>and a second part of the code that says basically "OR, if no size specified in

>URL" followed by what you set up:

The easiest way is to just have 2 different select statements. Test the condition of the size field and branch to the appropriate statement. This will work well for your example where you only have 2 possible select statements - one that includes size and one that does not. This technique can get messy if you are testing more conditions. In that case, it usually works better to dynamically construct the where clause string, testing the condition and appending the string as appropriate.

In your case, I would go with option A.

Report · Nov 06, 2009

Thanks for sticking with me here. I'm sorry for being clueless, but could you clarify for me a couple of things?

If I had two select statements, would the sql code be like this:

SELECT price, `description`, categoryID, largeImage, smallImage, sizeID
FROM products
WHERE categoryID = colname AND sizeID = varSize
ORDER BY price ASC

OR

SELECT price, `description`, categoryID, largeImage, smallImage, sizeID
FROM products
WHERE categoryID = colname
ORDER BY price ASC

Is that "OR" in the middle a legitimate way to code the combination of SELECTS statements?

Also, once I've set up a recordset, I know you can test the whole thing by hitting the test button, but is that what you meant when you said, "Test the condition of the size field and branch to the appropriate statement."

I was also wondering what you meant in your first reply when you said, "test the value of the menu."

Thanks for your patience. It's much appreciated