Complex discount

[MA06]

Hi Guys,

I think this is a complex discount. I have 2 tables in mysql say product1 and product2, with the following characteristics:

[b]product1 table[/b]
p1id......price
1............20
2............30
3............40


[b]product2 table[/b]
p2id.......price........p1id
101.........10............1
102.........20............1
103.........70............1
201.........80............2
202.........6..............2
203.........4..............2
301.........10............3
302.........40............3
303.........30............3

where p1id is a foreign key in product2 table.

[b]Problem[/b]

[b]1)[/b] I have a calculate price function in php, what i want to do is if a person purchases any 2 items from product2 table with the same p1id key e.g. 101 + 102 or 101 +103 not 101 + 201, reduce the price of each of the 2 items by 10%.

[b]2)[/b] Also if they purchase 3 items with the same p1id then reduce the price of each of the 3 items by 15%.

Here is the calculate function i have, WHERE $IDS is a variable with the id of the purchased item. Th code just calculates the prices at the moment:
[code]
function calculate_price($cart)
{
  // sum total price for all items in shopping cart
  $price = 0.0;
  if(is_array($cart))
  {
    $conn = db_connect();
    foreach($cart as $IDS => $qty)
    {  
      $query = "select Price from Product1 where p1id='$IDS' union select Price from Product2 where p2id='$IDS'";

      $result = mysql_query($query);
      if ($result)
      {
        $item_price = mysql_result($result, 0, 'price');
        $price +=$item_price*$qty;
      }
    }
  }
  return $price;
}
[/code]

I am not sure how to apply these 2 discounts to the code, any help would be very much appreciated.

Thanks in advance,

MA.

[Barand]

If you have MySql 4.1+ you can use a subquery instead of the temporary table but this will work for all versions
[code]
$cart = array (
        101 => 2,
        102 => 1,
        201 => 1,
        202 => 1,
        203 => 1,
        302 => 2
);

$prodList = join(',', array_keys($cart));
$sql = "CREATE TEMPORARY TABLE tmp
        SELECT p1id, COUNT(*) as itemcount FROM product2
        WHERE p2id IN ($prodList)
        GROUP BY p1id";
mysql_query ($sql) or die (mysql_error());

$sql = "SELECT p.p2id, p.price, t.itemcount
        FROM product2 p INNER JOIN tmp t
        ON p.p1id = t.p1id
        WHERE p2id IN ($prodList)
";
$res = mysql_query($sql) or die (mysql_error());
while (list($id, $price, $n) = mysql_fetch_row($res)) {
       switch ($n) {
           case 2: $disc = 0.9; break;
           case 3: $disc = 0.85; break;
           default: $disc = 1; break;
       }
       $discounted = $price*$disc;
       echo "$id, $n, $price, $discounted<br>";
}[/code]