PHP foreach isn't inserting

Question

I've been stuck on this for a while now and all the searches I did for this subject don't bring me the solution. This code inserts the value of the first product of the XML into MySQL table.

$xml=simplexml_load_file("URL") or die("Error: Cannot create object");

foreach ($xml->programs->products->product as $product);
foreach ($xml->programs->products->product->product_info->price as $price);

$insert = 'INSERT INTO BBB (price) VALUES (?)';
$insertStmt = $dbconnection->prepare($insert);
foreach ($product as $row) {
    $insertStmt->bind_param('s', $price);
}

$insertStmt->execute();

echo "New records created successfully";

After this I got the feedback that foreach doesn't work this way with prepared PHP statements. Thereafter I created this code:

$xml=simplexml_load_file("URL") or die("Error: Cannot create object");

$insert = 'INSERT INTO BBB (price) VALUES (?)';
$insertStmt = $dbconnection->prepare($insert);
foreach ($tags as $key=>$val) {
    if ($key == "product") {
        $dcprice = $key->product_info->price;
        $productranges = $val;
        for ($i=0; $i < count($productranges); $i+=2) {
            $offset = $productranges[$i] + 1;
            $len = $productranges[$i + 1] - $offset;
            $price[] = $dcprice(array_slice($values, $offset, $len));
        }
    } 
    $insertStmt->bind_param('s', $price);
    $insertStmt->execute();
}
echo "New records created successfully";

The code echoes that new records are created, but when I check the table in phpMyAdmin nothing appears.

Examples of links I've been checking are: How does PHP 'foreach' actually work?, foreach, xml_parse_into_struct. But I can't find the right piece of information, would be great if someone can point out what I'm doing wrong.

Answer 1

The following is not tested but I think it should give an idea. Create the prepared statement and bind a variable to the statement, then execute in a loop once you have declared the variable.

$xml=simplexml_load_file("URL") or die("Error: Cannot create object");

$sql = 'INSERT INTO BBB (price) VALUES (?)';
$stmt = $dbconnection->prepare( $sql );
$stmt->bind_param( 's', $price );


foreach ($tags as $key=>$val) {
    if ($key == "product") {
        $dcprice = $key->product_info->price;
        $productranges = $val;

        for($i=0; $i < count($productranges); $i+=2) {
            $offset = $productranges[$i] + 1;
            $len = $productranges[$i + 1] - $offset;

            $price = $dcprice(array_slice($values, $offset, $len));
            $stmt->execute();
        }
    }   
}
echo "New records created successfully";

Basic example of inserting multiple records.

$dbhost =   'localhost';
$dbuser =   'root'; 
$dbpwd  =   'xxx'; 
$dbname =   'experiments';

$db =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );


$sql='insert into `temp` (`val`) values (?)';
$stmt=$db->prepare( $sql );

if( $stmt ){
    $stmt->bind_param( 's', $i );
    for( $i=0; $i < 100; $i++ )$stmt->execute();

    $stmt->free_result();
    $stmt->close();
}

---

Using the actual data from the xml file ( but not using simpleXML )

$url='https://daisycon.io/datafeed/?filter_id=17940&settings_id=2510&demo';
$dom=new DOMDocument;
$dom->load( $url );

$xp=new DOMXPath( $dom );



$col=$dom->getElementsByTagName('product_info');
if( !empty( $col ) ){

    $dbhost =   'localhost';
    $dbuser =   'root'; 
    $dbpwd  =   'xxx'; 
    $dbname =   'experiments';

    $db =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );

    $sql='insert into `temp` (`brand`,`title`,`price`) values (?,?,?);';
    $stmt=$db->prepare( $sql );

    if( $stmt ){

        $stmt->bind_param( 'sss', $brand, $title, $price );

        foreach( $col as $i => $product ){
            $brand = $xp->query( 'brand', $product )->item( 0 )->nodeValue;
            $price = floatval( $xp->query( 'price', $product )->item( 0 )->nodeValue );
            $title = $xp->query( 'title', $product )->item( 0 )->nodeValue;

            $stmt->execute();
        }
        $stmt->free_result();
        $stmt->close();
    }
    $db=null;
}

This runs through the xml and inserts the records as one would expect - I hope that this helps.

