Board index   FAQ   Search  
Register  Login
Board index PHP PHP Scripts

Problem with mysql query after parsing an xml file

Links for php scripts

Moderators: macek, egami, gesf

Problem with mysql query after parsing an xml file

Postby fozzo » Mon Dec 05, 2011 7:53 am

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.
fozzo
New php-forum User
New php-forum User
 
Posts: 3
Joined: Mon Dec 05, 2011 7:50 am

Re: Problem with mysql query after parsing an xml file

Postby egami » Mon Dec 05, 2011 9:33 am

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.
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2196
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: Problem with mysql query after parsing an xml file

Postby fozzo » Mon Dec 05, 2011 9:43 am

egami wrote: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.
fozzo
New php-forum User
New php-forum User
 
Posts: 3
Joined: Mon Dec 05, 2011 7:50 am

Re: Problem with mysql query after parsing an xml file

Postby egami » Mon Dec 05, 2011 9:53 am

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($parserXML_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($parserXML_OPTION_CASE_FOLDING0);
    
xml_parser_set_option($parserXML_OPTION_SKIP_WHITE1);
    
xml_parse_into_struct($parsertrim($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($tagarray_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.
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2196
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: Problem with mysql query after parsing an xml file

Postby fozzo » Mon Dec 05, 2011 10:10 am

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.
fozzo
New php-forum User
New php-forum User
 
Posts: 3
Joined: Mon Dec 05, 2011 7:50 am

Re: Problem with mysql query after parsing an xml file

Postby egami » Mon Dec 05, 2011 10:33 am

Do you understand the foreach() function?
I'm not being rude, just trying to figure out where you are.
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2196
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT


Return to PHP Scripts

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.