Can you convert this to TRIGGER?

[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

[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.

[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

[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.

[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!