How do I open and use this xml file with php ?

[jeddiki]

Hi,

I am looking at a download from clickbank and I notice that
it has two files, a very small on suffixed with .dtd which I
list below, and a huge file 26 Mb suffixed with .xml

( not sure if I should use the "code" tags for these files–yes, you should, Mod)

Here is the .dtd

<!ELEMENT Catalog ( Category* ) >
<!ELEMENT Category ( Name, Site*, Category* ) >
<!ELEMENT Commission ( #PCDATA ) >
<!ELEMENT Description ( #PCDATA ) >
<!ELEMENT EarnedPerSale ( #PCDATA ) >
<!ELEMENT TotalEarningsPerSale ( #PCDATA ) >
<!ELEMENT TotalRebillAmt ( #PCDATA ) >
<!ELEMENT HasRecurringProducts ( #PCDATA ) >
<!ELEMENT Gravity ( #PCDATA ) >
<!ELEMENT Id ( #PCDATA ) >
<!ELEMENT Name ( #PCDATA ) >
<!ELEMENT PercentPerSale ( #PCDATA ) >
<!ELEMENT PopularityRank ( #PCDATA ) >
<!ELEMENT Referred ( #PCDATA ) >
<!ELEMENT Site ( Commission? | Description+ | EarnedPerSale? | TotalEarningsPerSale? | TotalRebillAmt? | Gravity? | Id+ | PercentPerSale? | PopularityRank+ | Referred? | Title+ | HasRecurringProducts )* >
<!ELEMENT Title ( #PCDATA ) >

And here is the first few lines on the .xml file.

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE Catalog SYSTEM "marketplace_feed_v1.dtd">
<Catalog>
<Category>
<Name>Business to Business</Name>
<Site>
<Id>REGEASY</Id>
<PopularityRank>1</PopularityRank>
<Title><![CDATA[Registry Easy - #1 Converting Registry Cleaner & System Optimizer.]]></Title>
<Description><![CDATA[Stunning Conversions With Extremely Low Refund Rate. Dedicated Affiliate Support. Extraordinary Customer Service. Any Kind Of Conversion Tracking & Multiple Landing Pages. Talk To Us! Http://www.cheesesoft.com/affiliates/registry-easy/.]]></Description>
<HasRecurringProducts>false</HasRecurringProducts>
<Gravity>226.333</Gravity>
<EarnedPerSale>31.7204</EarnedPerSale>
<PercentPerSale>75.0</PercentPerSale>
<TotalEarningsPerSale>31.7204</TotalEarningsPerSale>
<TotalRebillAmt>0.0</TotalRebillAmt>
<Referred>68.0</Referred>
<Commission>75</Commission>
</Site>
<Site>
<Id>BRYXEN4</Id>
<PopularityRank>2</PopularityRank>
<Title><![CDATA[Keyword Elite 2.0: The New Generation Of Keyword Research Software!]]></Title>
<Description><![CDATA[Dominate Adwords. Dominate Niche Marketing. Dominate The Search Engines. Go Here For Tons Of Affiliate Tools: Http://www.keywordelite.com/affiliate/.]]></Description>
<HasRecurringProducts>true</HasRecurringProducts>
<Gravity>229.6</Gravity>
<EarnedPerSale>65.1052</EarnedPerSale>
<PercentPerSale>48.0</PercentPerSale>
<TotalEarningsPerSale>74.1738</TotalEarningsPerSale>
<TotalRebillAmt>15.2186</TotalRebillAmt>
<Referred>79.0</Referred>
<Commission>50</Commission>
</Site>

Ok - so that shows the header info and the first two lines of data.

Now, the first line of the header info refers to the .dtd file.

I could use the info in the .dtd file to create a table
with columns ( fields) as it states.

Or I could just create the table structure from looking at the first few
records in the xml file that I have shown.

Once I have done that, I guess that I write a php script
to open the xml file and then step through each row and pull out the contents that is found between the tags.

As it finds each tag it can locate the contents and update the table.

So:

OPEN DBASE CONNECTION then:
 
$CB_file = file('clickbank.xml');
 
for($i=0; $i<count($CB_file); $i++) {
    $arrayOfLine = explode('???', $geo_arr[$i]);
 
    Update cbdb SET ????? = ??????
    $result = mysql_query($sql) or die("could not CBDB"). mysql_error();  
   break;
   }
}

Yes, I know that I have a lot of gaps to fill in

But, my question is, can this approach work with a
xml file of 28 Mb and based on the files that I have
can you please help me fill in the gaps.

PS I have searched and read up about XML -> MySQL but I didn' like the
look of XML-DOM or SAX-based parsers, and so would prefer to try and
get something "hand made" to work for my specific files.


Thanks for any input and help.

[zorgi]

Did you try simplexml?

http://php.net/manual/en/book.simplexml.php

Very easy to use. Converts XML to object. Only available from PHP5

[jeddiki]

Hi,
Thanks for the tip.

I have read it and as far as I can tell
this is what I need to do:

$CB_file = file('clickbank.xml');
 
$xmlstr ="<<<XML ".$CB_file." XML";
 
// ( do I have to ass line breaks at all ? )
 
// Then I continue to check validity:
 
$xmlObject = simplexml_load_string($xmlstr); 
 
// not sure about how I go to this next line
 
$xml = new SimpleXMLElement($xmlstr);
 

After this I guess that I need a foreach loop to work through the
whole file ?

[zorgi]

If you know path to your xml file you can do this:

$xml = simplexml_load_file('clickbank.xml');
 

$xml is now object and you could do something like this:

    foreach($xml->Category as $category){
            echo $category->Name."<br />";            
            foreach($category->Site as $site){
                echo $site->Id."<br />";
            }
    }
 

[jeddiki]

Thanks
I will work with what you have given me. :)

So are you saying that I don't need to bother with:
checking validity?

# $xmlObject = simplexml_load_string($xmlstr);

[zorgi]

simplexml_load_file — Interprets an XML file into an object

http://php.net/manual/en/function.si...-load-file.php

extracts from that page in the manual:

• Convert the well-formed XML document in the given file to an object.
• Produces an E_WARNING error message for each error found in the XML data.

There is also loads of tutorials on line for simplexml:
http://www.phpfever.com/simplexml-tutorial-part1.html

[Dormilich]

@jeddiki
I don’t think you can explicitly validate the XML against the DTD with SimpleXML (well, it’s not necessary in most cases)

[jeddiki]

Thanks, I read that tute.

I now have something close to working :)

But I get and error on trying to write to the database:
it may be becasue I need to convert the data ?

$sql = "INSERT INTO `clickbank` (cat,id,pop)
 VALUES                              ('$category->Name','$site->Id','$site->PopularityRank')";
 

I noticed in the tute it said something that might apply:

It gave this example:

      $xml = ‘test_file.xml’;
      $xml = simplexml_load_file($xml);
      $value_to_store = (string) $xml->make[0]->model;
      // This converts the "Mustang" SimpleXMLElement object to a string, making it disk storable.
 

Does this mean that I have to do this:
$Db_id = (string) $xml->Category->$site->Id;
for each field?

And is this enough? Or do I need to add counters to keep track of which row is being processed and then use something like:

$Db_id = (string) $xml->Category[$cnt1]->$site->Id;


The error message I get from the script is :

could not execute INSERT set up clients.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc,recurr,grav,earn,percent,totearn,rebill,refe r,comm) VALUES ' at line 1

[zorgi]

gettype( $category->Name ) results in object but
gettype( '$category->Name' ) results in sting

Based on that I suspect that your problem is not XML related any more but somewhere within you sql statement.

[jeddiki]

Thanks,

I have looked again bıt I can not see anything wrong:

This is my code:

$file = 'clickbank.xml';
$xml = simplexml_load_file($file);
$cnt1 = "0";
$cnt2 = "0";
 
 
foreach($xml->Category as $category){
   echo "$cnt1) ".$category->Name."<br>";
   $cnt1 = $cnt1+1;            
   foreach($category->Site as $site){
      echo "$cnt2) ". $site->Id, $site->PopularityRank, $site->Title,    
     $site->Description, $site->HasRecurringProducts, $site->Gravity, 
     $site->EarnedPerSale, $site->PercentPerSale, $site->TotalEarningsPerSale, $site->TotalRebillAmt, $site->Referred, $site->Commission."<br><br> ";
 
 
     $sql = "INSERT INTO clickbank   
         (cat,id,pop,title,desc,recurr,grav,earn,percent,totearn,rebill,refer,comm)
        VALUES     
      ('$category->Name','$site->Id','$site->PopularityRank','$site->Title','$site->Description','$site->HasRecurringProducts','$site->Gravity','$site->EarnedPerSale','$site->PercentPerSale','$site->TotalEarningsPerSale','$site->TotalRebillAmt','$site->Referred','$site->Commission')";
 
$result = mysql_query($sql) or die("could not execute INSERT set up clients.". mysql_error());  
         $cnt2 = $cnt2+1;
             }
     }
 

And this is the output:

0) Business to Business
0) REGEASY1Registry Easy - #1 Converting Registry Cleaner & System Optimizer.Stunning Conversions With Extremely Low Refund Rate. Dedicated Affiliate Support. Extraordinary Customer Service. Any Kind Of Conversion Tracking & Multiple Landing Pages. Talk To Us! Http://www.cheesesoft.com/affiliates....72040.068.075

could not execute INSERT set up clients.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc,recurr,grav,earn,percent,totearn,rebill,refe r,comm) VALUES ' at line 1

Maybe there is a reserved name I have used for a field or something ?

[zorgi]

What is structure of your clickbank table?

[jeddiki]

I added this:

print '<pre>'.$sql.'</pre>'; 

And I got this result:

INSERT INTO clickbank (cat,id,pop,title,desc,recurr,grav,earn,percent,to tearn,rebill,refer,comm)
VALUES
('Business to Business','REGEASY','1','Registry Easy - #1 Converting Registry Cleaner & System Optimizer.','Stunning Conversions With Extremely Low Refund Rate. Dedicated Affiliate Support. Extraordinary Customer Service. Any Kind Of Conversion Tracking & Multiple Landing Pages. Talk To Us! Http://www.cheesesoft.com/affiliates...0','75')

Can anyone see any problem ?

The numbers look a bit odd!

Could the problem be that grav is set up in the table as:

double(5.2) maybe the 31.7204 doesn't fit.

Actually I don't understand that number, is it supposed be 31720.40 dollars or 317,204 dollars ? Or only 31.72 dollars ?

Or maybe it is something in that long description ?

[zorgi]

@jeddiki
From the look of that first thing that comes to mind is that something is wrong with your desc field, probably too short.

Also you should do casting of your INSERT types to match those in the database table or change your tables types all to varchar. Not sure what is better but something should be done.

What is your table structure?

[jeddiki]

OK - think I have found the problem

I think it was the field named "desc" because it can be used in the ORDER part of sql command.

It was a guess but when I changed the name to "descrip" the first three rows get processed OK

This is my file structure now:

$sql = "CREATE TABLE `clickbank` (
        `cb_id` smallint(8) NOT NULL AUTO_INCREMENT,
     `id` varchar(10) NOT NULL default 'none',
        `cat` varchar(50) NOT NULL default 'none',
    `pop` smallint(8) NOT NULL default '1',
    `title` varchar(100) NOT NULL default 'n',
    `descrip` varchar(300) NOT NULL default 'n',
    `recurr` char(1) NOT NULL default 'n',
    `grav` double(10,2) NOT NULL default '99.99',
    `earn`  double(10,2) NOT NULL default '99.99',
    `percent`  double(5,2) NOT NULL default '99.99',
    `totearn`  double(10,2) NOT NULL default '99.99',
    `rebill`  double(10,2) NOT NULL default '99.99',
    `refer`  double(10,2) NOT NULL default '99.99',
    `comm`  double(5,2) NOT NULL default '99.99',
    PRIMARY KEY (cb_id)
 

I still have a problem and it seems to be caused by single quotes in the description data.

This is my out put

0) Business to Business
0) REGEASY1Registry Easy - #1 Converting Registry Cleaner & System Optimizer.Stunning Conversions With Extremely Low Refund Rate. Dedicated Affiliate Support. Extraordinary Customer Service. Any Kind Of Conversion Tracking & Multiple Landing Pages. Talk To Us! Http://www.cheesesoft.com/affiliates....72040.068.075

INSERT INTO clickbank ( cat, id, pop, title, descrip, recurr, grav, earn, percent, totearn, rebill, refer, comm )
VALUES ( 'Business to Business', 'REGEASY', '1', 'Registry Easy - #1 Converting Registry Cleaner & System Optimizer.', 'Stunning Conversions With Extremely Low Refund Rate. Dedicated Affiliate Support. Extraordinary Customer Service. Any Kind Of Conversion Tracking & Multiple Landing Pages. Talk To Us! Http://www.cheesesoft.com/affiliates/registry-easy/.', 'false',
'226.333', '31.7204', '75.0', '31.7204', '0.0', '68.0', '75' )

1) BRYXEN42Keyword Elite 2.0: The New Generation Of Keyword Research Software!Dominate Adwords. Dominate Niche Marketing. Dominate The Search Engines. Go Here For Tons Of Affiliate Tools: Http://www.keywordelite.com/affiliat...815.218679.050

INSERT INTO clickbank ( cat, id, pop, title, descrip, recurr, grav, earn, percent, totearn, rebill, refer, comm )
VALUES ( 'Business to Business', 'BRYXEN4', '2', 'Keyword Elite 2.0: The New Generation Of Keyword Research Software!', 'Dominate Adwords. Dominate Niche Marketing. Dominate The Search Engines. Go Here For Tons Of Affiliate Tools: Http://www.keywordelite.com/affiliate/.', 'true', '229.6', '65.1052', '48.0', '74.1738', '15.2186', '79.0', '50' )

2) MAVERICKCO3Maverick Coaching - Cell Phone Cash.Cell Phone Cash: A Brand New Course By Maverick Coaching Members Are Making At Least $279/Day With Cell Phones! Customers Get Our 'Make Money Or Its Free' Guarantee, 24/7 Phone Support! Affiliates: Http://cellphonecash.maverickcoachin...212.563486.050

INSERT INTO clickbank ( cat, id, pop, title, descrip, recurr, grav, earn, percent, totearn,rebill, refer, comm )
VALUES ( 'Business to Business', 'MAVERICKCO', '3', 'Maverick Coaching - Cell Phone Cash.', 'Cell Phone Cash: A Brand New Course By Maverick Coaching Members Are Making At Least $279/Day With Cell Phones! Customers Get Our 'Make Money Or Its Free' Guarantee, 24/7 Phone Support! Affiliates: ttp://cellphonecash.maverickcoaching.com/affiliates.php.', 'true',
'674.459', '12.9828', '50.0', '25.5462', '12.5634', '86.0', '50' )

could not execute INSERT set up clients.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Make Money Or Its Free' Guarantee, 24/7 Phone Support! Affiliates: Http://cellph' at line 4

[Dormilich]

you have to escape single quotes inside single quotes, if you don’t want to break the string.