FN-FORUM: Wednesday plea for help with MySQL
date posted 17th January 2007 09:47
Good Morning.
Right, I have asked before and some of you on here have been really
good to me, so I am hoping you will be able to bail me out again. I am
in over my head with this, and although to someone who knows what they
are doing this shouldn't take too long, I am prepared to pay if this
goes beyond the realms of a "favour".
I am building an online shop, which has been easy peasy, and have
built some before. the problem is that other than your average
products, the client now has kits, which you can build yourself,
adding a specified number of items from each of 3 lists to make up the
correct quantity. I have tried to explain the db structure as best I
could, so if anyone is feeling like being really generous this
morning, I would love to hear from you!.
Duncan
First off, let me "introduce" my database structure, and for each
table the important fields we are likely to be needing.
"Products"
productid - primary key
"prodVariants"
variantid
productid
So, relationship between product to product variant is one to many.
The "prodVariants" table exists because a product (say some wine, for
sake of example!) can come in different quantities/variants (such as
75cl, 2L, 3L, 10L)
"Kits"
kitid - primary key
kitAnum
kitBnum
kitCnum
"KitVariants"
kitvarid - primary
kitid
"KitLists"
listid - primary
listname
"listVariants"
listid
variantid
So, relationship between "kits" and "kitVariants" is one to many. Kits
is where you will get a box, and be able to build your product, by
selecting a number kitAnum of items from List A, kitBnum of items from
List B, kitCnum of items from List C.
"KitLists" simply stores the listid, and list name... and the system
that knows to match the number of items kitAnum with the listname
called List A from "KitLists" is hard coded as there will only ever be
3 lists.(clumsy I know, couldn't see another way of doing this).
Finally, the relationship between "KitLists" and "listVariants" is one
to many. Here this table stores the variantids which are allowed in
each of the lists.
What I am trying to achieve is when the visitor to the site selects a
kit (by picking a KitVariant), a page opens, which for each of the 3
lists has two boxes with an add and remove between each. In one box,
all the variants that are assigned as "allowed" for that list will be
listed as possibles. Selecting one and clicking on the add will move
it across to the selected box. Clicking remove will obviously remove
it. A counter will also keep track of how many items from each list
have been added, and not allow more than the kitAnum, kitBnum and
kitCnum specified for the kit to which the kitvariant they selected
belongs to.
The end result will be the 3 lists of variants selected from each to
form the kit.
I hope this will make sense to someone!
Duncan
