Member Avatar for anthonyjpv

Hi!

I dont know if this possible

I have a payments table in a database

what I want to accomplish is,

once a certain customer id has paid say $100 USD

my discounts table will generate a code for me automatically

say I'll insert a new record

is there such thing as automated insertion without me clicking anything?

thanks!

  • 2 Contributors
  • 2 Replies
  • 112 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by anthonyjpv

Dani AI

Generated

This thread is about generating a discount record automatically once a customer’s total payments hit a threshold. raised the requirement and pointed toward a DB-side solution. There are three practical approaches: a server-side DB routine, application (PHP) logic executed at payment time, or a scheduled batch job. Each has tradeoffs (atomicity, debuggability, hosting restrictions), so the examples below show safe patterns and pitfalls to avoid.

A MySQL trigger can do this atomically on insert. The pattern is: compute the cumulative total for NEW.customer_id, check if it crosses the threshold and whether a discount for that threshold already exists, then insert. Example (adjust column names and types to match the schema):

DELIMITER $$
CREATE TRIGGER payments_after_insert
AFTER INSERT ON payments
FOR EACH ROW
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT COALESCE(SUM(amount),0) INTO total FROM payments WHERE customer_id = NEW.customer_id;
  IF total >= 100 THEN
    IF (SELECT COUNT(*) FROM discounts WHERE customer_id = NEW.customer_id AND threshold = 100) = 0 THEN
      INSERT INTO discounts (customer_id, code, discount_value, threshold, created_at)
      VALUES (NEW.customer_id, CONCAT('DISC', LPAD(FLOOR(RAND()*1000000),6,'0')), 10.00, 100, NOW());
    END IF;
  END IF;
END$$
DELIMITER ;

An application-level approach using PDO gives more control and easier debugging. Use transactions, check totals after inserting the payment, make the discount insert conditional, and enforce uniqueness via a UNIQUE constraint on (customer_id, threshold) or on code. Minimal pattern:

$pdo->beginTransaction();
// insert payment
// SELECT COALESCE(SUM(amount),0) FROM payments WHERE customer_id = ?
// if total >= 100 and no discount exists, generate secure code (random_bytes) and INSERT into discounts
$pdo->commit();

Notes: add a UNIQUE constraint to prevent duplicate discounts, log automatic inserts, and handle hosting limits (some hosts disallow triggers). For reference: MySQL triggers and event scheduler docs (MySQL TRIGGERS, Event Scheduler) and PDO basics (PHP PDO).

Recommended Answers

All 2 Replies

Member Avatar for pritaeas

Perhaps a database trigger. You can write an update trigger, that gets activated when a record changes. Then you decide to insert a record if the amount is high enough. If this is what you want, I suggest you have this question moved to the MySQL forum.

Member Avatar for anthonyjpv

this is what im looking for. 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.