5

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.

rollin340
  • 358
  • 2
  • 7
  • 19
  • 1
    You should use a loop and a _normalized_ database. A column that contains multiple values is not normalized. – Oded Jan 24 '11 at 12:56
  • A loop is exactly how you should solve this, see http://php.net/manual/en/control-structures.foreach.php – Andomar Jan 24 '11 at 12:56

6 Answers6

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

meze
  • 14,975
  • 4
  • 47
  • 52
  • 2
    Has Oracle finished implementing SQL-92? SQL Server supported values(..),(..) only from 2008 onwards – RichardTheKiwi Jan 24 '11 at 13:04
  • 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: http://stackoverflow.com/questions/883706/inserting-multiple-rows-into-oracle – meze Jan 24 '11 at 13:11
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.

MrEyes
  • 13,059
  • 10
  • 48
  • 68
  • 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. – MrEyes Jan 24 '11 at 13:03
2

Some flavors of sql allow compound inserts:

insert into studentcoursedetails (studentid) values
   (1),
   (2),
   (3),
Ken Downs
  • 4,707
  • 1
  • 22
  • 20
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.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
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.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
0

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

buzzwang
  • 336
  • 3
  • 3