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
FROMtags' 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