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.

Brim

You should use a loop and a normalized database. A column that contains multiple values is not normalized. – Manhandle

A loop is exactly how you should solve this, see php.net/manual/en/control-structures.foreach.php – Maintop

Answer 1

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

Apyretic

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

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: #884206 – Apyretic

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

Overseer

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. – Overseer

Answer 3

Some flavors of sql allow compound inserts:

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

Epithelioma

Answer 4

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.

Tutuila

Answer 5

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.

Uncial

Answer 6

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

Linkoski