How to get php session value into mysql trigger

Question

How can I get a field value from php session (userid) to use it in mysql triggers

My Trigger :

 BEGIN
 IF NEW.nombre <> OLD.nombre THEN
        INSERT INTO socios_history (socio_id,tabla,campo, old_value, new_value,lstuser,updated) VALUES (old.socio_id,'usuarios','nombre',old.nombre,new.nombre,XXXXX,now());
    END IF;
 IF NEW.email <> OLD.email THEN
        INSERT INTO socios_history (socio_id,tabla,campo, old_value, new_value,lstuser,updated) VALUES (old.socio_id,'usuarios','email',old.email,new.email,XXXXX,now());
    END IF;
END

XXXXXX will be the userid from $_SESSION["userid"];

It would be easier if you put userid somewhere in your database, probably in your row NEW.userid — Andy Librian
– Andy Librian, Commented Dec 26, 2013 at 4:39
Sorry I forgot to mention that I'm running the trigger, from a web app, and the user is logged in and credentials save in php session — CaribeSoft
– CaribeSoft, Commented Dec 26, 2013 at 4:41

Yuriah · Accepted Answer · 2013-12-26 04:43:25Z

0

Im not exactly sure what you mean. It would be great if you could clean up your question a bit. From what im getting so far is that you want to retrieve a stored value from your session.

During the login you should have stored the users username into the session variable basically:

....mysql query checking for login
if login success
$_SESSION['userid'] = $username;

Then to retrieve it during your triggers make a variable for it

$userid = $_SESSION['userid'];

IF NEW.nombre <> OLD.nombre THEN INSERT INTO socios_history (socio_id,tabla,campo, old_value, new_value,lstuser,updated) VALUES (old.socio_id,'usuarios','nombre','old.nombre','new.nombre','$userid',now());

answered Dec 26, 2013 at 4:43

Yuriah

258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CaribeSoft Over a year ago

Hi, thank you Yuria, thats what I'm trying to do, I already insert this line "$userid = $_SESSION['userid']; into the Trigger but I'm receiving a Syntax Error.

Yuriah Over a year ago

You also want to make sure in the php file that you're running that there is an active session. To solve this problem usually you would have a functions.php and in it would be something like this:if (!isset($_SESSION)) { session_start(); }

CaribeSoft Over a year ago

Hi: I already resolve this issue, I add a new field on the users table: "updated_by" and then in the PHP script, where I update the table, I get userID from PHP Session, save on this new field and the trigger is this way : IF NEW.name <> OLD.name THEN INSERT INTO users_history (user_id,tabla,field,old_value, new_value,lstuser,updated) VALUES (old.user,'users','name',old.name,new.name,new.updated_by,now()); END IF; And it works for me !!!

cHao · Accepted Answer · 2013-12-26 04:59:39Z

0

You could conceivably set a variable in MySQL when you establish the connection. (I can verify that the SQL works; haven't tested doing it with PHP.)

$set_id = $pdo->prepare('SET @user_id = ?');
$set_id->execute([$_SESSION['userid']]);

And have your trigger use @user_id.

Note, this may act wacky with persistent connections.

answered Dec 26, 2013 at 4:59

cHao

87k21 gold badges147 silver badges178 bronze badges

Collectives™ on Stack Overflow

How to get php session value into mysql trigger

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related