How to insert multiple records with 1 Insert?

Asked

Viewed 754 times

1

I need to enter multiple records with an Insert

Example:

$ids= "1,2,3,4"

$conn->prepare("INSERT INTO documento (id) VALUES (1)");
$conn->prepare("INSERT INTO documento (id) VALUES (2)");
$conn->prepare("INSERT INTO documento (id) VALUES (3)");
$conn->prepare("INSERT INTO documento (id) VALUES (4)");

Is there any loop that for example checks how many records I have in my $ids variable and does the Insert several times repeating until entering the last record of the $ids variable

php mysql

  • Does the variable follow this pattern or can it change? Always will be 0.1.2.. ?

  • Yes, it will always be that pattern

2 answers

1


In addition to the links indicated in the comments. For your case, you can do so:

$ids = "1,2,3,4";
$idsArray = explode(",", $ids);
$query = "INSERT INTO documento (id, data) VALUES ";
foreach($idsArray as $id){
    $query .= "($id, now()),";
}
$query = substr($query, 0, -1);
$conn->prepare($query);

A string $query will look like this:

INSERT INTO documento (id) VALUES (1,2018-06-29),(2,2018-06-29),(3,2018-06-29),(4,2018-06-29)

  • If I had one more "date" column where I could pick up the current time now() left fixed too, where I would change in your code?

  • @Smokerohden I’ll put there. Calm ae.

  • @Smokerohden is there. See if it works.

  • 1

    worked! Broke a gallon Thanks for collaborating with the reply

  • @Smokerohden quiet. I’ve been there too! = ) Hug!

1

I leave an alternative.

You can use the function array_map to iterate the array and apply a function to each element. The function you see will perform its role:

array_map(function($id) { return "($id, now())"; }, explode(",", $ids))

The function array_map will return an array:

Array
(
  [0] => (1, now())
  [1] => (2, now())
  [2] => (3, now())
  [3] => (4, now())
)

then simply join the elements using the function implode separating them by the comma:

implode(', ', array_map(function($id) { return "($id, now())"; }, explode(",", $ids)))

Complete code:

$ids = "1,2,3,4";
$query = "INSERT INTO documento (id, data) VALUES " . implode(', ', array_map(function($id) { return "($id, now())"; }, explode(",", $ids)));
$conn->prepare($query);

exit the $query mounted:

INSERT INTO documento (id, data) VALUES (1, now()), (2, now()), (3, now()), (4, now())

See working on repl.it

References:

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.