0

I am trying to create a trigger into mySQL using PHP

mysqli_select_db($host, "game");
$sql = "CREATE TRIGGER test_seven BEFORE INSERT ON test FOR EACH ROW INSERT INTO test (name) VALUES ('test')";
$query = $host->prepare($sql);
$query->execute();

And it is not working even though I tried running it on phpMyAdmin and it worked.

EDIT: "This command is not supported in the prepared statement protocol yet" Is there a way of skipping the prepare statement then ?

Johny
  • 21
  • 3
  • I also faced the same issue , for procedures / tiggers, still dont know why it does not allow, but my guess is , its due to user rights, in phpmyadmin user has diffrent rights/ privelges and when you do some thing with code it has diffrent priveleges, even though the user are same. Not sure if its correct or not – Purushottam zende Feb 05 '18 at 12:29
  • "not working" means what? What error do you get? Are you even checking for mySQL errors in your PHP code? Does the user which PHP is logging in with have rights to do this? – ADyson Feb 05 '18 at 13:04
  • @ADyson "This command is not supported in the prepared statement protocol yet" Is there a way of skipping the prepare statement then ? – Johny Feb 05 '18 at 13:11
  • https://stackoverflow.com/questions/3762880/create-mysql-trigger-via-php – GolezTrol Feb 05 '18 at 13:12
  • 1
    Don't execute it as prepared statement. This is an admins job, not a programmers. But apart from that, I doubt that the statement worked, like you say. Your trigger would create an infinite loop and is therefore not allowed. Your insert statement would fire the trigger, which would fire the trigger, which would fire the trigger...and so on. – fancyPants Feb 05 '18 at 13:16
  • Well I don't know what Db library you're using but I would think you can run a raw query instead of a prepared statement. Just make sure it's not open to SQL injections – ADyson Feb 05 '18 at 13:17
  • @GolezTrol Thanks ! It works now. – Johny Feb 05 '18 at 13:17
  • @fancyPants It worked after I bypassed the prepare statement and yes you are right, I was just trying to make a quick trigger so i can see if it can create it. I will not use it, i'm going to drop it. Thanks anyway ! – Johny Feb 05 '18 at 13:20

1 Answers1

1

Apparently the real problem was the fact that I was not supposed to use the PDO::prepare statement, if anyone else is having this issue use PDO::exec.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
Johny
  • 21
  • 3