insert multiple rows at once in a MySQL database

Question

I can't get PHP to insert multiple rows at once in a MySQL database.

I use the $sql = "INSERT INTO database (a,b,c,d,e) VALUES ('$a', '$b' ,'$c', '$d', '$e')";

I want to insert 5 rows at a time in the database, but it only inserts every 5th record.

For example: 1. AA 2. AB 3. AC 4. AD 5. AE

score 2 · Answer 1 · edited May 23 '17 at 10:30

2

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

edited May 23 '17 at 10:30

Community

1
1

answered Aug 28 '13 at 18:01

sarat

10,512
7
43
74

Bembo · Answer 2 · 2015-03-16T16:57:52.890

HTML

<span class="row" ><input name="a[]" value="AA" /><input name="b[]" value="AA" /></span>
<span class="row" ><input name="a[]" value="AB" /><input name="b[]" value="AB" /></span>
<span class="row" ><input name="a[]" value="AC" /><input name="b[]" value="AC" /></span>
<span class="row" ><input name="a[]" value="AD" /><input name="b[]" value="AD" /></span>
<span class="row" ><input name="a[]" value="AE" /><input name="b[]" value="AE" /></span>

PHP

$sql= array(); 
    for ($x = 0; $x < count($_POST['a']); $x++ ) {
            $sql[] = '('.$_POST["a"][$x].','.$_POST["b"][$x].')';
    }
mysql_query('INSERT INTO `orders` (`a`, `b`) VALUES '.implode(',', $sql));

OUTPUT

==================
| id |  a  |  b  |
|================|
| 1  | AA  | AA  |
|----|-----|-----|
| 2  | AB  | AB  |
|----|-----|-----|
| 3  | AC  | AC  |
|----|-----|-----|
| 4  | AD  | AD  |
|----|-----|-----|
| 5  | AE  | AE  |
------------------

insert multiple rows at once in a MySQL database

2 Answers2