Import PARAM values of PRODUCT from xml to DB table using PHP-CodePudding

I am trying to import product parameters (param_name, val) from the product feed for particular products to DB tables. As a PHP beginner, my code does not return/insert any data :( Example of product feed example.php:

$xmlstr = <<<XML
<?xml version='1.0' standalone='yes'?>
<SHOP>
    <SHOPITEM>
        <ITEM_ID>product_1</ITEM_ID>
        <PARAM>
            <PARAM_NAME>param_1</PARAM_NAME>
            <VAL>value_1</VAL>
        </PARAM>                            
        <PARAM>
            <PARAM_NAME>param_2</PARAM_NAME>
            <VAL>value_2</VAL>
        </PARAM>                                        
    </SHOPITEM>
    <SHOPITEM>
        <ITEM_ID>product_2</ITEM_ID>
        <PARAM>
            <PARAM_NAME>param_3</PARAM_NAME>
            <VAL>value_3</VAL>
        </PARAM>                            
        <PARAM>
            <PARAM_NAME>param_4</PARAM_NAME>
            <VAL>value_4</VAL>
        </PARAM>
        <PARAM>
            <PARAM_NAME>param_5</PARAM_NAME>
            <VAL>value_5</VAL>
        </PARAM>                                       
    </SHOPITEM>
</SHOP>
XML;
?>

Part of my code:

include 'example.php';
$file = new SimpleXMLElement($xmlstr);

foreach ($file->SHOPITEM as $shopitem) {
    $item_id  = $shopitem->ITEM_ID;
    foreach ($shopitem->PARAM as $param) {
$param_name = (string)$param->PARAM_NAME;
$param_val  = (string)$param->VAL;
    }
}

$sql = "INSERT INTO PRODUCTS_PARAMS (ITEM_ID,PARAM_NAME,VAL) 
    VALUES ('" . $item_id . "','" . $param_name . "','" . $param_val . "')";

I will be grateful for each piece of advice. Thank you.

CodePudding user response：

your foreach is going through the many SHOPITEM and setting the variables $param_name and $param_val, But, when it comes to the end of the foreach you have done nothing with these values, so the foreach repeats, $param_name and $param_val, receive new values, and this goes on and on.

your insert for the DB should be inside the foreach:

foreach ($file->SHOPITEM as $shopitem) {
    $item_id  = $shopitem->ITEM_ID;
    foreach ($shopitem->PARAM as $param) {
$param_name = (string)$param->PARAM_NAME;
$param_val  = (string)$param->VAL;

  ///---here goes the code to insert each row
    }
}

CodePudding user response：

The sql you generated never got executed and could potentially yield errors depending upon the actual contents of the XML. It would be easier to use a prepared statement that is executed within the nested foreach loop.

include 'example.php';

# create the SQL for a prepared statement & generate Prepared Statement ( PDO )
$sql='insert into `products_params` ( `item_id`, `param_name`, `val` ) values ( :id, :name, :value )';
$stmt=$db->prepare( $sql );

Using SimpleXML

$file = new SimpleXMLElement( $xmlstr );

foreach( $file->SHOPITEM as $shopitem ) {
    $id = $shopitem->ITEM_ID;
    foreach( $shopitem->PARAM as $param ) {
        $name = (string)$param->PARAM_NAME;
        $value  = (string)$param->VAL;
        
        $args=array(
            ':id'       =>  $id,
            ':name'     =>  $name,
            ':value'    =>  $value
        );
        $stmt->execute( $args );
    }
}

Or, an alternative using DOMDocument

$dom=new DOMDocument;
$dom->loadXML( $xmlstr );

$col=$dom->getElementsByTagName('SHOPITEM');
foreach( $col as $node ){
    
    $id=$node->getElementsByTagName('ITEM_ID')[0]->nodeValue;
    $params=$node->getElementsByTagName('PARAM');
    
    foreach( $params as $param ){
        $name=$param->getElementsByTagName('PARAM_NAME')[0]->nodeValue;
        $value=$param->getElementsByTagName('VAL')[0]->nodeValue;
        
        $args=array(
            ':id'       =>  $id,
            ':name'     =>  $name,
            ':value'    =>  $value
        );
        $stmt->execute( $args );
    }
}

If you are using mySQLi rather than PDO ( as shown above ) then the alternative prepared statement would be:

    # mysqli prepared statement with bound placeholders
    $sql='insert into `products_params` ( `item_id`, `param_name`, `val` ) values ( ?,?,? )';
    $stmt->bind_param('sss', $id, $name, $value );
    
    # then, within nested foreach loop
    # once variables ( $id, $name & $value ) are defined  simply:
    $stmt->execute();