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."&nbsp;";
            $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.

nope, but the way its coded, you could be wide open to SQL-Injection attacks — DRapp
– DRapp, Commented Jul 27, 2010 at 17:21
you need to add some debugging in your code. echo as many info as possible - every variable, query results, etc. I bet you don't even have an idea, what's $tags variable content is. — Your Common Sense
– Your Common Sense, Commented Jul 27, 2010 at 17:22
true, not much security added. In the future, I'll add some checks to validate the entry. Right now I'm only striving for basic functionality since I know exactly what $tags variable content is for now. — samfu_1
– samfu_1, Commented Jul 27, 2010 at 17:29
another lack-of-debugging question. and bunch anything-but-on-topic answers — Your Common Sense
– Your Common Sense, Commented Jul 27, 2010 at 17:34

Jim · Accepted Answer · 2010-07-27 17:20:57Z

4

$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.

answered Jul 27, 2010 at 17:20

Jim

18.9k5 gold badges51 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

samfu_1 Over a year ago

I turned off auto_incrementing and primary key. I want to have one blog id (i.e.: blog id = 1) that could have multiple tags.

Mark Byers Over a year ago

@smafu_1: Then the column name id is a bit misleading as it is not an identity for the row. Perhaps it could be called blog_id instead.

Mark Byers · Accepted Answer · 2010-07-27 17:25:14Z

4

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.

answered Jul 27, 2010 at 17:25

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

1 Comment

Mitch Dempsey Over a year ago

+1 - Using combined inserts like this is much more efficient as well.

Nicolas78 · Accepted Answer · 2010-07-27 17:21:34Z

0

$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.

answered Jul 27, 2010 at 17:21

Nicolas78

5,1441 gold badge26 silver badges43 bronze badges

2 Comments

samfu_1 Over a year ago

Correct. It is the id of the blog entry. It was the primary key and i changed it for that reason so that I could insert multiple entries with the same number.

Nicolas78 Over a year ago

hm. here's what I'd do then: try printing out the sql commands, and enter them to the commandline. See if you get an error function.

opatut · Accepted Answer · 2010-07-27 21:44:23Z

0

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

answered Jul 27, 2010 at 21:44

opatut

6,8745 gold badges34 silver badges37 bronze badges

Collectives™ on Stack Overflow

using foreach loop for multiple MySQL inserts

4 Answers 4

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related