在PDO中插入带有多个标签的帖子 - Insert post with multiple tags in PDO

Insert post with multiple tags in PDO

本文关键字：标签 PDO 插入 | 更新日期: 2023-09-27

数据库结构：

Table: posts
Columns: postid, postsubject, contents
Table: tags
Columns: tagid, tagtxt
Table: posts_tags
Columns: postid, tagid

//已经添加了帖子并获取最后一个id并将其放入$newId

在标签表和地图中插入post_tags:

if (isset($_POST['tagtxt'])){
$tags = explode(",", $_POST['tagtxt']);
    for ($x = 0; $x < count($tags); $x++){
        //Due to unique it will only insert if the tag dosent already exist
        $sql_addTags = "INSERT INTO tags (tagtxt) VALUES (?)";
        $stmt = $kanzconn->prepare($sql_addTags);
        $stmt->bindValue(1, $tags[$x], PDO::PARAM_STR); 
        $stmt->execute();       
        //Add the relational Link
        $sql_addRelational = "INSERT INTO posts_tags (postid,tagid) VALUES (?,?)";
        $stmt = $kanzconn->prepare($sql_addRelational);
        $stmt->bindValue(1, $newId, PDO::PARAM_INT);
        $tid = ('SELECT tags.tagid FROM tags WHERE tags.tagtxt = $tags[$x]');
        $stmt->bindValue(2, $tid, PDO::PARAM_INT); 
        $stmt->execute();   
    }
}

注：

$newId = $kanzconn->lastInsertId()

错误：

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row:
a foreign key constraint fails (`dbname`.`posts_tags`, CONSTRAINT `posts_tags_ibfk_2`
FOREIGN KEY (`tagid`) REFERENCES `tags` (`tagid`) ON DELETE CASCADE)

Thsnks

这很好！

在无重复的条件下，将任何爆炸$_POST['tagtext']插入标签表

并在posts_tags表中插入post和标签id的关系链接

if (isset($_POST['tagtxt'])){
$tags = explode(",", $_POST['tagtxt']);
    for ($x = 0; $x < count($tags); $x++){
    //Due to unique it will only insert if the tag dosent already exist
    $sql_addTags = "INSERT INTO tags (tagtxt) VALUES (?) ON DUPLICATE KEY UPDATE tagtxt = ?";
    $stmt = $kanzconn->prepare($sql_addTags);
    $stmt->bindValue(1, $tags[$x], PDO::PARAM_STR); 
    $stmt->bindValue(2, $tags[$x], PDO::PARAM_STR); 
    $stmt->execute();
    //get tags.tagid inserted or updated in above query
    $TID = $kanzconn->query("SELECT tags.tagid FROM tags WHERE tags.tagtxt = '$tags[$x]'")->fetchColumn();
    //Add the relational Link
    $sql_addRelationalTags = "INSERT INTO posts_tags (postid,tagid) VALUES (?,?)";
    $stmt = $kanzconn->prepare($sql_addRelationalTags);
    $stmt->bindValue(1, $newId, PDO::PARAM_INT);
    $stmt->bindValue(2, $TID, PDO::PARAM_INT); 
    $stmt->execute();
    }
}

感谢这里的用户，尤其是angel-king-47

PHP/MySQL-如何添加多个标签