SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    PHP Code:
        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";
        } 

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,187
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)

    stmt

    Hi Alshie, welcome to the forums,
    Instead of "reset" try "close" in the loop.

  3. #3
    SitePoint Evangelist catweasel's Avatar
    Join Date
    Apr 2007
    Location
    Goldfields, VIC, Australia
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Alshie View Post
    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.
    a much more efficient way of doing multi inserts using prepared statements -
    PHP Code:
    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.

  4. #4
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    PHP Code:
    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?

  5. #5
    SitePoint Evangelist catweasel's Avatar
    Join Date
    Apr 2007
    Location
    Goldfields, VIC, Australia
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Alshie View Post
    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
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •