-1

I have a php array with 100 elements in it. How can I insert the entire array at once in mysql. Currently, I am inserting 1 element at a time using a loop, as below.

for($x=1;$x<=100;$x++){
    mysqli_query($Conn,"INSERT INTO mytable (slno) VALUES ('$myarray[x]')");
}
user3652549
  • 69
  • 1
  • 2
  • 8

2 Answers2

2

Something like this must work:

$query = "INSERT INTO mytable (slno) VALUES ";

for($x=1;$x<=100;$x++){
    $query .= "('$myarray[x]')";

    if($x < 100)
    {
        $query .= ",";
    }
    else
    {
        $query .= ";";
    }
}

mysqli_query($Conn, $query);
MillaresRoo
  • 3,808
  • 1
  • 31
  • 37
1

Your can insert multiple rows at once, with a single INSERT. Your MySQL request should look like this:

INSERT INTO
mytable
(slno)

VALUES
(1),
(2),
(3),
...

You could use a loop to build a values array and then implode it with a comma:

$values = array();

foreach( $myarray as $value)
{
    $values[] = '('.$value.')';
}

$query = "INSERT INTO mytable (slno) VALUES ".implode( ',', $values );
Sébastien
  • 11,860
  • 11
  • 58
  • 78
  • Is this method efficient and the right way to do? Sorry I am new to programming. – user3652549 Oct 13 '14 at 18:02
  • I would not say it's *the* "right" way, but it works. As to effiency, you now have only one MySQL INSERT query as opposed to how many you had before. So in that respect it is more efficient. – Sébastien Oct 13 '14 at 18:04
  • I think there is something wrong with the last line in code you wrote. There is a " (double quote) missing. – user3652549 Oct 13 '14 at 18:25
  • You're right, in fact I removed the last double quote because it is not needed (it was not missing it was added by mistake). – Sébastien Oct 13 '14 at 18:28