MySQL query works when applied by Phpmyadmin, but not when applied through PHP?

Question

I am trying to apply a trigger to an existing table and column through PHP. When executing the PHP code, no error is thrown, but the trigger is not created.

My PHP code is:

//file: migrations.php

// created_at column already defined in a previous migration
//...

$migrations[3]['message'] = "added created_at, updated_at columns on rate table";
$migrations[3]['created'] = "2015-09-05 08:28:00";
$migrations[3]['sql']     = "
DELIMITER |
CREATE TRIGGER rate_created_at BEFORE INSERT ON rate
FOR EACH ROW
    BEGIN
        SET NEW.created_at = CURRENT_TIMESTAMP;
    END |
    DELIMITER ;
";

// apply migration
foreach ($migrations as $key => $value) {
    // run the migration SQL for the current iteration
    $sth = $dbh->prepare( $value['sql'] );
    $sth->execute();
}

heres what does work

Copying and pasting the trigger creating SQL into Phpmyadmin
getting rid of the DELIMITER and BEGIN END statements e.g. if I reduce the SQL to

$migrations[3]['sql']     = "
CREATE TRIGGER rate_created_at BEFORE INSERT ON rate
FOR EACH ROW
        SET NEW.created_at = CURRENT_TIMESTAMP;
";

It does run from PHP, problem is I dont believe I can run multiline statements without DELIMITER and BEGIN END.
I have tried

escaping the delimiter character
using different delimiter characters

What can I do to get the PHP to run the SQL with the DELIMITER and BEGIN END statements?

run the delimeter commands as SEPARATE queries. e.g. delimiter/create/delimiter — Marc B
– Marc B, Commented Sep 4, 2015 at 21:52

Community · Accepted Answer · 2017-05-23 12:14:40Z

2

yes, you will have to get rid of those DELIMITER, just have the below code

$migrations[3]['sql'] = "
CREATE TRIGGER rate_created_at BEFORE INSERT ON rate
FOR EACH ROW
    BEGIN
        SET NEW.created_at = CURRENT_TIMESTAMP;
    END;";

See this another post saying same thing PHP: multiple SQL queries in one mysql_query statement

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Sep 4, 2015 at 21:51

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

3 Comments

the_velour_fog Over a year ago

thanks Rahul, that works! I wonder why MySQL doesnt think the whole trigger statement is not finished when not using a different delimiter character?

Barmar Over a year ago

DELIMITER isn't part of SQL syntax, it's a feature of the interactive user interface.

Barmar Over a year ago

This is why you can't use it at sqlfiddle, you have to use their menu to set the delimiter.

Collectives™ on Stack Overflow

MySQL query works when applied by Phpmyadmin, but not when applied through PHP?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related