Brain is melting

[tidusjar]

Hi guys,

So I've got a audit table in one of my mysql databases and currently I'm doing all custom auditing in my php code. Not ideal. I want to move towards using triggers, I want to be able to use these columns in the trigger:
newVal, oldVal, datetime, username.

Now it's the username which is getting me, I am not sure how I can send the username session variable out of php for a trigger. I don't think it's possible. So my question is, can I do it?

Currently I've set up some a stored procedure just to call from a PDO statement and pass the variables through, which works fine but I still have to call it in my code. If rather a trigger do all the work.

 

[planty]

It can be done. You can assign the username to a variable in mysql from php before your insert/update/delete is done. As long as you use the same mysql connection for both of those actions, the username variable will then be available in the trigger.

So for example, when your php page is loaded you'd do

//open connection first
...
mysqli_query($db, "SET @username = '$username' ");
...
//handle web request
...
mysqli_query($db, "insert into table...");


Then in your trigger, just use @username.

(Written on my phone so apologies for any syntax issues)

 

[tidusjar]

It can be done. You can assign the username to a variable in mysql from php before your insert/update/delete is done. As long as you use the same mysql connection for both of those actions, the username variable will then be available in the trigger.

So for example, when your php page is loaded you'd do

//open connection first
...
mysqli_query($db, "SET @username = '$username' ");
...
//handle web request
...
mysqli_query($db, "insert into table...");



Then in your trigger, just use @username.

(Written on my phone so apologies for any syntax issues)

Ah yes! Didn't think about that! More of a MSSQL person myself. Thanks.