Insert multiple values in one column mysql?

Question

I have a table of checkboxes and values, if a user selects a checkbox they select the value of the id in an array called checkedHW for simplicity sake this is what code looks like:

$ids = implode(',',arrayofids);

$sql = "insert into table(id, type) values($ids,type);
$db->query($sql);

echo query for testing:

"insert into table('id1,id2','type')

I figured that if I loop through this query I could hypothetically do this:

"insert into table('id1','type');"

"insert into table('id2','type');"

but I'm not exactly quite sure how to do, any help would be wonderful :)

I actually solved it using:

for($i=0;$i<count(arrayofids); $i++){
$sql = "insert into table(id,type) values(array[$i], 'type'";
$db->query($sql);}

I hope that helps someone and thank you guys for the help!

I just added an int cast and it only inserts a row for the first value — bubbles
– bubbles, Commented Sep 14, 2012 at 19:16

Kermit · Accepted Answer · 2012-09-14 19:18:46Z

7

You could do something like this:

$base = 'INSERT INTO table (id, type) VALUES (';
$array = array(1, 2, 3, 4);
$values = implode(", 'type'), (", $array);
$query = $base . $values . ", 'type')";

$db->query($query);

This is what would be getting submitted:

INSERT INTO table (id, type) VALUES (1, 'type'), (2, 'type'), (3, 'type'), (4, 'type')

answered Sep 14, 2012 at 19:18

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

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

1 Comment

bubbles Over a year ago

i was able to solve it without using implode, but thank you for the help!

xkeshav · Accepted Answer · 2012-09-14 19:16:12Z

0

Both query are completely different

insert into table('id1,id2','type') will insert single row

id1,id2 | type

whereas

insert into table('id1','type');"

insert into table('id2','type');"

will insert two rows

id1 | type
id2 | type

so if your id column is int type then you cant run first query

answered Sep 14, 2012 at 19:16

xkeshav

54.2k47 gold badges183 silver badges255 bronze badges

Comments

Martin Bean · Accepted Answer · 2012-09-14 19:18:09Z

0

If you have a series of checkboxes, and wanting to insert the values into your table, you could loop through them like this:

<?php

$values = array();

foreach ($_POST['field_name'] as $i => $value) {
    $values[] = sprintf('(%d)', $value);
}

$values = implode(',', $values);

$sql = "INSERT INTO `table_name` (`column_name`) VALUES $values";

This will give you a SQL query similar to:

INSERT INTO `table_name` (`column_name`) VALUES (1),(2),(3),(4)

Hope this help.

answered Sep 14, 2012 at 19:18

Martin Bean

39.6k28 gold badges134 silver badges207 bronze badges

Collectives™ on Stack Overflow

Insert multiple values in one column mysql?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related