Is this legal?
$string1= "INSERT INTO....;";
$string1 .= "INSERT INTO....;";
$string1 .= "INSERT INTO....;";
mysql_query($string1) or die(mysql_error());
-
I solved my problem run multiple query on my php project using this mysqli method mysqli.multi-query.Fthr– Fthr2017-02-03 16:32:58 +00:00Commented Feb 3, 2017 at 16:32
Add a comment
|
8 Answers
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);
1 Comment
Sarath Chandra
Special thanks for the reference to "Insert Speed" page.
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)
1 Comment
itsricky
This one is better - its important to specify the field names that you're dumping the data into!
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. :)
Comments
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());
}
}
3 Comments
Sourav Ghosh
It's better to avoid concatenation. Use
implode(). see this answer for details.
Adrian P.
Why 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.
Sourav Ghosh
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.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');
Comments
// 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());
Comments
No. They are separate queries, and must be called as such.
