Insert post with multiple tags in PDO

debugcn Published at Dev

netlover

DB Structure:

Table: posts
Columns: postid, postsubject, contents

Table: tags
Columns: tagid, tagtxt

Table: posts_tags
Columns: postid, tagid

//already added the posts and get last id and put it in $newId

insert in tags table and in map for posts_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();   

    }
}

NOTE:

$newId = $kanzconn->lastInsertId()

the error:

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

netlover

This is work fine!

Insert any explode $_POST['tagtxt'] to tags table on the condition of non duplication

And insert relational Link for post and tags id in posts_tags table

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();

    }
}

Thanks to the users here, especially angel-king-47

PHP/MySQL - How to add multiple tags

Collected from the Internet

Please contact [email protected] to delete if infringement.