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"];

Possible duplicate of: http://stackoverflow.com/q/7750208/829970 — Moshe Katz, Dec 26 '13 at 04:39
It would be easier if you put userid somewhere in your database, probably in your row NEW.userid — Andy Librian, Dec 26 '13 at 04: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, Dec 26 '13 at 04:41

score 0 · Answer 1 · answered Dec 26 '13 at 04:43

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 '13 at 04:43

Yuriah

25
8

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. – CaribeSoft Dec 26 '13 at 04:52
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(); } – Yuriah Dec 26 '13 at 04:58
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 !!! – CaribeSoft Dec 26 '13 at 17:59

score 0 · Answer 2 · answered Dec 26 '13 at 04:59

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.

How to get php session value into mysql trigger

2 Answers2