MariaDB conditional insert with placeholders doesn't work with PDO

Question

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

Try removing the delimiter, since it's only needed for the command line. Also, PDO can only handle 1 query statement at a time, so you'll have to break it up into two separate queries. — aynber, Apr 25 '19 at 17:51
I'm not 100% sure about MariaDB, but I don't believe MySQL supports procedural syntax (like DELIMITER, BEGIN, that form of IF, etc....) outside of stored procedure and function definitions. In the past when necessity has required it, and the connection permitted, I have been able to make "temporary" stored procedures to do what I need, and then drop them after. _(3 querys: create proc, exec proc, drop proc)_ — Uueerdo, Apr 25 '19 at 17:52
@aynber that resolved the error, but also gave a new one: (11 is indeed the post it got stuck on) 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 'INSERT `post_tags` (`post_id`, `tag_id`) SELECT 11, `tags`.`tag_id` FROM `tags`' at line 7. So yeah, I'll break it up. — AnnoyinC, Apr 25 '19 at 18:16
I think the entire task can be done with a single statement. See IODKU and LAST_INSERT_ID(). — Rick James, May 17 '19 at 05:53

score 0 · Accepted Answer · answered Apr 25 '19 at 23:18

I achieved the desired result with the following php code:

Keep in mind that my error/bounds checking is minimal here because I knew the dataset I was working with.

$pdo = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
$postid;
$tags = [];
$tagidStmt      = $pdo->prepare("SELECT tag_id FROM tags WHERE tag = :tagname;"); // $tag
$linkExistsStmt = $pdo->prepare("SELECT * FROM post_tags WHERE post_id = :postid AND tag_id = :tagid;"); // $postid, $tagid
$insertionStmt  = $pdo->prepare("INSERT INTO post_tags (post_id, tag_id) VALUES (:postid, :tagid);"); // $postid, $tagid

foreach ($tags as $tag){
    $tagidStmt -> execute(['tagname' => $tag]);
    $tagid = ($tagidStmt -> fetch())["tag_id"];
    $tagid = intval($tagid);

    $linkExistsStmt -> execute(['postid' => $postid, 'tagid' => $tagid]);
    $linkExists = $linkExistsStmt -> fetch();

    if ($linkExists === false){
        $insertion = $insertionStmt -> execute(['postid' => $postid, 'tagid' => $tagid]);
    }
}

MariaDB conditional insert with placeholders doesn't work with PDO

1 Answers1