SQL - 将多个行值插入单个列

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')

（'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:

这些数字是我希望插入数据库的新ID。我用来破坏数组的代码是这样的：

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

$ combi =“（'”。implode（“'，'”，$ box）。“'）”; //其中$ box是初始数组

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

我打算使用的查询卡在这里：

mysql_query("INSERT INTO studentcoursedetails (studentID) VALUES

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.

一种选择是重复这一点，但我不能，因为数组将循环;可能有3个ID，可能有20个。循环似乎不正确。任何帮助，将不胜感激。

6 个解决方案

#1

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

要在表中插入多个值，您应该使用（value1），（value2）语法：

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

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

PS：此功能称为行值构造函数，自SQL-92起可用

#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

这将直接在您的数组上工作，为$ box中的每个值插入一个新行，并且还可以防止需要将数组内爆到逗号分隔的字符串

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.

将id作为逗号分隔的字符串存储可能最初看起来像一个简单的模型，但从长远来看，这将使您在尝试使用非规范化数据库时不会遇到麻烦。

#3

Some flavors of sql allow compound inserts:

一些sql允许复合插入：

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

#4

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

如果您使用的是MySQL，则可以在一个句子中插入多个值：

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

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

所以，你只需要在PHP中构建该字符串就可以了。

#5

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

您仍然可以通过implode创建语句。只是不要使用VALUES;改用SELECT

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

SELECT .. union可以跨多个dbms移植。

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

关于ID的注意事项 - 如果它们是数字，请不要引用它们。

#6

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

检查是否存在将对数组参数进行操作的mysql_query函数的变体。

#1