current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Not sure what to search for when solving this puzzle. Not asking for the world... just direction unless you're feeling generous. This is going to be terribly hard to expain.

My website has dropdown menus with measurements. When selecting width x height dropdowns, it checks database for price. Currently widths and heights were specific in the DATABASE such as 24, 28, 32, 36. So I just pulled the measurements from a query loop into my list... and voila it matches and echos a price.. but..

Now I'm finding out that the measurements listed will need to be in 1" increments. So, I only have a database with specific measurements but I need the entire measurement range.

Do I really have to go into the DATABASE and manually enter the SAME price for 24", 25", 26", 27"... and SAME price for 28", 29", 30", 31", ... and do this time hundreds of products?

Or is there a SQL trick that allows me to select the minimum and maximum measurement, list them in a dropdown, then match one of those 1" increment measurements that isn't exactly what the DATABASE says... but is between (for example) 24" and 28".

Hope this isn't too confusing and THANK YOU.

Thanks to Gordon this was much easier than anticipated. I made it more complicated than it should have been.

Code that worked:

SELECT * FROM price_dimensions WHERE prodid = '".$_POST['prodid']."' AND height >= '".$_POST['heightX']."' AND width >= '".$_POST['widthX']."' ORDER BY height ASC, width ASC LIMIT 1
share|improve this question
    
I feel like it has something to do with the SQL BETWEEN condition but it's opposite of that because the two variables BETWEEN x and y... is where x and y are in the database list... but the entire range is in the <select><option> listing.. sorry if my lingo isn't correct. –  LITguy May 23 '12 at 20:12
    
If you have prices for 24x28 and 28x24, and want to sell a 22x22, which one would you pick? –  Quassnoi May 23 '12 at 20:34

2 Answers 2

up vote 1 down vote accepted

I think you can just insert the rows you want. Here is some pseudo code:

insert into t
    select t.width+1, price
    from t

insert into t
    select t.width+2, price
    from t

insert into t
    select t.width+3, price
    from t

If the prices are really the same, you might be able to just query for nearest value, less than or equal to or greater than or equal to the width given.

share|improve this answer
    
Gordon, it worked based on your idea of simply using query for nearest value, less than or equal to or greater than or equal to the width given. –  LITguy Jun 21 '12 at 0:59
up vote 1 down vote

It does have something to do with between, as long as you change your table structure. Your problem is basically that you have a single value and you need to find out which two values it's between.

This, in turn, implies that you need to store two values in your database, the minimum value and the maximum value. Taking your example your table might look something like this:

+------------+-----------+-----------+-------+
| product_id | min_value | max_value | price |
+------------+-----------+-----------+-------+
|          1 |        24 |        27 | 75.13 |
|          1 |        28 |        31 | 83.55 |
+------------+-----------+-----------+-------+

You would need a unique constraint on product_id, min_value. You would also need to ensure that for each product_id there existed no max_value less than of equal to any min_value for that same productid.

Your query to select your data then becomes something like:

select price
  from my_table
 where product_id = :product_id
   and :value between min_value and max_value

As a slight aside you probably didn't need to loop before. Always do something in a single query if at all possible.

share|improve this answer
    
I see what you mean. I may as well go in and manually enter these. The client gives me a data matrix where along the top row it has widths listed... such as 20, 22, 26, 30, 34, etc... up to 80 inches sometimes. Left column has same thing listed vertically. All the measurements are in there and I looked up how you can go into Excel and do a reverse pivot table to get it into a listing, then import into MySQL database. I'm really lost now as there are hundreds of products already setup as different matrices like this. Thank you for trying though. –  LITguy May 25 '12 at 8:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.