Select from option list

[grandpa]

I've done pretty well for a novice. Last night I created MySQL tables for my parts and prices. Then I created the PHP files to display my data and make some choices.

Table_1 contains 2 fields, CODE and DESCRIPTION.
Table_2 contains 2 fields, PACK and PRICE.

Everything works pretty slick, I'm kinda proud actually. I'm outputting Table_1 into 4 columns, each is a link to another page (all link to somepage.php) and the link passes the CODE as a parameter. The next page then is where I can pick from any number of combinations of the item selected from Table_1.

For example, CODE and DESCRIPTION are AAA and AAA-Widgets, respectively. On the second page I have Fuzzy AAA-Widgets, available in 3 sizes, and I have Warm Fuzzy AAA-Widgets available in 6 sizes.

Here's my problem. The sizes are selected from a drop down box, and the size and price is hard coded in the select options of the drop down. I want to take the size option, and query Table_2 for the correct price.

I have opened Table_2, but I can't figure how to get my hard coded option into a variable to query the table. I've seen some similar code here in the forum, but got nothing to work yet. Can someone suggest the way to the light.

Thanks
grandpa

[ergophobe]

Something like this maybe?

$options = "";

$query = "select table1.size, table1.code, table2.price from table1, table2 where table1.code = table2.code";
$result = mysql_query($query);

while ($product = mysql_fetch_assoc($result)) {
$options .= "<option value=\"{$product['code']}\">{$product['size']} - {$product['price']}</option>";
}

[grandpa]

I'm not sure... looking at this solution, it appears to load everything from the price table (Table_2) into the options, and that's not a desirable result. The reason is that I my different widgets don't have the same prices.

Let me re-state that...

I've got AAA-Fuzzy widgets with prices in my table, but only 3 of the prices in that table are valid for those particular widgets. Likewise, only a few of the prices in my table are valid for the Fuzzy Blue Widgets. In no case is there situation where one widget would would have all the price options available to it.

I need to be able to parse the existing value out of my options string and then go get the correct price. But methinks this is lunacy....or just not possible today. Actually, it was suggested that I might use checkboxes or radio buttons for the size/price options... still thinking that over.

Short of that, I need to figure a better way to link my tables... sigh.

[ergophobe]

Naturally this is possible -it's what most e-commerce sites do.

table 1 - item
code
product_id
price
size
color

table2 - product
product_id
product_name
product_description

$query = "
select code, size, color, price
from item, product
where item.product_id = product.product_id";

The rest as per my script.

[grandpa]

Well, I'm gonna go get some sleep on this.

But I need to say my db tables are probably not going to
work... they have no common field.

Table 1:
Code
Description

Table 2
Size
Price

As you can see, there just isn't a common id between the
2 sets of data. To try to describe how the 2 sets of data *do* relate:

Code Desc Size Price
AAA Desc1 10 1.00
AAA Desc2 11 1.25
AAA Desc3 12 1.55
AAA Desc4 14 3.55
ABA Desc1 10 1.00
ABA Desc2 11 1.25
ACA Desc1 22 3.45
ACA Desc2 23 4.45

There's the problem. A code/desc group can be matched to any size/price group, but not every size/price is associated to a code/desc. Another way of saying it...
I have 400 code/desc items and 35 size/price options.

I could load up one table or the other with the last bit of
info to match the 2 tables, but if I did that I would end up with roughly 400 * 35 items in Table 1, so the optimization goes out the window.

I'm tired, I probably won't even be able to understand this later when I read it :)

grandpa

[ergophobe]

they have no common field.

Make them so.... you will definitely want this in the long run. You should probably read a few articles on "database normalization". That will help point you in the right direction.

[grandpa]

Thanks ergophobe.

I know that would make processing a *lot* easier.
I've got to give some thought about how to get the 2 tables to relate to each other.... with breaking what I have. And what I have is a product table and a price table, where a change-add-drop in either is a breeze. Maybe a 'group' code could be added, so all the products with a group 'A' class retrieve group 'A' prices.... (just thinking out loud).

I want to avoid the same situation I currently have with adding a new product to my accounting package, which is to add 35 items for each new item... because the item can have 35 different prices based on size, quantity, color.... it's tedious and not at all efficient for database processing.

[grandpa]

OK, I have a solution, but haven't figured out how to implement it yet. Perhaps someone could shed some light.

I need to load a js array with my Table_2 data - and that's where I'm having a problem. After reading and reading and reading more, I just don't get it.

So far I've managed to load my table data to an array,
$array, but can't get that into js-array.

Here's what I've got so far:

$query2 = "SELECT * FROM i_price";
$result2 = mysql_query($query2) or die("Query failed : " . mysql_error());
$line2 = mysql_fetch_array($result2, MYSQL_ASSOC);
$pprice = $line2['price'];
$ppack = $line2['pack'];
while ($line2 = mysql_fetch_array($result2, MYSQL_ASSOC)) {
$array[1] = $pprice;
$array[2] = $ppack;
}

It this now a js problem? If it is I'll go post something in that forum.

Thanks
grandpa

[ergophobe]

Sorry, but your PHP array won't magically appear in a Javascript array.

- PHP is server side and only those items you echo or otherwise send to the server are available
- Javascript is (usually) client side. If it's not visible in View Source (either as script or link to a script), it isn't there.

So, you need to use PHP to dynamically generate your JS, and then put that at the top of your page between <script></script> tags.

$js = "function make_options(){\n";
$js .= "document.write(" . $php_var_with_value_I_want_to_write . ");";
$js .= "}";

echo "<script type=\"text/javascript\">" . $js . "</script>";

Tom