php- MySQL Trigger not fired after insert from php

Question

my problem is basically that the trigger (MySQL) is not fired after an INSERT data from a PHP function and I don't know why. The trigger fires normally when I insert data from MySQL Workbench.

Here is an example code for better understanding:

PHP function:

public function insert($data){
    $result = mysql_query("INSERT INTO Table(Column) VALUES('$data')");
    if($result){
       return true;
    }
    return false;
}

Trigger:

delimiter //
CREATE TRIGGER last_id AFTER INSERT ON Table
FOR EACH ROW
BEGIN
    SET @lastid = NEW.Id;
END;//

And finally when I execute this query from MySQL Workbench for example:

INSERT INTO Table(Column) VALUES(1)

the trigger fires normally.

NOTE: the PHP function also inserts the data successfully, the only problem is that it doesn't fire the trigger.

what I feel is you should use insert into Table(lastid) Values(New.id) instead of SET @lastid=New.ID — aroy, Mar 29 '17 at 04:13
@aroy Alejo already said that trigger executes when inserted from workbench. It means trigger has no issue — Nishant Nair, Mar 29 '17 at 04:14
I feel OP needs to upgrade to mysqli http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons?rq=1 — Louis Loudog Trottier, Mar 29 '17 at 04:15

score 0 · Answer 1 · answered Nov 29 '18 at 19:00

I was facing the same issue recently while working on a mini project for my Database course, after the research and headbanging sessions on MySQL terminal I found out that the variable(@lastid) that you are setting and updating are client instance variables only and its lifespan and scope is for that connection instance only. What really happens is that when you update the variable from the PHP you are actually using a different instance of the connection to the database than the connection instance of MySQL workbench. So actually your trigger on insert event executed by query through PHP is executing but you are not able to see the result because you are checking it from a different connection instance(Workbench instance).

To get clarity on this issue open two connections using the command line interface to the same database, set the variable from one instance and try accessing it from the other. You will get a null value on the second terminal.

Knowing all this now use this same connection you are using to insert the value through PHP to retrieve the temporary variable you are creating or updating.

    public function insert($data){
    $result = mysql_query("INSERT INTO Table(Column) VALUES('$data')");
    if($result){
       return true;
    }
    $result = $connection->query("select @lastid"); //create your own connection
    if(!$result) die($connection->error);
    $row = $result->fetch_array();
    echo $row[0];

}

This will probably give you your desired result as you are using the same connection instance variable.

php- MySQL Trigger not fired after insert from php

1 Answers1