jeudi 25 avril 2019

MariaDB conditional insert with placeholders doesn't work with PDO

I have the following tables for my blog:

Table tags which has columns tag_id (A.I.) and tag

Tablepost_tags which has columns post_id and tag_id. It describes which tags belong to which posts.

I am trying to make some SQL query that binds a given tag to a given post_id in post_tags, but if tag doesn't exist in tags it creates it there first.

$query = 
SQL
DELIMITER //

BEGIN NOT ATOMIC
    IF NOT EXISTS (SELECT `tags`.`tag` FROM `tags` WHERE `tag`  = :tagname) THEN 
       INSERT INTO `tags` (`tag_id`, `tag`) VALUES (NULL, :tagname);
    END IF;
END //

DELIMITER ;

INSERT INTO post_tags (post_id, tag_id)  SELECT :postid, tags.tag_id FROM tags WHERE tags.tag = :tagname;

I then have the following PHP code to execute this procedure:

// all $db variables are given, known and correct.
$pdoconn = new PDO("mysql:host=$dbhost;dbname=$db", $dbuser, $dbpass);
$pdoconn -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$taginsert = $pdoconn -> prepare($query);

$id = 0; // The post_id. This is given, known and correct.
$tags = array(); // Array of strings (the tags)
foreach ($tags as $tag){
    try{
        $taginsert -> bindValue(":tagname", $tag, PDO::PARAM_STR);
        $taginsert -> bindValue(":postid", $id, PDO::PARAM_INT);
        $taginsert -> execute();
    } catch(PDOException $e){
        $message = $e->getMessage();
        var_dump($message);
    }
}

$taginsert -> close();

This should be straightforward enough. And indeed, when replacing the placeholders for actual values and executing the raw query from within phpMyAdmin, it works! But when I execute the pdo php code, it just dumps this error:

string(284) "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER // BEGIN NOT ATOMIC IF NOT EXISTS (SELECT tags.tag FROM tags' at line 1" Needless to say this cryptic error message is of no use to me.

I am using php 7.3 with 10.1.30-MariaDB-1~xenial

Aucun commentaire:

Enregistrer un commentaire