Dynamic Form Select logic

[dmacman1962]

Hi everyone,

I am trying to figure out the most efficient way of populating form select with MySQL data.

I have dynamic items that have unique quantities and prices.

IE

A Sign with a single price, and quantities of 1-100.

A business card with 250 = $25.00, 500 = $30.00, etc.

I am not sure how to handle items with unique quantities and pricing as such.

I currently have a table with the items and they have an ID that is what I use to select those items based the users login.

But, when I go to display them and build the SELECT FORM, I need to look up the quantities and prices offered for that item. Then build the statment to poplulate the SELECT options.

I am really looking for feedback on both the MySQL setup and PHP.

I currently have an item with a field called Quantity1, Quantity2, and so on.

I don't think this is the way most users o this board would handle that.

I appreciate the help,

Don

[sneakyimp]

so you have a database table of products, right? table 1.

and you (hopefully) have another db table that stores shopping cart contents for each user, right? table 2.

and you need to dynamically create a form that, when a product is going to be selected, you fetch the prices and display them with the appropriate HTML form elements, right?

you need to think through the pricing schemes you have...per unit price (where each is $x.xx no matter how many) is by far the easiest. you have one price per product and that's it. in this case, just store the price in the product table -- table 1.

if you have elaborate pricing schemes that involve price breaks for higher quantities, you either need a formula or you need a lookup table. you can encode a lookup table in a database table...this would be table 3. each product will have an id, you can associate a few records in this table 3 with each product by that product's id.

as for thinking through the logic, you need to do that yourself. i know that http://oscommerce.com has code that supports a variety of pricing schemes, but i just don't have the time or inclination to get download it, read the code, and give you the answer. maybe you should.

[wempy]

This sort of thing can get quite complex, unfortunately.

If I understand you, what you require is:

a table of products giving fields for name, description etc.

a second table of sizes with fields like size_name and actual_size (i.e. size_name could be "pack" and actual_size could be "1", or "box" and "100" etc).

You would then want a map table witch links the products to the appropriate sizes so if product id 1 can be bought singly, in a pack of 5 and in a box of 100 then the data in the table would look something like this:

Prod_id size_id
1 1
1 4
1 7

You would then need a fourth table of price/product/size mappings.

This is what I learnt as normalisation of data and if it is wrong can someone correct me please. As far as I have learnt you should only ever have exactly one piece of data in one field in a table, and this is my current solution to getting that.

[sneakyimp]

any given field in a table ideally would only have one kind of data because it makes it easier to do more with a query. other data types like timestamps and datestamps are a little different...you generally have to parse them a bit sometimes. there's no crime against putting multiple types of data in a single db field, you just have to be ready to parse out the data yourself.

but as i understand it, dmacman might want to do 2 different types of pricing.

one is a flat rate no matter what quantity. that's easy. if red t-shirts were $5 each, you might have a table with records like this

id=1
name='t-shirt'
color='red'
cost=5.00

suppose he also had blue t-shirts. they are overstocked so he's offering bulk discounts. since the bulk discount has several prices points, you are going to need a separate table. by convention, he could say that if the price for a product is empty then look in the price_break_table for the price breaks.

in the product table, a record might be:

id=2
name='t-shirt'
color='blue'
cost=

suppose that up to 9 t-shirts, they are $5. 10-19 s-shirts would be $4 and 20 or more would be $3. price_break_table needs to associate a quantity and cost with the blue t-shirt. price_break_table in that case might have 3 records that look like:

prod_id, count, cost
2, 1, 5.00
2, 10, 4.00
2, 20, 3.00

the tricky part now is setting up the database queries and store logic to decipher your price_break_table records for a particular item and figure out which record applies.