Page 1 of 1

table design & query problems ??

Posted: Wed Jan 23, 2008 3:52 am
by PHPycho
Hello forums !!
I am getting trouble in designing and queries some tables.
I am supposed to design a table which contains the prices of the products as per no
and i designed such table as
|id|product_id|qty_from|qty_to|price|
suppose it may contain values like
1| 122 | 1 | 99 | 100 |
2| 122 | 100 | 199 | 80 |
3| 122 | 200 | + | 70 |
In above table for product with id=122 has following values for the given ranges
1 - 99 = $100
100 - 199 = $80
200+ = $70

I dont know whether such table is fit for such or not. and i feel some difficulty in querying.
Case1:
Suppose if i would like to know the price of the product with qty = 250 , how should i perform such query as it lies in the 200+ range ?

Thanks for the suggestions in advance.

Re: table design & query problems ??

Posted: Wed Jan 23, 2008 4:01 am
by VladSun
You don't need the qty_from field
[sql] SELECT * FROM products WHERE $qty < qty_to AND product_id  = $id ORDER BY qty_to LIMIT 1[/sql]

And just add as the infinite quantity a very big number :)

Re: table design & query problems ??

Posted: Wed Jan 23, 2008 12:29 pm
by Christopher
You could also check the range:
[sql] SELECT * FROM product_qty_price WHERE product_id = $id  AND $qty >= qty_from AND $qty <= qty_to[/sql]
As VladSun said, you would need to always put some very large number where you currently have '*' (e.g.999999)