Building variants from a list of options and values

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 862 Views - Last Post: 18 September 2013 - 10:46 PM Rate Topic: -----

#1 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Building variants from a list of options and values

Posted 05 September 2013 - 08:24 PM

I feel like this could have been something I was taught in a fundamental programming class, but either it's harder than it looks or it's been far too long since I took a fundamental programming class. I have a feeling the solution will have me slapping my forehead with an exclamation of, "Duh!"

I'm trying to build a table of product variants from a Zen Cart table of product options. Basically, I'll be assigning a productid to each possible product.

Every product has a different set of options, and each option has its own set of values. For instance, a product might have sizes of Small, Medium, and Large and colors of red, white, and blue. You could have a blue medium, or a red large, but you couldn't have a red blue or a large small. With two options of three values each, there are 9 possibilities and I need to generate a separate line for each.

The options are represented by options_id integers and the values are represented by options_values_id integers.

Given this table:
+-------------+------------+-------------------+
| products_id | options_id | options_values_id |
+-------------+------------+-------------------+
|           1 |          3 |                 5 |
|           1 |          3 |                 6 |
|           1 |          4 |                 1 |
|           1 |          4 |                 2 |
|           1 |          4 |                 3 |
|           2 |          3 |                 6 |
|           2 |          3 |                 7 |
|           2 |          4 |                 3 |
|           2 |          4 |                 4 |
|           2 |          5 |                 8 |
|           2 |          5 |                 9 |
+-------------+------------+-------------------+

I want to create an output of:
1 3 5 4 1
1 3 5 4 2
1 3 5 4 3
1 3 6 4 1
1 3 6 4 2
1 3 6 4 3 5 8
1 3 6 4 3 5 9
2 3 6 4 4 5 8
2 3 6 4 4 5 8
2 3 7 4 3 5 9
2 3 7 4 3 5 9
2 3 7 4 4 5 9
2 3 7 4 4 5 9

The columns in order are productid, optionid, valueid, optionid, valueid, optionid, valueid... repeated for however many options that product has. (The full table has about a hundred products and some of them have up to eight or nine options. So the output from this is going to be ridiculously long.)


The closest I've come is generating an output of:
1 3 5
1 3 6
1 4 1
1 4 2
1 4 3
2 3 6
2 3 7
2 4 3
2 4 4
2 5 8
2 5 9

I can't figure out how to structure the loops to make them combine.

Here's the program that generated the 3 column output above:
<?
include_once("ezsql/ezsql.php");
$products= $db->get_col("select distinct(products_id) from zc_products_attributes order by products_id",0);
foreach ($products as $product) {
     $options = $db->get_col("select distinct(options_id) from zc_products_attributes where products_id=$product order by options_id",0);
     foreach ($options as $option) {
           $values = $db->get_col("select options_values_id from zc_products_attributes where products_id=$product and options_id=$option order by options_values_id",0);
           foreach ($values as $value) {
                echo "$product $option $value\n";
           }
     }
}
?>

