Jump to content
• Sign Up

#### Archived

This topic is now archived and is closed to further replies.

# Complex discount

## Recommended Posts

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.

#### Share this post

##### Share on other sites
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]

×

• Chat

## Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.