Is this legal?
$string1= "INSERT INTO....;";
$string1 .= "INSERT INTO....;";
$string1 .= "INSERT INTO....;";
mysql_query($string1) or die(mysql_error());
Asked
Active
Viewed 8.3k times
27
Ian McCullough
- 1,423
- 4
- 25
- 36
-
I solved my problem run multiple query on my php project using this mysqli method [mysqli.multi-query.](http://us2.php.net/manual/en/mysqli.multi-query.php) – Fthr Feb 03 '17 at 16:32
8 Answers
54
For what it's worth, and depending on if you're inserting the same data into the same tables, it's much better to insert multiple values with the one insert e.g.
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
Gavin Gilmour
- 6,833
- 4
- 40
- 44
41
No, mysql_query() only allows one query at a time.
You can insert multiple rows like this:
INSERT INTO table (col1, col2)
VALUES (1, 2), (3, 4), (5, 6)
Greg
- 316,276
- 54
- 369
- 333
-
1This one is better - its important to specify the field names that you're dumping the data into! – itsricky Aug 01 '13 at 12:17
13
From MySQL dev support MySQL dev forum
INSERT INTO table (artist, album, track, length)
VALUES
("$artist", "$album", "$track1", "$length1"),
("$artist", "$album", "$track2", "$length2"),
("$artist", "$album", "$track3", "$length3"),
("$artist", "$album", "$track4", "$length4"),
("$artist", "$album", "$track5", "$length5");
So insert goes as normal as always:
- naming first the table name where we want to insert new row,
- followed by naming column names in round brackets (Note: Not needed if you want to insert ALL columns),
- followed by VALUES key name and then in round brackets comes the values that you want to insert for new ROW in the above table,
- followed by COMMA and then another pair of round brackets with new values for new row in the mentioned table above
- and this repeats N-times as long you have the data to insert.
Happy inserting multiple values with ONE insert statement. :)
Matija
- 17,604
- 2
- 48
- 43
4
Copy/paste example within a function and a loop (suppose $ids is an array)
public function duplicateItem($ids)
{
if (isset($ids[0])){ //at least one item
$sqlQuery = "INSERT INTO items (item_id, content) VALUES ";
for($i=0; $i<count($ids); $i++) {
if ($i == count($ids)-1){
$sqlQuery .= "(".$ids[$i][0].", '".$ids[$i][1]."');";
}else{
$sqlQuery .= "(".$ids[$i][0].", '".$ids[$i][1]."'),";
}
}
mysql_query($sqlQuery) or die('Error, insert query failed: '.mysql_error());
}
}
Adrian P.
- 5,060
- 2
- 46
- 47
-
It's better to avoid concatenation. Use `implode()`. see this [answer](http://stackoverflow.com/a/780046/4040525) for details. – Sourav Ghosh Dec 29 '15 at 16:55
-
1Why using of implode() is beneficial in this case? In the question above we have normal SQL statements. In the answer you point to they talk about large amount of text (such as 1000 inserts of large texts) and, yes, in that case is good to avoid concatenation. – Adrian P. Dec 30 '15 at 00:36
-
I know. Its OK to use concatenation here. Yesterday I noticed both of the answers and thought it's better to use `implode()` as a rule of thumb. – Sourav Ghosh Dec 30 '15 at 07:42
2
In general, that's valid SQL since each statement ends with a semicolon, but PHP doesn't allow you to send more than one query at a time, in order to protect against SQL injection attacks that might exploit a poorly written script.
You can still use a syntax like:
INSERT INTO foo VALUES ('a1', 'b1'), ('a2', 'b2');
VoteyDisciple
- 37,319
- 5
- 97
- 97
1
INSERT INTO table (a,b) VALUES (1,2), (2,3), (3,4);
1
// if $data is an array
$sqlQuery ="INSERT INTO tableName(col1, col2) VALUES ";
for($i=0; $i<count($data); $i++) {
$sqlQuery .="(".$data[$i][0].", '".$data[$i][1]."'),";
}
$sqlQuery = rtrim($sqlQuery, ','); // Remove last comma
mysql_query($sqlQuery) or die('Error, insert query failed: '.mysql_error());
