如何使用php将多行数据插入到mysql表中？

Question

我在MySQL中有一个表格来包含用户创建的反机器人问题。但是，在安装表格时，我想事先插入一些默认的反机器人问题。我想出了下面的代码，但我不确定如何将它放入正确的语法。基本上，我创建了两个数组（一个用于问题，另一个用于答案，按照各自的顺序排列），并且想要使用foreach（）或者while（）函数循环访问每个问题和答案。

这里是我的代码：

$questions = array(
"question1" => "What is 2+6?", 
"question2" => "What color is the sky?", 
"question3" => "What number is betwee 6 and 8?", 
"question4" => "How many letters are there in the English alphabet?", 
"question5" => "How many hours are there in a day?", 
"question6" => "Are you a human or a bot?" 
); 
$answers = array(
"answer1" => "8", 
"answer2" => "blue", 
"answer3" => "7", 
"answer4" => "26", 
"answer5" => "24", 
"answer6" => "human" 
); 
$x = 0; 
foreach ($question[$x]) { 
$sql = "INSERT INTO 
administrator_instructions 
    (question, question_naswer) 
VALUES 
    ('" . $question[$x] . "','" . $answer[$x] . "')"; 
$x++; 
} 

Answer 1

沿着线的东西：

$q_a = array(
    'what is 2 + 6' => '8', 
    'what color is the sky?' => 'blue', 
    // etc ... 
} 
$i = 0; 
$cnt = count(array_keys($q_a)); 
$sql = 'insert into administrator_instructions (question, question_answer) values '; 
foreach ($q_a as $q => $a) { 
    $sql .= sprintf("('%s', '%s')", mysql_real_escape_string($q), mysql_real_escape_string($a)); 
    $i++; 
    if($i < $cnt) $sql .= ", "; 
} 

Answer 2

这样的事情会做到这一点，再加上你必须在某个

$questions = array(
"question1" => "What is 2+6?", 
"question2" => "What color is the sky?", 
"question3" => "What number is betwee 6 and 8?", 
"question4" => "How many letters are there in the English alphabet?", 
"question5" => "How many hours are there in a day?", 
"question6" => "Are you a human or a bot?" 
); 
$answers = array(
"answer1" => "8", 
"answer2" => "blue", 
"answer3" => "7", 
"answer4" => "26", 
"answer5" => "24", 
"answer6" => "human" 
); 
$x = 0; 
$sql = 'INSERT INTO 
administrator_instructions 
    (question, question_naswer) 
VALUES' 
for ($i = 0; $i < count($question); $i++) { 
    if($i){ 
    $sql .= ',' 
    } 
    $sql . = "('" . $question[$i] . "','" . $answer[$i] . "')"; 

} 
mysql_query($sql); 

MySQL的支持，这句法一些批量插入执行的SQL（这不SQL标准）

INSERT INTO TABLE(col1,col2) VALUES(1,1),(2,2),(3,3) 

我也发现它会更容易有像这样的数组

$questions_answers = array(
    array('q' => 'How are you?', 'a' => 'Good') 
); 

Answer 3

你可以建立这样的命令并执行一个时间

USE YourDB 
GO 
INSERT INTO MyTable (FirstCol, SecondCol) 
SELECT 'First' ,1 
UNION ALL 
SELECT 'Second' ,2 
UNION ALL 
SELECT 'Third' ,3 
UNION ALL 
SELECT 'Fourth' ,4 
UNION ALL 
SELECT 'Fifth' ,5 

进一步详情，请参阅this

Answer 4

$questions = array(
"0" => "What is 2+6?", 
"1" => "What color is the sky?", 
"2" => "What number is betwee 6 and 8?", 
"3" => "How many letters are there in the English alphabet?", 
"4" => "How many hours are there in a day?", 
"5" => "Are you a human or a bot?" 
); 
$answers = array(
"0" => "8", 
"1" => "blue", 
"2" => "7", 
"3" => "26", 
"4" => "24", 
"5" => "human" 
); 
$row = array_combine($answers,$questions); 

foreach($row as $k=>$v){ 
$sql = "INSERT INTO 
administrator_instructions 
    (question, question_naswer) 
VALUES 
    ('" . $k . "','" . $v . "')"; 
} 

我觉得这是比较容易

你摆脱柜台，你可以简单做 $ questions [] =“”; $ answers [] =“”; 你想写一个问题和答案，每次从思考所有nummbers节省您和所有和让你集中在什么是重要的

这样

@$questions[] = "What is 2+6?"; 
$questions[] = "What color is the sky?"; 
$questions[] = "What number is betwee 6 and 8?"; 
$questions[] = "How many letters are there in the English alphabet?"; 
$questions[] = "How many hours are there in a day?"; 
$questions[] = "Are you a human or a bot?"; 

@$answers[] = "8"; 
$answers[] = "blue"; 
$answers[] = "7"; 
$answers[] = "26"; 
$answers[] = "24"; 
$answers[] = "human"; 

Answer 5

实际上，在这里使用PDO要比制作一个巨大的查询字符串要快得多。

 
$db = new PDO("connect string"); 
$stm = $db->prepare("INSERT INTO administrator_instructions (question, question_naswer) VALUES(?,?)"); 
foreach($q_a as $q => $a) { 
    if($stm && $stm->execute(array($q, $a))) { 
    // Handle insert 
    } else { 
    // Handle error 
    } 
} 

不仅每次查询都不必重新分析，每次调用只传递准备好的语句的数据。另外，如果插入失败，您将立即得到答复，并处理它。做一个巨大的单一插入是不好的，因为如果失败了，你不会真正知道什么是被忽略的，什么都没有，除非你实际上重新查询了所有的东西。