table design & query problems ??

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.

rulian

first, i would suggest you formulate rather then create ranges for prices in quantity, but given this is your task some help here

to find a number between a range you would do

SELECT * FROM mytable WHERE qty_from < '$mynum' AND qty_to > '$mynum'
or use a BETWEEN which works the same way

so your 200 + cannot be a plus because + is not a number
if you are sure nobody will order mroe then 999 billion of the product, you can put the the range from 200-999,999,999,999
so when somebody looks for 200+ it will fall withing that range
to give you an idea,

bogu

After 2 min of thinking this is how I would change your database table structure:

CREATE TABLE IF NOT EXISTS `table` (
  `qty_from` int(11) unsigned default NULL,
  `qty_to` int(11) unsigned default NULL,
  `price` decimal(10,2) unsigned NOT NULL default '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is how your records should look like:

1| 122 | null | 99 | 100 |
2| 122 | 100 | 199 | 80 |
3| 122 | 200 | null | 70 |

And this should be the query:

SELECT
	*
FROM `table`
WHERE
	IF(qty_from IS NOT NULL, IF(qty_from<250, true, false), true)
		AND
	IF(qty_to IS NOT NULL, IF(qty_to>250, true, false), true)

Something liike this ....

rulian

if not that, try this simple measure

SELECT * FROM table WHERE quantity_from < '$userprice' AND product_id= '$userproduct' ORDER BY quantity_from DESC LIMIT 1

that will always return the highest value matching the user requests
so if they ask for 175 it will return 100 or price of 80
if they entered 200 it would return quantity of 200, price 70
if they entered 300, then it would still return 200, price 70