Alshie
April 15, 2007, 10:42pm
1
Hi,
I’m trying to insert multiple rows into a database via mysqli. The information is pulled from an xml source and a loop should insert the data into the database but only the first entry is recorded.
I’ve never used mysqli before so I think I may be doing something wrong there? I’ve trimmed the code below for brevity!
Appreciate any help.
foreach ($xml->SearchResultItemArray->SearchResultItem as $searchitem)
{
$itemID = $searchitem->Item->ItemID;
$title = $searchitem->Item->Title;
$link = $searchitem->Item->ListingDetails->ViewItemURL;
$current_price = $searchitem->Item->SellingStatus->CurrentPrice;
$bid_count = $searchitem->Item->SellingStatus->BidCount;
$sql = 'INSERT INTO uk_boards (itemID, title, link, current_price, bid_count) VALUES (?, ?, ?, ?, ?)';
$stmt = $conn->stmt_init();
if ($stmt->prepare($sql))
{
// bind params and execute statement
$stmt->bind_param('issdi', $itemID, $title, $link, $current_price, $bid_count);
$stmt->execute();
$stmt->reset();
}
}
}
else {
$results = "No xml";
}
Hi Alshie, welcome to the forums,
Instead of “reset” try “close” in the loop.
a much more efficient way of doing multi inserts using prepared statements -
if (is_array($xml->SearchResultItemArray->SearchResultItem)) {
$sql = "INSERT INTO uk_boards (itemID, title, link, current_price, bid_count) VALUES (?, ?, ?, ?, ?)";
if ($stmt = $conn->prepare($sql)) {
$stmt->bind_param('issdi', $itemID, $title, $link, $current_price, $bid_count);
foreach ($xml->SearchResultItemArray->SearchResultItem as $searchitem) {
$itemID = $searchitem->Item->ItemID;
$title = $searchitem->Item->Title;
$link = $searchitem->Item->ListingDetails->ViewItemURL;
$current_price = $searchitem->Item->SellingStatus->CurrentPrice;
$bid_count = $searchitem->Item->SellingStatus->BidCount;
$stmt->execute();
}
$stmt->close();
}
} else {
$results = "No xml";
}
This way you prepare the statement only once and just re-use it for each insert.
Alshie
April 16, 2007, 10:56pm
4
Thanks for the suggestions.
Mittineague> I had tried close previously, I must have been trying reset as an alternative?
Catweasel> Thanks, your code did not quite work for me, but it pushed me in the right direction. I could not get the items to add to the DB at all until I moved the binding inside the loop
foreach ($xml->SearchResultItemArray->SearchResultItem as $searchitem)
{
$itemID = $searchitem->Item->ItemID;
$title = $searchitem->Item->Title;
$link = $searchitem->Item->ListingDetails->ViewItemURL;
$current_price = $searchitem->Item->SellingStatus->CurrentPrice;
$bid_count = $searchitem->Item->SellingStatus->BidCount;
$stmt->bind_param('issdi', $itemID, $title, $link, $current_price, $bid_count)
$stmt->execute();
}
Now it is adding all the details ok, assume I am losing some efficiency though?
shouldn’t make any difference… you could try using -
echo $stmt->error;
right after the $stmt->execute() line to see if there are any errors.
Now it is adding all the details ok, assume I am losing some efficiency though?
Probably but I doubt you’d notice any performance loss. The main thing is to keep the prepare() call outside the loop.