Problem with mysql query after parsing an xml file

[fozzo]

Hello to all you guys,
i'm working with ebay api, and i'm using call GetOrders.
I'm actually having a problem in storing the xml response into mysql database.
I already parsed it correctly but i cannot figure out how to put data in db.
I've tried to put all data in array but all i can do is write in db only the first row, instead i need to loop all avaiable rows.
The targer is to create a query wich will create a row for any SellingManagerSalesRecordNumber contained in TransactionArray
container with associated SellingManagerSalesRecordNumber cointained in top level (in Orders->ShippingDetails)

For example first Order has two transaction so the result of the query would be to create two rows:

<OrderArray>
<Order>
<ShippingDetails>
<SellingManagerSalesRecordNumber>12658</SellingManagerSalesRecordNumber>
</ShippingDetails>
<TransactionArray>
<Transaction>
<ShippingDetails>
<SellingManagerSalesRecordNumber>12365</SellingManagerSalesRecordNumber>
</ShippingDetails>
</Transaction>
<Transaction>
<ShippingDetails>
<SellingManagerSalesRecordNumber>12398</SellingManagerSalesRecordNumber>
</ShippingDetails>
</Transaction>
</TransactionArray>
</Order>
<Order>
<ShippingDetails>
<SellingManagerSalesRecordNumber>12678</SellingManagerSalesRecordNumber>
</ShippingDetails>
<TransactionArray>
<Transaction>
<ShippingDetails>
<SellingManagerSalesRecordNumber>12678</SellingManagerSalesRecordNumber>
</ShippingDetails>
</Transaction>
</TransactionArray>
</Order>
</OrderArray>


SellingManagerSalesRecordNumber_int | SellingManagerSalesRecordNumber
12635 12658
12398 12658

Instead second order would create only a row:

SellingManagerSalesRecordNumber_int | SellingManagerSalesRecordNumber
12635 12658
12398 12658
12678 12678

Actually parsing it would give as result:

12658|12635|12398|12678|12678

without pipe.

Do you have any clues on how to do that?

Any help would be apreciated.
Thanks in advance.

[egami]

do a quick google search for xml2array() function.

I downloaded one a while back and it seems to work extremely well. If you have troubles locating it, I may be able to dig and find it.

[fozzo]

do a quick google search for xml2array() function.

I downloaded one a while back and it seems to work extremely well. If you have troubles locating it, I may be able to dig and find it.

Thanks for your answer. Actually this work but i'm still at dead line i was before.
I can't sort out how the query should be done to import data into mysql db.
Actually the first salesmanagernumber is like the orderID.
eBay system generate a number for each transaction.
If a costumer purchase more than an item and require multiple shipping so eBay will generate a new orderID (salesmanagernumber) wich is correlate with the two (or more) "old" salesmanagernumber.
If otherwise the order contain only 1 item so both Order->shippingdetails-salesmanagernumber and order->transactionarray->transaction->shippingdetails->salesmanagernumber will be the same.
I would like to make 1 row each 'TransactionArray/Transaction/ShippingDetails/SellingManagerSalesRecordNumber' with the associated
'ShippingDetails/SellingManagerSalesRecordNumber'

The table schema is like this:

