MySQL Triggers - How to capture external web variables? (eg. web username, ip)

Question

Hi,

I'm looking to create an audit trail for my PHP web app's database (eg. capture inserts, updates, deletes).

MySQL triggers seem to be just the thing -- but how do I capture the IP address and the web username (as opposed to the mysql username, localhost) of the user who invoked the trigger?

Thanks so much.

-Ken

P.S. I'm working with this example code I found:

DROP TRIGGER IF EXISTS history_trigger $$

CREATE TRIGGER history_trigger
BEFORE UPDATE ON clients
    FOR EACH ROW
    BEGIN
        IF OLD.first_name != NEW.first_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'first_name',
                        NEW.first_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;

        IF OLD.last_name != NEW.last_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'last_name',
                        NEW.last_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;

    END;
$$

Answer 1

You could add columns in the Clients table for IP Address and Web Username, updating those columns from your PHP, and then logging updates to those columns in the trigger the same way as changes to any other column.

But I expect you've already thought of that.