How do I insert multiple rows of data into a mysql table using php?

Question

I have a table in MySQL to contain user created anti-bot questions. When installing the tables however, I want to insert some default anti-bot questions beforehand. I came up with the following code but I'm not sure exactly how to put it into the correct syntax. Basically, I created two arrays (one for questions and another for answers, in respective and matching order.) and want to cycle through each pair of question and answer using the foreach() or perhaps the while() function.

Here's my code:

$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++;
}

Rahly · Accepted Answer · 2010-12-02 09:08:18Z

1

actually, using PDO would be a lot faster here, than making a huge query string.

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

Not only does the query not have to be re-parsed everytime, each call only passes the data for the prepared statement. Plus, you get an immediate response if the insert failed, and to handle it. Doing a huge single insert is bad because if it fails, you don't really know whats been insered and what hasn't, unless you actually re-query everything.

answered Dec 2, 2010 at 9:08

Rahly

1,5311 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andreas Wong · Accepted Answer · 2010-12-02 06:25:31Z

0

Something along the line of:

$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 .= ", ";
}

answered Dec 2, 2010 at 6:25

Andreas Wong

60.7k19 gold badges112 silver badges123 bronze badges

Comments

Jahan Zinedine · Accepted Answer · 2010-12-02 06:21:06Z

0

You can build a command like this and executing one time

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

for further details refer to this

answered Dec 2, 2010 at 6:21

Jahan Zinedine

14.9k5 gold badges49 silver badges74 bronze badges

Comments

RageZ · Accepted Answer · 2010-12-02 06:25:00Z

something like this would do it, plus you have to execute the sql at some point

$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 support some batch inserts with this syntax (this not SQL standard)

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

I also find it would be easier to have the array like this

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

Breezer · Accepted Answer · 2010-12-02 06:27:49Z

$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 . "')";
}

i find this easier

you get rid of the counter and you could simple do $questions[]=""; $answers[]=""; each time you want to write a question and answers saves you from thinking about all the nummbers and all and keeps you concentrated on what's important

like this

@$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";

Collectives™ on Stack Overflow

How do I insert multiple rows of data into a mysql table using php?

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related