CREATE TABLE IF NOT EXISTS `ebay_sales_to_products` (
`SellingManagerSalesRecordNumber_int` int(11) unsigned NOT NULL,
`SellingManagerSalesRecordNumber` varchar(11) NOT NULL,
PRIMARY KEY (`SellingManagerSalesRecordNumber_int`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

where SellingManagerSalesRecordNumber_int -> 'TransactionArray/Transaction/ShippingDetails/SellingManagerSalesRecordNumber'

Thanks in advance.

[egami]

Again, this is code I found on the Internet, I didn't write this. So the credit goes to them, not to me.

Code: Select all

function xml2array($contents, $get_attributes=1, $priority = 'tag') {
    if(!$contents) return -1;

    if(!function_exists('xml_parser_create')) {
        //print "'xml_parser_create()' function not found!";
        return -1;
    }

    //Get the XML parser of PHP - PHP must have this module for the parser to work
    $parser = xml_parser_create('');
    xml_parser_set_option($parser, XML_OPTION_TARGET_ENCODING, "UTF-8"); # http://minutillo.com/steve/weblog/2004/6/17/php-xml-and-character-encodings-a-tale-of-sadness-rage-and-data-loss
    xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, 0);
    xml_parser_set_option($parser, XML_OPTION_SKIP_WHITE, 1);
    xml_parse_into_struct($parser, trim($contents), $xml_values);
    xml_parser_free($parser);

    if(!$xml_values) return;//Hmm...

    //Initializations
    $xml_array = array();
    $parents = array();
    $opened_tags = array();
    $arr = array();

    $current = &$xml_array; //Refference

    //Go through the tags.
    $repeated_tag_index = array();//Multiple tags with same name will be turned into an array
    foreach($xml_values as $data) {
        unset($attributes,$value);//Remove existing values, or there will be trouble

        //This command will extract these variables into the foreach scope
        // tag(string), type(string), level(int), attributes(array).
        extract($data);//We could use the array by itself, but this cooler.

        $result = array();
        $attributes_data = array();

        if(isset($value)) {
            if($priority == 'tag') $result = $value;
            else $result['value'] = $value; //Put the value in a assoc array if we are in the 'Attribute' mode
        }

        //Set the attributes too.
        if(isset($attributes) and $get_attributes) {
            foreach($attributes as $attr => $val) {
                if($priority == 'tag') $attributes_data[$attr] = $val;
                else $result['attr'][$attr] = $val; //Set all the attributes in a array called 'attr'
            }
        }

        //See tag status and do the needed.
        if($type == "open") {//The starting of the tag '<tag>'
            $parent[$level-1] = &$current;
            if(!is_array($current) or (!in_array($tag, array_keys($current)))) { //Insert New tag
                $current[$tag] = $result;
                if($attributes_data) $current[$tag. '_attr'] = $attributes_data;
                $repeated_tag_index[$tag.'_'.$level] = 1;

                $current = &$current[$tag];

            } else { //There was another element with the same tag name

                if(isset($current[$tag][0])) {//If there is a 0th element it is already an array
                    $current[$tag][$repeated_tag_index[$tag.'_'.$level]] = $result;
                    $repeated_tag_index[$tag.'_'.$level]++;
                } else {//This section will make the value an array if multiple tags with the same name appear together
                    $current[$tag] = array($current[$tag],$result);//This will combine the existing item and the new item together to make an array
                    $repeated_tag_index[$tag.'_'.$level] = 2;

                    if(isset($current[$tag.'_attr'])) { //The attribute of the last(0th) tag must be moved as well
                        $current[$tag]['0_attr'] = $current[$tag.'_attr'];
                        unset($current[$tag.'_attr']);
                    }

                }
                $last_item_index = $repeated_tag_index[$tag.'_'.$level]-1;
                $current = &$current[$tag][$last_item_index];
            }

        } elseif($type == "complete") { //Tags that ends in 1 line '<tag />'
            //See if the key is already taken.
            if(!isset($current[$tag])) { //New Key
                $current[$tag] = $result;
                $repeated_tag_index[$tag.'_'.$level] = 1;
                if($priority == 'tag' and $attributes_data) $current[$tag. '_attr'] = $attributes_data;

            } else { //If taken, put all things inside a list(array)
                if(isset($current[$tag][0]) and is_array($current[$tag])) {//If it is already an array...

                    // ...push the new element into that array.
                    $current[$tag][$repeated_tag_index[$tag.'_'.$level]] = $result;

                    if($priority == 'tag' and $get_attributes and $attributes_data) {
                        $current[$tag][$repeated_tag_index[$tag.'_'.$level] . '_attr'] = $attributes_data;
                    }
                    $repeated_tag_index[$tag.'_'.$level]++;

                } else { //If it is not an array...
                    $current[$tag] = array($current[$tag],$result); //...Make it an array using using the existing value and the new value
                    $repeated_tag_index[$tag.'_'.$level] = 1;
                    if($priority == 'tag' and $get_attributes) {
                        if(isset($current[$tag.'_attr'])) { //The attribute of the last(0th) tag must be moved as well

                            $current[$tag]['0_attr'] = $current[$tag.'_attr'];
                            unset($current[$tag.'_attr']);
                        }

                        if($attributes_data) {
                            $current[$tag][$repeated_tag_index[$tag.'_'.$level] . '_attr'] = $attributes_data;
                        }
                    }
                    $repeated_tag_index[$tag.'_'.$level]++; //0 and 1 index is already taken
                }
            }

        } elseif($type == 'close') { //End of tag '</tag>'
            $current = &$parent[$level-1];
        }
    }

    return($xml_array);
}
 

This will create the array, a usable array that PHP understands.
Then you loop through the array, and create your DB row, one record at a time.

ie..
foreach($array as $key => $subarray) {
do this and that..
}


enjoy.

[fozzo]

That's exactly the function i was using that give me that:

http://www.sefelectronics.com/ebay/ebay/xml2array.php

but i still can't understand how to make the query, sorry to bother you.

[egami]

Do you understand the foreach() function?
I'm not being rude, just trying to figure out where you are.