iam having an array of items like
[item1,itmem2,item3];
i have to insert these items at a particular userId:
final results look like this
UserId ItemId
2 || item1
2 || item2
2 || item3
currently iam looping through the array in php code and inserting each item one by one eg
foreach($items as $item)
{
insert into items (UserId,ItemId) value (2,$item);
}
is it possible i can insert all entries in single query.
Yes, your query could look like this:
INSERT INTO items (UserId,ItemId)
VALUES
(2, 'item1'),
(2, 'item2'),
(2, 'item3');
You can construct that string in PHP.
// 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);
Implode is perfect for this..
foreach($items as $item){
$items[] = '(2,' . $item.')';
}
$q = "INSERT INTO items (userID, itemID) VALUES " . implode(', ', $items);
mysql_query($q);
You can pre build the query like so:
$query = "";
foreach($items as $item)
{
$query .= "insert into items (UserId,ItemId) value (2,$item);";
}
Most databases allow you to issue multiple commands if you separate each with a semicolon.
