Jump to content


Photo

Complex discount


  • Please log in to reply
1 reply to this topic

#1 MA06

MA06
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 21 April 2006 - 12:16 AM

Hi Guys,

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

product1 table
p1id......price
1............20
2............30
3............40


product2 table
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.

Problem

1) 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%.

2) 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:
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;
}

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

Thanks in advance,

MA.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 21 April 2006 - 11:58 PM

If you have MySql 4.1+ you can use a subquery instead of the temporary table but this will work for all versions
$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>";
}

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users