(Ezsql is just the mysql wrapper I use to simplify things. It's method of get_col just pulls a single column of data directly into an array.)

I hope I'm describing all this without being too confusing. To be honest, just getting to where I could define the problem even as succinctly as this has taken me way too long. I can't quite wrap my head around the logic I'm trying to come up with.

Any help would be very much appreciated.

Thanks,
w

Is This A Good Question/Topic? 0
  • +

Replies To: Building variants from a list of options and values

#2 Atspulgs  Icon User is offline

  • D.I.C Regular

Reputation: 68
  • View blog
  • Posts: 380
  • Joined: 29-July 09

Re: Building variants from a list of options and values

Posted 06 September 2013 - 01:22 AM

Im sorry, I dont think I quite understand. Your table only has 3 columns and you want to add extra ones? Are you asking how to setup your sql tables to accommodated unknown amount of option-value sets? Or do you want to somehow generate the extra option-value sets?
Was This Post Helpful? 0
  • +
  • -

#3 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Building variants from a list of options and values

Posted 06 September 2013 - 08:12 AM

I'm trying to generate a string for each set of options. It's confused by the option names and values being represented by id numbers, but it's like saying

"Widget: Color red, size small, shape square"
"Thingamajig: Size medium, Monogram Q, Material plastic, Capacity 2Gallons"

Different products may share an option name (size, in the example above), but they never contain the same option name twice in a single product (Color red Color Blue).
Was This Post Helpful? 0
  • +
  • -

#4 Atspulgs  Icon User is offline

  • D.I.C Regular

Reputation: 68
  • View blog
  • Posts: 380
  • Joined: 29-July 09

Re: Building variants from a list of options and values

Posted 06 September 2013 - 05:26 PM

Ok, but the part I dont get is, you have the same products id for multiple entries, and thats what youve also printed out. Where will you get the extra option-value sets from, if the table doesnt have them? Like youve printed out the whole table at this point, and you say that its not the final result. But where will you get the extra options from?
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3746
  • View blog
  • Posts: 13,116
  • Joined: 12-December 12

Re: Building variants from a list of options and values

Posted 06 September 2013 - 05:38 PM

I'm confused as well :dontgetit:. How does your raw-data containing 3 columns generate the desired output of up-to 7 columns?

Perhaps, as well as attempting to describe this again, you can post versions of your tables that substitutes the words that each id-number represents.

This post has been edited by andrewsw: 06 September 2013 - 05:40 PM

Was This Post Helpful? 0
  • +
  • -

#6 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 543
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: Building variants from a list of options and values

Posted 06 September 2013 - 05:56 PM

I see what you're trying to do, but I'm wondering if your database design is going to be effective in this regard.

For the others trying to help here, his idea seems to be that he wants one line for each possible permutation of product including its options (which, as the name suggests, are optional). The part of the original post that makes this confusing, however, is that some of the example output that he's looking for includes options not available on the products listed.

Based on the sample table above, the output should actually be something like this (if I'm understanding this correctly):

1 3 5
1 3 6
1 4 1
1 4 2
1 4 3
1 3 5 4 1
1 3 5 4 2
1 3 5 4 3
1 3 6 4 1
1 3 6 4 2
1 3 6 4 3
2 3 6
2 3 7
2 4 3
2 4 4
2 5 8
2 5 9
2 3 6 4 3
2 3 6 4 4
2 3 6 5 8
2 3 6 5 9
2 3 7 4 3
2 3 7 4 4
2 3 7 5 8
2 3 7 5 9
2 4 3 5 8
2 4 3 5 9
2 4 4 5 8
2 4 4 5 9
2 3 6 4 3 5 8
2 3 6 4 3 5 9
2 3 6 4 4 5 8
2 3 6 4 4 5 9
2 3 7 4 3 5 8
2 3 7 4 3 5 9
2 3 7 4 4 5 8
2 3 7 4 4 5 9

Does this sound correct? For others helping, does this make more sense?
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3746
  • View blog
  • Posts: 13,116
  • Joined: 12-December 12

Re: Building variants from a list of options and values

Posted 06 September 2013 - 06:07 PM

I suspected form the outset that the database design probably needs reviewing (output varying horizontally is a clue, and possibly having to refer to previous rows) but the question is still not clear enough for me.
Was This Post Helpful? 0
  • +
  • -

#8 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 543
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: Building variants from a list of options and values

Posted 06 September 2013 - 06:21 PM

+-------------+------------+-------------------+
| car         | option     | option_type       |
+-------------+------------+-------------------+
|        Ford |    spoiler |            Type A |
|        Ford |    spoiler |            Type B |
|        Ford |     stereo |          Phillips |
|        Ford |     stereo |              Bose |
|        Ford |     stereo |         Blaupunkt |
|   Chevrolet |    spoiler |            Type B |
|   Chevrolet |    spoiler |            Type C |
|   Chevrolet |     stereo |         Blaupunkt |
|   Chevrolet |     stereo |           Pioneer |
|   Chevrolet |   interior |             cloth |
|   Chevrolet |   interior |           leather |
+-------------+------------+-------------------+


Probably not the best conversion from IDs to words for an analogy, but hopefully that helps.
Was This Post Helpful? 0
  • +
  • -

#9 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Building variants from a list of options and values

Posted 10 September 2013 - 10:17 AM

Sorry for not responding to requests for clarification sooner - I've been out of town on unrelated business and couldn't back to this until now. (Until last night, actually, but my post form last night seems to have vanished.)

Thank you, Valek, for clarifying it so nicely. You almost nailed it. The only thing I would change from your description is that, despite the name, options aren't optional. The number of options associated with any given product might have differs from product to product, but a particular product will always use all its options. So, in the sample data given, product 1 strings will always have five numbers and product 2 strings will always have 7 numbers. The number of option/value pairs for a product is arbitrary - it could be a hundred, though it will rarely be more than 3.

Changing the database is, unfortunately, not an option. This is the out-of-the-box database that comes with Zen Cart. I'm integrating a new customer with my warehouse shipping system. They use Zen Cart, so I have to work with it. Unlike most shopping carts, Zen Cart doesn't allow you to assign UPC numbers to each variant of a product - it assumes that a master product description has a UPC and that all variants of that product have the same UPC. My warehouse system, on the other hand, requires a product code for each possible variant. I've checked in the Zen Cart forums, and while others have run against this problem nobody has published their solution to it. So I'm forced to create my own solution.

So I'll be creating a table that has a product_id field, an options_string field (the options string is what I'm trying to find a way to generate in this thread), and a UPC field which will be maintained in a separate interface.

Thanks,
w
Was This Post Helpful? 0
  • +
  • -

#10 Atspulgs  Icon User is offline

  • D.I.C Regular

Reputation: 68
  • View blog
  • Posts: 380
  • Joined: 29-July 09

Re: Building variants from a list of options and values

Posted 10 September 2013 - 10:47 AM

So you want to take the existing option- value pairs for the ford and generate the extra combinations for it.

Like continuing on from valeks example.

+-------------+------------+-------------------+
| car         | option     | option_type       |
+-------------+------------+-------------------+
|        Ford |    spoiler |            Type A |
|        Ford |    spoiler |            Type B |
|        Ford |     stereo |          Phillips |
|        Ford |     stereo |              Bose |
|        Ford |     stereo |         Blaupunkt |
+-------------+------------+-------------------+



So you want to turn that into

That...???

+-------------+------------+-------------------+
| car         | option     | option_type       |
+-------------+------------+-------------------+
|        Ford |    spoiler |            Type A |
|        Ford |    spoiler |            Type B |
|        Ford |     stereo |          Phillips |
|        Ford |     stereo |              Bose |
|        Ford |     stereo |         Blaupunkt |
|        Ford |    spoiler |            Type A |      stereo |           Phillips |
|        Ford |    spoiler |            Type A |      stereo |               Bose |
|        Ford |    spoiler |            Type A |      stereo |          Blaupunkt |
|        Ford |    spoiler |            Type B |      stereo |           Phillips |
|        Ford |    spoiler |            Type B |      stereo |               Bose |
|        Ford |    spoiler |            Type B |      stereo |          Blaupunkt |
+-------------+------------+-------------------+

This post has been edited by Atspulgs: 10 September 2013 - 10:49 AM

Was This Post Helpful? 0
  • +
  • -

#11 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Building variants from a list of options and values

Posted 10 September 2013 - 12:01 PM

Almost.

I want to turn
+-------------+------------+-------------------+
| car         | option     | option_type       |
+-------------+------------+-------------------+
|        Ford |    spoiler |            Type A |
|        Ford |    spoiler |            Type B |
|        Ford |     stereo |          Phillips |
|        Ford |     stereo |              Bose |
|        Ford |     stereo |         Blaupunkt |
+-------------+------------+-------------------+

into
+----+-------------+-------------------------------------------+
 id  | car         | option_string                             |
+----+-------------+-------------------------------------------+
| 1  |        Ford |    Ford spoiler Type A stereo Phillips    |
| 2  |        Ford |    Ford spoiler Type A stereo Bose        |
| 3  |        Ford |    Ford spoiler Type A stereo Blaupunkt   |
| 4  |        Ford |    Ford spoiler Type B stereo Phillips    |
| 5  |        Ford |    Ford spoiler Type B stereo Bose        |
| 6  |        Ford |    Ford spoiler Type B stereo Blaupunkt   |
+----+-------------+-------------------------------------------+


I don't need help getting it into the database, I just need help iterating through the options to get the strings. So, for the purposes of this post, I guess it would be more accurate to say I want to turn it into:

$optionstr[]= "Ford spoiler Type A stereo Phillips";
$optionstr[]= "Ford spoiler Type A stereo Bose";
$optionstr[]= "Ford spoiler Type A stereo Blaupunkt";
$optionstr[]= "Ford spoiler Type B stereo Phillips";
$optionstr[]= "Ford spoiler Type B stereo Bose";
$optionstr[]= "Ford spoiler Type B stereo Blaupunkt";


w

This post has been edited by wzeller: 10 September 2013 - 12:03 PM

Was This Post Helpful? 0
  • +
  • -

#12 Atspulgs  Icon User is offline

  • D.I.C Regular

Reputation: 68
  • View blog
  • Posts: 380
  • Joined: 29-July 09

Re: Building variants from a list of options and values

Posted 10 September 2013 - 01:08 PM

Yeah, its kindof a tricky problem.

My first idea was and still is to put the whole thing into an array structure.

I might try to generate the wanted result that way maybe tomorrow or later tonight, right now I dont think I can :D
So let me get back on this one.

This post has been edited by Atspulgs: 10 September 2013 - 01:16 PM

Was This Post Helpful? 0
  • +
  • -

#13 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Building variants from a list of options and values

Posted 10 September 2013 - 02:11 PM

It would certainly make my day if somebody could figure it out.

Loading the entire initial table into an array and manipulating the data from there might be the easiest way to go. For customers with a lot of products this might not be the best way to go, though, because it could easily be huge. With this particular customer that wouldn't be a problem, and I'd be fine going that way. But the first time we get a new customer using Zen Cart for a large set of products I might be back here again. (Though the logic would probably be the same so I might be okay.)
Was This Post Helpful? 0
  • +
  • -

#14 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 543
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: Building variants from a list of options and values

Posted 10 September 2013 - 02:21 PM

I'm about to head out from work, but I'll take a crack at it when I get home.
Was This Post Helpful? 0
  • +
  • -

#15 Atspulgs  Icon User is offline

  • D.I.C Regular

Reputation: 68
  • View blog
  • Posts: 380
  • Joined: 29-July 09

Re: Building variants from a list of options and values

Posted 10 September 2013 - 06:15 PM

Yeah for a big data set sure arrays wont be the best option unless you limit the data sets. Like taking 50 records at the time, rinse and repeat kinda thing. You were gonna make a new table from that anyways right? so 50 read, sorted, dealt with, then store it in your table, clear out your memory and repeat the process until nothings left to deal with. If you can cook up a way to do this with sql an some loops straight off the bat, thats already cool and id like to see that.

That is an idea though, you could simply go row by row and generate the output, store it and repeat. that way, no need for an array. Actually the more I think about it, the more I like the idea of not using the array. Just get the data fro the id, sort it in a way that lets you generate your desired result, throw it in the database.... just keep going row by row.

Yeah, I kindof like this problem, Its exciting :) I have to sleep on this one a bit and then see what I can come up with tomorrow.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2