Forum Moderators: coopster
I am still quite new to php but could do with a "nudge" in the right direction. Not even sure if I can describe it clearly but here goes!
I have a list of widgets/prices in a MySql database. The widgets are available in any size [height x width] within a given limit [say 1m square]. There are a range of "standard" sizes and corresponding prices. Non standard prices can be calculated by taking the next standard size above and adding 20%.
To give you some idea, there are potentially 1.4 million possible size variations, the prices of which can all be calculated from 136 "standard" sizes/prices.
So, standard sizes/prices may look like this:
715 x 313 = $16
715 x 395 = $17.23
895 x 295 = $21.60
895 x 395 = $23.61
Height, width and price are stored in separate fields in the table.
My question is this: If a price is required for a widget 895 x 313, [no standard price] the price would be $28.33 [ie next size up plus 20%]. How do I search the database and when the size I require does not exist, find the next size above which does have a price?
I hope this makes sense! I just can't get my head around the principles involved.
Sorry if this is a little long winded.
Thanks in advance
Spook
SELECT price FROM table1 WHERE height='$height' AND width='$width'
When you get your result, do a mysql_num_rows. If the result is 0, you have to try again.
SELECT price from table1 WHERE height>'height' OR width>'width' ORDER BY price
This should give you the lowest price for the next size up. Add 20% and you're done.
Tom
mysql_num_rows is a new one on me but I have located it in the php manual and will "play around with it" today.
I will let you know how I get on!
Thanks again.
Spook
When looking for texts, you get things like
name LIKE '%widget%'
SELECT * FROM WIDGETS
WHERE
name ILIKE '%widget%'
AND
size < 20 AND size > 10
ORDER BY size DESC;
Then just fetch the topmost row from the result to get your most likely result. There are more powerful tools to do that, just read your database's manual carefully.
The following will give you the nearest, next higher price:
SELECT price FROM table1 WHERE height >= '$height' AND width >= '$width' ORDER BY price LIMIT 1
It will not, however, tell you whether or not it was an exact match and whether or not the 20% surcharge applies.
ergophobe, I think you meant
ORDER BY height, width:
$sql = "SELECT height, width, price FROM table1 WHERE height >= '$height' AND width >= '$width' ORDER BY height, width LIMIT 1";
Also, you could use the single query to the database and compare values upon return to determine price:
$row = mysql_fetch_assoc(mysql_query($sql));
if ($row['height'] == $height AND $row['width'] == $width) {
$price = $row['price'];
} else {
$price = $row['price'] * 1.20; // 20% markup
}
ergophobe, I think you meant ORDER BY height, width:
Actually, I meant price. I think both should ultimately give the same result.
715 x 313 = $16
715 x 395 = $17.23
895 x 295 = $21.60
895 x 395 = $23.61
if the user requests 736 x 250, then the result set is
895 x 295 = $21.60
895 x 395 = $23.61
Regardless of whether you order by price or dimensions, it's the same order. The question is whether Spook wants to bump up to the next closest size or the next closest price. Based on his example, this will always be the same. Say there are some exceptions,though, he would presumably want to give his customer the best price rather than cut that provides the least waste.
Also, you could use the single query to the database and compare values upon return to determine price:
That's probably the best solution. One query and then check it.
True, assuming the price increases with dimension.
Spook said: >>...and when the size I require does not exist, find the next size above which does have a price?
That's why I figured sort by size. And now that I read the statement again, I see something else in there. "...which does have a price...". We may need to add that to the query?
$sql = "SELECT height, width, price FROM table1 WHERE height >= '$height' AND width >= '$width' AND price > 0 ORDER BY height, width LIMIT 1";
Like I said, I'm relatively new to PHP, but this stuff seems second nature to you guy's.
I am trying to work through [and understand] what you are all saying. It may take a little longer, but don't think I'm not grateful for your advice.
Thanks.
Spook
technically...
$price = $row['price'] * 1.20; //is price + 20%
$price = $row['price'] / .80; // is a 20% markup
i.e. 20% markup usually means that if you sell the item you make 20% profit. So you must arrive at a figure 20% of which would be the difference between it and the cost.
Spook said: >>...and when the size I require does not exist, find the next size above which does have a price?That's why I figured sort by size. And now that I read the statement again, I see something else in there. "...which does have a price...". We may need to add that to the query?
Fair enough. I suspect it makes no difference, because price probably always increases with dimension.
My point is that if there are exceptions, you probably would not want the next size up as he said, you would want the cheapest alternative with those minimum dimensions, especially in a competitive bidding environment.
One other thing a little OT from the original quesiton. Will users always enter requests in the form "larger x smaller"? It seems like if they are just selecting dimensions from dropdowns or entering them in input boxes, you'll need to put it in that form:
if ($_POST['dim1'] > $_POST['dim2']) {
$height = $_POST['dim1'];
$width = $_POST['dim2'];
} else {
$height = $_POST['dim2'];
$width = $_POST['dim1'];
}
Coopster -
technically...
true. true. I'll try to watch my comments from here on ;)
Its taken me a while but having taken on board all of the above comments I now have a query [well two actually] which seems to work OK.
Maybe not the prettiest piece of code you have seen, but for anyone else in the same part of the learning curve as me, this is what I have:
{
$query = "SELECT * FROM product, product_price
WHERE product.product_height ='$height'
AND product.product_width ='$width'
AND product_price > 0
AND product_price.shopper_group_id= '8'
AND product.product_id = product_price.product_id
ORDER BY product_height, product_width
LIMIT 1";
$result = mysql_query($query)
or die("could not execute initial query.");
$base_price = mysql_fetch_array($result);
$message="<br> Exact Match";
}
if ($base_price == "0")
{
$query = "SELECT * FROM product, product_price
WHERE product.product_height >='$height'
AND product.product_width >='$width'
AND product_price > 0
AND product_price.shopper_group_id= '8'
AND product.product_id = product_price.product_id
ORDER BY product_height, product_width
LIMIT 1";
$result = mysql_query($query)
or die("could not execute query.");
$base_price = mysql_fetch_array($result);
$message="<br> Not Exact Match";
}
print_r($base_price[product_price]);
echo $message;
Many thanks for all of your advice.
Spook
Have fun!
Spook
