Member Avatar for anthonyjpv

After INSERTing record(s) to payments_table (from paypal IPN),

$query = "SELECT sum(payments)
FROM payments_table
WHERE customer_id='{$customer_ID}'";

$result = mysql_query($query);

//get the value from result here

$query = "SELECT customer_id
FROM discounts_table
WHERE customer_id='{$customer_ID}'";

$result = mysql_query($query);

//if row > 1

//loop until reached a certain condition

THEN genarate a NEW discount code and insert record to discounts_table

Edited by anthonyjpv because: typo
  • 2 Contributors
  • 4 Replies
  • 121 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by anthonyjpv

Dani AI

Generated

Converting that PHP flow into a MySQL trigger is feasible, but there are a few practical points to keep in mind that and already touched on: generate a reliably unique discount code, avoid duplicate inserts, and test in the MySQL shell before deploying. A trigger can run AFTER INSERT on the payments table, sum the customer�s payments for the relevant period, check if a discount for that customer/period already exists, and insert a new discount if needed.

Here is a concise example trigger (adjust column names and types to match your schema):

DELIMITER //
CREATE TRIGGER payments_after_insert
AFTER INSERT ON payments
FOR EACH ROW
BEGIN
  DECLARE tot DECIMAL(12,2);

  SELECT COALESCE(SUM(p.amount),0) INTO tot
    FROM payments p
    WHERE p.customer_id = NEW.customer_id
      AND p.paid_at >= MAKEDATE(YEAR(NEW.paid_at),1)
      AND p.paid_at <  MAKEDATE(YEAR(NEW.paid_at)+1,1);

  IF tot >= 8000 THEN
    IF NOT EXISTS (
      SELECT 1 FROM discounts d
      WHERE d.customer_id = NEW.customer_id
        AND d.discount_year = YEAR(NEW.paid_at)
    ) THEN
      INSERT INTO discounts (discount_code, customer_id, discount_year)
      VALUES (CONCAT('D', LEFT(REPLACE(UUID(),'-',''),8)), NEW.customer_id, YEAR(NEW.paid_at));
    END IF;
  END IF;
END;
//
DELIMITER ;

Notes and cautions:

  • Add a unique key to prevent duplicates, e.g. UNIQUE(customer_id, discount_year) and/or UNIQUE(discount_code). This protects you from race conditions when two payments make the same threshold concurrently.
  • Index payments(customer_id, paid_at) to make the SUM fast.
  • Triggers run per row; bulk inserts will invoke the trigger multiple times. For complex code-generation or business rules, consider doing this in application code or a periodic job (MySQL Event) where logic and logging are easier to manage.
  • Test thoroughly on a copy of your schema and simulate concurrent inserts to confirm dedup behavior.

Recommended Answers

All 4 Replies

Member Avatar for smantscheff

Yes you can.
To be more explicit, show us the code for generating the discount code and the condition under which it should execute.
And before you code in PHP use the MySQL command line for testing until you know what you want to code.

Member Avatar for anthonyjpv

NOT TESTED

$transacion_id = mysql_insert_id();

//gets the customer id from payments table after the last insert
$query = "SELECT customer_id FROM payments WHERE transaction_id='{$transaction_id}'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$customer_id = $row['customer_id'];

//gets the customer id from discounts table after the last payment insert
$query = "SELECT customer_id 
FROM discounts 
WHERE customer_id='{$customer_id}'";
$result = mysql_query($query);
$count = mysql_num_rows($result);

$current_year = date("Y");

$start_day = 1;
$start_month = 1;

$end_day = 31;
$end_month = 12;

if($count == 0)
{
	//gets the total sum from payments table after the last insert
	$query = "SELECT SUM(amount) 
	FROM payments 
	WHERE customer_id='{$customer_id}'
	AND (date >= $start_day, $start_month, $current_year && date <= $end_day, $end_month, $current_year)";
	$result = mysql_query($query);
	$row = mysql_fetch_array($result);
	$total = $row[SUM(amount)];

	if($total >= 8000)
	{
                //some sort of discount code generation here

		$query = "INSERT INTO discounts 
		(discount_code, 
		customer_id)
		VALUES
		('{$discount_code}','{$customer_id}')";
                mysql_query($query);
	}
}

this is the php code that I wat to automate after inserting a payment to payments table

so basically, I want this to be converted into trigger

Edited by anthonyjpv because: n/a
Member Avatar for smantscheff

$query = "SELECT customer_id
FROM discounts
WHERE customer_id='{$customer_id}'";

This is tautological nonsense.

It is not clear how you deal with recurring customers. Do you give a discount for every 8.000 units? Or every time a customer buys something? Or only during a fixed period?

As I said before, if you have a function which gives a unique and reproducible dicount code for every incident and customer, you can set a unique index on the discount field and just insert all records which satisfy your discount conditions without checking for double entries. So your first task should be the design of this discount code generator.

Member Avatar for anthonyjpv

It makes sense if "I wanna know if theres an existing discount code for a certain customer by not selecting all columns" but anyway thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.