Insert multiple rows into mysql (items separated by comma)

Question

I have a small problem :) I was searching the web but didn't find any solutions.

I have a value like this (got it from $_GET[])

tag1, tag2, tag3, tag4

and all I want to do is insert it into MySql (via PHP) like this:

+---------+-------------+
|      id |         tag |
+---------+-------------+
|      33 |        tag1 |
|      33 |        tag2 |
|      33 |        tag3 |
|      33 |        tag4 |
+---------+-------------+

I found many articles about this on Stack Overflow but the biggest problem here is that I don't know how many values I'm gonna get each time (I did not find that answer).

I will appreciate any help I get.

CompanyDroneFromSector7G · Accepted Answer · 2012-02-08 13:36:27Z

4

Hi greetings from UK :)
Can't you just loop through the querystring fields and add one row to the database at a time?

e.g.

<?php

$id = 33;
$value_list = 'tag1,tag2,tag3,tag4';
$values = explode(',', $value_list);
foreach ($values as $value)
{
    $sql = "INSERT INTO table (id, value) VALUES ($id, '$value');";
    //.. execute SQL now
    echo '<p>' . $sql . '</p>';
}

?>

I uploaded this to http://cyba.co/test.php so you can see the output.

edited Feb 8, 2012 at 13:36

answered Feb 8, 2012 at 11:55

CompanyDroneFromSector7G

4,55915 gold badges62 silver badges102 bronze badges

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

5 Comments

CompanyDroneFromSector7G Over a year ago

Ah - you mean "tag1, tag2, tag3" is a continuous string? You could split these into an array using "expode"

Matic Kaltenekar Over a year ago

I used explode and with this code I get only 0, 1, 2, 3 into Mysql

Matic Kaltenekar Over a year ago

Now I get only the second tag into mysql.

CompanyDroneFromSector7G Over a year ago

Ok I added an "echo" and uploaded the script to the URL shown - you can see the results show that it should work. If not, can you post your updated code so we can see where a problem could be? :)

Matic Kaltenekar Over a year ago

Now it works like a charm. I don't know what was wrong before, all I know is that next time I'm in UK I should buy you a beer or two :) Thanks!

iblue · Accepted Answer · 2012-02-08 12:00:13Z

2

You can do an INSERT INTO with multiple values:

INSERT INTO table (tag) VALUES ('tag1', 'tag2', 'tag3');

Make sure, you properly escape your input (if user suplied) to prevent SQL injection. You can use mysql_escape_string() for this.

Edit: To get these values, you can do:

$values = explode(",", $_GET['your_param']);
foreach($values as $idx => $val) {
  $values[$idx] = mysql_real_escape_string(trim($val));
}

mysql_query("INSERT INTO table (tag) VALUES (".implode(",", $values).");");

edited Feb 8, 2012 at 12:00

answered Feb 8, 2012 at 11:54

iblue

30.6k20 gold badges93 silver badges131 bronze badges

2 Comments

Fahim Parkar Over a year ago

but how you are going to get 'tag1', 'tag2', 'tag3' from tag1, tag2, tag3, tag4???

iblue Over a year ago

Added another revision, so hopefully now everything is clear.

Fahim Parkar · Accepted Answer · 2012-02-08 12:00:40Z

0

Use explode() function to split the string

$tag  = "tag1, tag2, tag3";
$pieces = explode(", ", $tag);

SQL query would be

INSERT INTO myTable VALUES ($pieces[0],$pieces[1],$pieces[2]);

Good Luck!!!

answered Feb 8, 2012 at 12:00

Fahim Parkar

31.8k46 gold badges169 silver badges285 bronze badges

2 Comments

user984869 Over a year ago

Since Matic said he did not know how many tags there would be at any given time, Matic would need to loop through the array you created.

Fahim Parkar Over a year ago

@user984869 : agreed... then there is problem what if tag are less?? is he going to have NULL there? we will need table structure then... what are your thoughts??

user984869 · Accepted Answer · 2012-02-08 12:06:49Z

0

I would convert your comma-delimited tags into an array, then loop through the array to do your inserts.

$data_array=explode(",",$tag); // converts tags to an array based on the comma

// loop through each item in the array
foreach($data_array as $one_tag){

// Get rid of the space you have in your tag string
$one_tag=trim($one_tag);

// Do whatever sanitization you need to do to your data, for example...
$one_tag=mysql_real_escape_string($one_tag);

// Insert into the database
mysql_query("INSERT INTO table SET tag='$one_tag'");

}

You can make this more efficient using Prepared Statements, but it kept this example simple.

answered Feb 8, 2012 at 12:06

user984869

4422 silver badges8 bronze badges

Comments

Sam Arul Raj T · Accepted Answer · 2012-02-08 12:21:11Z

0

This will work out according to your result

$tagData = array();
$tagList  = explode(",","tag1,tag2,tag3,tag4");
foreach ($tagList as $key=>$value) {
    $tagData[] = '("' . $key . '", "' . $tagList[$key] . '")';
 }
$query = 'INSERT INTO tags (id,tag) VALUES' . implode(',', $tagData);
echo $query;die;
mysql_query($query);

edited Feb 8, 2012 at 12:21

answered Feb 8, 2012 at 12:06

Sam Arul Raj T

1,78017 silver badges23 bronze badges

Comments

Naveen Kumar · Accepted Answer · 2012-02-08 12:25:32Z

0

$id=33;

$tag=$_GET['tag'];
$tag_Split= explode(",", $tag);

$cnt=count($tag_Split);

for($i=0;$i<$cnt;$i++)
{
mysql_query("insert into tag_table(id,tag) 
values ($id, $tag_Split[$i])");
}

answered Feb 8, 2012 at 12:25

Naveen Kumar

4,6011 gold badge20 silver badges39 bronze badges

5 Comments

Naveen Kumar Over a year ago

check the place where you get the tags do not add comma and space after each entry only add a comma.

Matic Kaltenekar Over a year ago

I removed space after comma and the same thing happens

Naveen Kumar Over a year ago

try var_dump($tagStr) if it is like "tag1,tag2,tag3,tag4" then explode should work.

Matic Kaltenekar Over a year ago

Still no success, anything I try I get all the same values or just one.

Naveen Kumar Over a year ago

what is you $tag string output

dar7yl · Accepted Answer · 2012-02-08 12:27:49Z

-1

Try this for size: (combining ideas from a number of people who almost got it right).

$values =  array(
                    33=>'tag1'),
                    33=>'tag2'),
                    33=>'tag3'),
                );

$sql = "INSERT INTO table (id, value) VALUES ";
$count=0;
foreach ($values as $id=> $value)
{
    if ($count++ > 0)
        $sql .= ',';
    $sql .= "($id, '$value')";
}
$query = mysql_query($sql, $db);

answered Feb 8, 2012 at 12:27

dar7yl

3,78528 silver badges20 bronze badges

Collectives™ on Stack Overflow

Insert multiple rows into mysql (items separated by comma)

7 Answers 7

5 Comments

2 Comments

2 Comments

Comments

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

5 Comments

2 Comments

2 Comments

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related