The Task: I need to easily enter numbers for stock and items sold in a backend. The shopping cart selection, etc. is not an issue.
Database structure: roughly,
products
id, title, .... etc.
options
id, prod_id, option_name, option_value . . . etc.
A simplified version of the selections:
select title from products where id=196;
print "$title\n";
sub-select*: select option_name,option_value from options where prod_id=196;
print "$option_name $option_value\n";
This yeilds
[product name]
Color Red
Color Green
Color Blue
Size Medium
Size Small
Size Large
As you can see, the options of color and size appear on separate rows. Where I need to get to from here is:
Color: Red Size: Small <input type="text" name="stock_field" value="">
<input type="text" name="purchase_field" value="">
Color: Red Size: Medium <input type="text" name="stock_field" value="">
<input type="text" name="purchase_field" value="">
Color: Red Size: Large <input type="text" name="stock_field" value="">
<input type="text" name="purchase_field" value="">
Color: Green Size: Small <input type="text" name="stock_field" value="">
<input type="text" name="purchase_field" value="">
Color: Green Size: Medium <input type="text" name="stock_field" value="">
<input type="text" name="purchase_field" value="">
Color: Green Size: Large <input type="text" name="stock_field" value="">
<input type="text" name="purchase_field" value="">
(and the same follows for blue, blue and sizes each on one line.)
The field names are not important at this point, if I can get past this hurdle I can make those unique and relate to the options.
The problem: If it's not evident by now, the issue is to get each possible combination of product options on a single line for the purpose of entering stock and purchases. "I sold three Red Mediums today and have two in stock." One line.
What I've Tried: The samples here are color and size, but this will vary and is user configurable. The number of options are unlimited, as well as the number of selections from the options, so enum and set data fields are useless in this scenario. I've tried various other database structures, as well as complex array structures to no avail.
This is one of those problems where I can see the solution, I just can't seem to connect the dots. :-(
Am I going about this all wrong, is there an easier way to go at it, any suggestions? Would greatly be appreciated, thank you.
* I know this can be done in one select but I've put it in two here just for simplicity here. The problem is not getting the data out of the database, it's getting the various option combinations to display on one line each.
sort by $color;
for each $color {
sort by $size;
for each $size {
print "Color: $color Size: $size <input type="text" name="stock_field" value="">
<input type="text" name="purchase_field" value=""><BR>\n";
}
}
Currently here's what I and a friend came up with (Also pseudo-select and code):
Select id,option_name,option_value from options where prod_id=[whatever] order by option_name,sequence;...
while (($oid,$oname,$ovalue) = $sth->fetchrow_array) {
if ($currentOption ne $oname) {
$currentOption = $oname;
$table_name = $currentOption . '_temp';
push (@tables, $table_name);
create temporary table $table_name (id,optionid,name,value)
}
insert into $table_name (values....)
}
So each time a new option is encountered, a new temp table is created and all the option values are dumped into the table determined by the "if". Ordering by option_name prevents duplicate tables. So for three options, this gives us three temp tables. Then,
foreach $t (@tables) {
$fields .= "$t.option_name,$t.option_value,";
$tables .= "$t,";
}
chomp $fields;
chomp $tables;
$select = qq¦select $fields from $tables;¦;
**NORMALLY you have to do a join on cross tables, as in
... where relational_table.main_table_id=main_table.id
to avoid pulling every record from the relational table when you only want matching ones. But in THIS case, this is exactly what we want - each row of each table pulls the values from the other tables, presenting every possible combination of options. SHAZAAM!
When done selecting and concatinating my output string, I clean house by
foreach $t (@tables) { drop table $t; }
... which is why I append the table names with _temp.
I eliminated a bunch of stuff here - for example, what to do if there are NO options - but this presents the central problem and **A** solution.
This does work and being an administration function, doesn't appear to have **too** much overhead, but I'm sure there's a better way and am still open to ideas. I still see it as a workaround! :-)
The only problem with that is the number of options vary as will the number of times the nesting must occur.A complex multidimensional array would work, just couldn't figure out how to vary the depth of the nesting in accordance with the number of options, and how to prevent duplicate rows.
To avoid dupes, I would try to nest with the "most unique" values in the outermost loops. ie- if ID_NUM is always unique, it is the outside loop. If ID_NUM_SIZE is next in uniqueness, (ie- it would have no other "parent" variables but would have "child" variables (like ID_NUM_SIZE_COLOR), it would be the next inner loop, followed by a loop for ID_NUM_SIZE_COLOR...
I realize you are writing this generic, but it should be possible to evaluate the table to see if one field has a related field that is dependent on an intermediary field. (easier to evaluate if it DOESN'T which will tell you if it does)...
I'm in a rush right now, but if you don't nail it with a truely generic solution (code that will accomodate any number of fields regardless of values), I will look at a script I wrote for a calendar --- it has something close that builds tables based on year, month, number of days in month and number of events for that date --- I actually build the "rows" as concatenated strings during the evaluation loops, and stores them in an array, then use a second set of loops to format the "rows" into screen output... I think your problem is similar.
