Multiple inserts with mysqli

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.

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.