using foreach loop for multiple MySQL inserts

Question

I'm using the following snippet to break up an string array, then insert them into the database.

//split tags into individual words
        $tag_array = explode(',', $tags);

        foreach($tag_array as $tag){
            addslashes($tag);
            echo $tag." ";
            $addTagsQuery = "INSERT INTO `my_blog`.`tags` (`id`, `name`) VALUES 
            ('".$id."', '".$tag."');";
            $tagsResult = $db->query($addTagsQuery);
            if($tagsResult){
                echo "tag added <br />";
            }
            else {
                echo "tag was not added <br />";
            }

        }

My problem lies within a scenario where multiple tag (strings) are submitted. Unfortunately, only the first string in the array is inserted. Any insight as to why only the first string in the array is inserted into the MySQL database would be appreciated.

Answer 1

$id is not being incremented in the loop. Chances are you are getting a duplicate error, but for whatever reason it is not telling you (poor error handling?).

$addTagsQuery = "INSERT INTO `my_blog`.`tags` (`name`) VALUES 
            ('".$tag."');";

If the ID is auto_incrementing, just omit and it will handle that for you.

Answer 2

$id is the id of the blog entry the tags are submitted for? Do you maybe have turned ID into a primary key or otherwise unique? That could cause the problem.

Answer 3

• You should use an auto-incrementing id instead of setting the id manually.

• You don't need to run multiple insert statements. You can do it in one statement:

  INSERT INTO my_blog.tags (name) VALUES ('tag1'), ('tag2')
  

• The function addslashes doesn't modify the string so the way you are using it will have no effect.

• You should use bind parameters instead of escaping strings.

Answer 4

Try it like this:

$tag_array = explode(',', $tags);
$stmt = $db->prepare("INSERT INTO my_blog.tags (id, name) VALUES (?,?)");
foreach($tag_array as $tag){
    if ($stmt->execute(Array($id, $tag))){
        echo "tag added <br />";
    }
    else{
        echo "tag was not added <br />";
    }
    $stmt->closeCursor();
}