SQL - Inserting multiple row values into a single column

Question

I need help on a method of inserting values into a single column on different rows.

Right now, I have an imploded array that gives me a value such as this:

('12', '13', '14')

Those numbers are the new IDs of which I wish to insert into the DB.
The code I used to implode the array is this:

$combi = "('".implode("', '",$box)."')"; // Where $box is the initial array

The query of which I plan to use gets stuck here:

mysql_query("INSERT INTO studentcoursedetails (studentID) VALUES

One option would be to repeat this, but I cant, because the array will loop; there might be 3 IDs, there might be 20.
A loop doesn't seem right. Any help would be appreciated.

You should use a loop and a normalized database. A column that contains multiple values is not normalized. — Oded
– Oded, Commented Jan 24, 2011 at 12:56
A loop is exactly how you should solve this, see php.net/manual/en/control-structures.foreach.php — Andomar
– Andomar, Commented Jan 24, 2011 at 12:56

meze · Accepted Answer · 2011-01-24 12:58:32Z

3

For inserting more than one value into a table you should use (value1), (value2) syntax:

$combi = "('".implode("'), ('",$box)."')";

PS: This feature is called row value constructors and is available since SQL-92

answered Jan 24, 2011 at 12:58

meze

15.1k4 gold badges49 silver badges53 bronze badges

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

2 Comments

RichardTheKiwi Over a year ago

Has Oracle finished implementing SQL-92? SQL Server supported values(..),(..) only from 2008 onwards

meze Over a year ago

Don't think they would add a new syntax for that. There are already two ways to do this in oracle, which i found here: stackoverflow.com/questions/883706/…

MrEyes · Accepted Answer · 2011-01-24 12:58:09Z

2

Can you not do something like this:

for($x = 0; $x < count($box); $x++)
{
  mysql_query("INSERT INTO studentcoursedetails (studentID) VALUES ($box[$x]);
}

This will work directly on your array, insert a new row for each value in $box and also prevent the need to implode the array to a comma delimited string

Storing ids as a comma delimited string might initially seem like a simple model but in the long term this will cause you no end of trouble when trying to work with a non-normalised database.

answered Jan 24, 2011 at 12:58

MrEyes

13.9k11 gold badges50 silver badges68 bronze badges

1 Comment

MrEyes Over a year ago

Actually the suggestion from cyberkiwi is better as this only requires 1 DB call whereas mine would require however many values there are in the array. Both achieve exactly the same thing but cyberkiwis solution would create less load in both PHP and MySQL.

Ken Downs · Accepted Answer · 2011-01-24 12:59:22Z

2

Some flavors of sql allow compound inserts:

insert into studentcoursedetails (studentid) values
   (1),
   (2),
   (3),

answered Jan 24, 2011 at 12:59

Ken Downs

4,8671 gold badge25 silver badges20 bronze badges

Comments

Pablo Santa Cruz · Accepted Answer · 2011-01-24 13:00:31Z

2

If you are using MySQL, you can insert multiple values in a single sentence:

sql> insert into studentcoursedetails (studentID)
   > values (('12'), ('13'), ('14'));

So, you just need to build that string in PHP and you are done.

answered Jan 24, 2011 at 13:00

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Comments

RichardTheKiwi · Accepted Answer · 2011-01-24 12:59:00Z

1

You can still create the statement via implode. Just don't use VALUES; use SELECT instead

$combi = " ".implode(" UNION ALL SELECT ",$box)." "; // Where $box is the initial array
mysql_query("INSERT INTO studentcoursedetails (studentID) SELECT " . $combi)

The SELECT .. union is portable across many dbms.

Note on the IDs - if they are numbers, don't quote them.

answered Jan 24, 2011 at 12:59

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Comments

buzzwang · Accepted Answer · 2011-01-24 12:59:04Z

0

Check to see if there is a variant of the mysql_query function that will operate on an array parameter.

answered Jan 24, 2011 at 12:59

buzzwang

3363 silver badges3 bronze badges

Collectives™ on Stack Overflow

SQL - Inserting multiple row values into a single column

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest