Mix value from two different rows [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 11 years ago.

Hi I have a table in my database for product disc开发者_StackOverflow社区ount which looks something like this:

id  product quantity NewPrice

On my website in php at the moment:

for each row:

Price per unit when buying $quanity or more: $NewPrice

But what I would like is:

Price per unit when buying $quanity1-$quanity2: $NewPrice1

Price per unit when buying $quanity2-$quanity3: $NewPrice2

Price per unit when buying $quanity3 or more: $NewPrice3

So basically I would like to take the quanity-value from two different rows to make up the text for the price from the first row.

If that makes sense?

---

Order the results by product, quantity and then loop over the results and compare with the previous row:

$lastRow = null;
$sql = "SELECT * FROM prices ORDER BY product, quantity";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
    if ($lastRow and $lastRow['product'] == $row['product']) {
        echo "Price per unit when buying ".$lastRow['quantity']." - ".($row['quantity']-1).": ".$lastRow['price']."\n<br>";
    } elseif ($lastRow) {
        echo "Price per unit when buying ".$lastRow['quantity']." or more: ".$lastRow['price']."\n<br>";
    }
    $lastRow = $row;
}
if ($lastRow) {
    echo "Price per unit when buying ".$lastRow['quantity']." or more: ".$lastRow['price']."\n<br>";
}

---

Just get all data from [discounts table] by [product column] sorted by [quantity column]

then extract [quantity column] values and work with array

$quantities = array(quantity0,quantity1,quantity2);
// $quantities[0] ... $quantities[n]

• Select from [discounts table] where [product column] = "someid" ORDER BY [quantity column] ASC;
• $quantities = array();
• foreach($row as $r){ $quantities[] = $r[quantity column name]; }

you will end up with quantities array, sorted from smallest to biggest
exact procedure depends on your database layer, php framework, and so..