Insert an array into a database table in one query

Question

iam has an array of elements of type

[element1, itmem2, element3];

I need to insert these elements into a specific userId:

final results are as follows

UserId ItemId

2   ||  item1 
2   ||  item2
2   ||  item3

currently iam cycles through the array in php code and inserts each element one at a time, for example

foreach($items as $item)
{
insert into items (UserId,ItemId) value  (2,$item);
}

Is it possible that I can insert all the records in one query.

Answer 1

You can pre-create a query as follows:

$query = "";
foreach($items as $item)
{
  $query .= "insert into items (UserId,ItemId) value  (2,$item);";
}

Most databases allow you to issue several commands if you separate each semicolon.

Answer 2

Yes, your query might look like this:

INSERT INTO items (UserId,ItemId)
VALUES
(2, 'item1'),
(2, 'item2'),
(2, 'item3');

You can build this line in PHP.

Answer 3

// Prepare the items to be inserted
foreach($items as $item)
{
   $sString .= '(2,' . $item.'),';
}

// Remove the last char comma from the built string
$sString = substr($sString,0,-1);

$sqlQuery = "INSERT INTO items (UserId,ItemId) VALUES" . $sString;
mysql_query($sqlQuery);

Answer 4

Implode is perfect for this.

foreach($items as $item){
   $items[] = '(2,' . $item.')';
}

$q = "INSERT INTO items (userID, itemID) VALUES " . implode(', ', $items);
mysql_query($q);

See - http://php.net/manual/en/function.implode.php