0

When I send this SQL from a PHP script I get a SQL syntax error, however, if I run it from phpMyAdmin it works as I expect it to.

The SQL:

DELIMITER $
    CREATE TRIGGER update_listing_count
    AFTER UPDATE ON `items`
    FOR EACH ROW BEGIN
        UPDATE `listings` SET units = (SELECT COUNT(*) FROM `items` WHERE listingid = NEW.listingid) WHERE id = NEW.listingid;
        UPDATE `listings` SET unitsSold = (SELECT COUNT(*) FROM `items` WHERE listingid = NEW.listingid AND sold = true) WHERE id = NEW.listingid;
    END$
    DELIMITER ;

The PHP:

$conn = DBConnection();

    $stmt = $conn->prepare("DELIMITER $
    CREATE TRIGGER update_listing_count
    AFTER UPDATE ON `items`
    FOR EACH ROW BEGIN
        UPDATE `listings` SET units = (SELECT COUNT(*) FROM `items` WHERE listingid = NEW.listingid) WHERE id = NEW.listingid;
        UPDATE `listings` SET unitsSold = (SELECT COUNT(*) FROM `items` WHERE listingid = NEW.listingid AND sold = true) WHERE id = NEW.listingid;
    END$
    DELIMITER ;");
    $stmt->execute();

The error:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $ CREATE TRIGGER update_listing_count AFTER UPDATE ON items ' at line 1 in C:\xampp\htdocs\marketplace.php:25 Stack trace: #0 C:\xampp\htdocs\marketplace.php(25): PDOStatement->execute() #1 {main} thrown in C:\xampp\htdocs\marketplace.php on line 25

jonjo
  • 3
  • 1

1 Answers1

0

The answer is you can't use DELIMETER ON pdo...

Check this answer https://stackoverflow.com/a/50969269/13113663

Javier Rojas
  • 165
  • 6
  • If the answer is to link to another question, this would normally just be closed as a duplicate. As it stands this question/answer doesn't provide anything new and can just clog up the already large pool of resources on SO. – Nigel Ren Aug 04 '20 at 06:16