Adding trigger to MySQL by PHP

Question

I use PhpMyAdmin to run the following SQL statement.

CREATE TRIGGER `insert_channel_test2` AFTER DELETE ON `channel_test2` FOR EACH ROW INSERT INTO `tv`.`LOG_test2` (`table_name`, `table_action`) VALUES('channel_test2', 'delete');

It works successful, but when I use the PHP webpage to query above code, it occurs Fatal error.

$query = "CREATE TRIGGER `insert_channel_test2` AFTER DELETE ON `channel_test2` FOR EACH ROW INSERT INTO `tv`.`LOG_test2` (`table_name`, `table_action`) VALUES('channel_test2', 'delete');"
$this->link = new mysqli( DB_HOST, DB_USER, DB_PASS, DB_NAME );
$full_query = $this->link->multi_query( $query );

And the error messages is following.

 Commands out of sync; you can't run this command now

Does everyone knows what the problem is? Thanks!

Permission error perhaps, try actually printing the error message.. — hank
– hank, Commented Dec 17, 2013 at 9:10
What is the actual error from mysqli that you can get with mysqli_error? What you posted is your custom error message, that shows the time and the query which is nine, but doesn't contain the real error message from MySQL. — peterm
– peterm, Commented Dec 17, 2013 at 9:19
sorry, the following error message is produced by mysqli_error. Commands out of sync; you can't run this command now — Andy
– Andy, Commented Dec 17, 2013 at 9:22

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

As per MySQL:

C.5.2.14. Commands out of sync If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

Refer to:

MySQL: Commands out of sync
One of my answers to a similar query on SO: getting error in calling stored procedure

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 17, 2013 at 9:47

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

Comments

Andy · Accepted Answer · 2013-12-17 09:58:03Z

1

Thanks Everybody. I modified my code to following, and fix the problem.

public function multi_query( $query )
{
    $query = $this->link->multi_query( $query );
    $this->clear_next();
    if(  mysqli_error( $this->link ) )
    {
        $this->log_db_errors( mysqli_error( $this->link ), $query, 'Fatal' );
        return false;
    }
    else
    {
        return true;
    }
    mysqli_free_result( $query );
} 

public function clear_next()
{
    while($this->link->more_results())
    {
        $this->link->next_result();
        if($res = $this->link->store_result())
        {
            $res->free(); 
        }
    }
}

answered Dec 17, 2013 at 9:58

Andy

4172 gold badges14 silver badges30 bronze badges

Collectives™ on Stack Overflow

Adding trigger to MySQL by PHP

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related