CODEWITHSUNDEEP
phpsqlarraysinsertimplode
rollin340
rollin340

Reputation: 358

SQL - Inserting multiple row values into a single column

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.

Upvotes: 5

Views: 5484

Answers (6)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

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.

Upvotes: 2

Ken Downs
Ken Downs

Reputation: 4827

Some flavors of sql allow compound inserts:

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

Upvotes: 2

buzzwang
buzzwang

Reputation: 336

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

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

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.

Upvotes: 1

meze
meze

Reputation: 15087

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

Upvotes: 3

MrEyes
MrEyes

Reputation: 13690

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.

Upvotes: 2

Related Questions