# Complex discount

### #1 MA06

MA06
• New Members
• 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.

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.