CODEWITHSUNDEEP
phpmysqlinsertrows
Simon Suh
Simon Suh

Reputation: 10882

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

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

Upvotes: 1

Views: 1431

Answers (5)

Rahly
Rahly

Reputation: 1511

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.

Upvotes: 1

Breezer
Breezer

Reputation: 10490

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

Upvotes: 0

Andreas Wong
Andreas Wong

Reputation: 60506

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

Upvotes: 0

RageZ
RageZ

Reputation: 27313

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

Upvotes: 0

Jahan Zinedine
Jahan Zinedine

Reputation: 14864

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

Upvotes: 0

Related Questions