---

Example of reading the XML and displaying results - NO database, just to show how to read records!

$url='https://daisycon.io/datafeed/?filter_id=17940&settings_id=2510&demo';
$dom=new DOMDocument;
$dom->load( $url );

$xp=new DOMXPath( $dom );

$col=$dom->getElementsByTagName('product_info');
if( !empty( $col ) ){
    foreach( $col as $i => $product ){
        $data=array(
            'brand' =>  $xp->query( 'brand', $product )->item( 0 )->nodeValue,
            'title' =>  $xp->query( 'title', $product )->item( 0 )->nodeValue,
            'price' =>  floatval( $xp->query( 'price', $product )->item( 0 )->nodeValue )
        );
        echo '<ul><li>',implode('</li><li>',$data),'</li></ul>';
    }
}

This then outputs:

<ul>
   <li>Dr. Fix</li>
   <li>Dr. Fix Organic Droge Voeten Balsem 75 ml</li>
   <li>11.95</li>
</ul>
<ul>
   <li>Lamberts</li>
   <li>Lamberts Selenium ACE 100 TAB</li>
   <li>19.1</li>
</ul>
<ul>
   <li>Annemarie Borlind</li>
   <li>Annemarie Borlind Lipcontourstift Red 1.05 G</li>
   <li>13.25</li>
</ul>
<ul>
   <li>Wapiti</li>
   <li>Wapiti Darmfunctie 20 dragees</li>
   <li>3.95</li>
</ul>
<ul>
   <li>Davitamon</li>
   <li>Davitamon Compleet Mama 60 capsules</li>
   <li>11.95</li>
</ul>
<ul>
   <li>Vitaminhealth</li>
   <li>Vitaminhealth Multi Vitaminen  30 tabletten</li>
   <li>9.95</li>
</ul>
<ul>
   <li>Vitaminhealth</li>
   <li>Vitaminhealth Multi Vitaminen  120 tabletten</li>
   <li>32.5</li>
</ul>
<ul>
   <li>Vitaminhealth</li>
   <li>Vitaminhealth Multi Vitaminen  240 tabletten</li>
   <li>58.95</li>
</ul>
<ul>
   <li>Vitaminhealth</li>
   <li>Vitaminhealth Botformule 60 tabletten</li>
   <li>12.95</li>
</ul>
<ul>
   <li>Vitaminhealth</li>
   <li>Vitaminhealth Betacaroteen 60 softgels</li>
   <li>16.95</li>
</ul>
<ul>
   <li>Brita</li>
   <li>Brita Optimax Cool Wit Waterfilterkan 1 exemplaar</li>
   <li>49.95</li>
</ul>
<ul>
   <li>Essential Organics</li>
   <li>Essential Organics Cal Mag &amp; Zink  90 tabletten</li>
   <li>11.5</li>
</ul>
<ul>
   <li>Vitotaal</li>
   <li>Vitotaal Canadese Geelwortel 45 vegicaps</li>
   <li>9.95</li>
</ul>
<ul>
   <li>Annemarie Borlind</li>
   <li>Annemarie Borlind Combination Skin Dagfluid  75 ml</li>
   <li>27.95</li>
</ul>
<ul>
   <li>Vitotaal</li>
   <li>Vitotaal Cranberry + C 45 vegicaps</li>
   <li>9.95</li>
</ul>
<ul>
   <li>Vital Cell Life</li>
   <li>Vital Cell Life Vitamine B6/B12 foliumzuur 60 cap</li>
   <li>12</li>
</ul>
<ul>
   <li>Brita</li>
   <li>Brita Marella XL Wit Waterfilterkan (3,5 liter) 1 exemplaar</li>
   <li>21.5</li>
</ul>
<ul>
   <li>Plantina</li>
   <li>Plantina Astaxanthin-eco 60 capsules</li>
   <li>33.1</li>
</ul>
<ul>
   <li>Terrasana</li>
   <li>Terrasana Umeboshi 150 G</li>
   <li>7.49</li>
</ul>
<ul>
   <li>Solgar Vitamins</li>
   <li>Solgar Vitamins Digestive Enzymes 250 tabletten</li>
   <li>39.9</li>
</ul>