Multiple mysql INSERT statements in one query php

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. :)


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

    }   
}

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

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)

Tags:

Mysql

Php