# Thread: How To: Pricing For This > 1-3 - \$3.99; 4-6 - \$2.99; 7+ - \$1.99 ?

1. ## How To: Pricing For This > 1-3 - \$3.99; 4-6 - \$2.99; 7+ - \$1.99 ?

Hello Folks, I need to find out how the following can be done for pricing products whereby customer has option of reducted costs based on the amount they buy...

[PHP]

PRODUCT Amt

Chicken Paste(Pckts) 1-3 4-6 7 or more

3.99 2.99 1.99

So if someone only buys one they'll be charged 3.99, although if they decide to buy 5, they'll be charged 2.99 each - that is to say, 2.99 * 5, if you follow ?

First, how can this be done from the database design point of view, and any ideas on how to script it ? The first question - database design - is more important though, the scripting I could proberly figure it out myself if needs be

Any help as always is welcome, thanks in advance.

2. Well how about a pricing matrix table, something like

Code:
`product_id, min_quantity, max_quantity, price`

3. Yes, but each group of quantities requires it's own price; I was maybe thinking that it can be done using ENUM ?

Either that, or set up a table giving each grouped price it's own row linked by product ID, and from script, specifiy the amounts to buy at that given price range ?

such as:

PHP Code:
``` # table STOCKproductId = 34raj493..# table PRICINGprodID = 34raj493grpOne = 3.99grpTwo = 2.99grpThree = 1.99  ```
Then the amount are done via script and set through a configuration variable ?

4. YEah well thats what I was thinking,
Code:
```product_id, min_quantity, max_quantity, price
1, 1, 3, 3.99
1, 4, 6, 2.99
1, 7, 100000, 1.99```
So you can just test the quantity to get the price, something like
Code:
```SELECT
price
from
matrix
WHERE
quantity
BETWEEN
min_quantity
AND
max_quantity
AND
product_id = \$prodid```
Or something like that.

5. Sounds like a good idea, although how would I then implement/design into database table the quantity ?

My idea would need to select all three rows solely based on the product id, and then from script, select the required row - your idea bypasses the first part altogether

So how would I know that the first price for example, be between 1 and 3 products to purchase ? ENUM would I think allow you to store 1 or more values within the one row I think - going by using ENUM in other technology...

Thanks for helping btw

6. Well you already know the quantity of the product, from the user inputting it, right? Well then you could either query the table based on product id and user quantity to retrieve the correct row's price. Which is only selecting one row. Or you could possibly load the matrix into say a javascript object, which would allow you to do the calculation on the client end. But I am not seeing why you would need to get all three rows, when you already have quantity and product_id.

7. If those are your real prices: Why would anyone buy 3 or 6? They save 0.01 by getting one more.

8. Well, that's the whole point of this. People are seduced to buy more.

9. tetranz - have you ever looked over a distribution catelog ?

Office equipment, for example... I have some script written to build an on-line shop dynamically and have a number of options you can select to use for pricing a product, describing a product etc.

This is an additional option I'd liked featured since it's a possibility that a client would want this, no ? More and more features generates more sales I say

freddy - I like your idea though wouldn't it mean that there would be 3 rows per product w/in the table ? - ie one per price....

Client side arithemetic isn't required (at the moment) but I can see me using your solution since it's more flexible than the solution I've thought off.

Thanks for the help thus far, and I'll get back if things end up the clyde w/out a paddle - 'glesga talk folks'

10. PHP Code:
``` "SELECT * FROM Products WHERE ProdID = 12233 AND '". \$Qty ."' BETWEEN Min AND Max"  ```
And some sample data:

PHP Code:
``` Products Table..ProdID Min Max PricePer12233  1    3    3.9912233  4    6    2.9912233  7    0    1.99.12244 .......  ```
Notice that I'd proberly use '0' for infinite amount of products - ie no limits. Freddy - is what you have in mind I wonder ?

11. Thats exactly what I had in mind.

12. Cool

13. What's that they say? Great minds think alike.... nah that can't be it... Can it?

14. Great ? My Mind ?

Not sure about that.... at the moment it's thinking... can I finish this beer

15. Just an additionally Note. If you had to change the Price for one of your Products, you must change all the "Quantity Prices" as well. So, I would recommend to store not the static prices, but rather the per cent in the quantity table.

16. Thanks Something to think about though it wouldn't be too much trouble to modify the priceing really, surely ?

What I mean is for percentages as you've said, you'd take say 15% off the original price for second group, then another 15% off again for third group for example ?

This means that the percentage reduction would need to be scripted into the shop it's self; even if the percentage value it's self (ie 15% or whatever) is DEFINEd elsewhere.

Thanks for bringing this up, but the client would only need to fill in 3 text boxes, so surely it isn't going to be that much of a hassle ?

Thanks for mentioning it anyways....

I'll give it more thought; maybe offer the option of priceing and/or percentage reductions I think ?

17. No Problem, isn't replying the point of an forum?!

It wouldn't be mouch trouble if you have a small amount of articles in your shop to organize and the prices for them won't change frequently.

Thinking as an product-manager, i see the following situation: I changed the price for one of my articles to an higher one and a day later I have a couple of orders for that article. Oh, they all ordered for the much lower quantity-price, thats what I call a hassle, because the customers won't be happy about that mistake, surely.

So, it is really no problem to fill out 3 text boxes - as long as it won't be overlooked. If there is an percantage solution (or a thing like: 2-5: "-1.99\$" / 6-8: "-2.99\$", so if the price change the lower price for different quantity keepes in relation with the "real" article price) that won't be happen.

And one thing about Quantitys in general: a customer who want to buy only one article will get deterred if he see that he can't buy it to the best possible price.. if you know what i mean. Man, i need some private english-lessons - for sure.

18. Thanks - I understand your last point about the customer deterred though this really isn't my problem, nor my concern either

I would like this functionality at the end of the day, and how it's used/who uses it and who has lost a sale I don't want to know....

I will not be managing any shop generated/administered by my software either so the product line(s) to me are unknown.

But I take your views into consideration.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•