My php script is not picking up the xml info.

habbardone

Hı
I am trying to read the lastest clickbank xml file
but I am not picking up the categories properly 🙁

I am using a simple script to take this data and write it
to a mysql table. The idea is to wite the SITE details only one and if it appears again in the xml then only add the category and sub category info.

The problem:
It looks like my script is not picking up the sub-categories,
but I am not sure how I need to change it.

I think I need another level for the sub-cat ?

This is what I have.

$xml = simplexml_load_file($file);
$cnt = 0;

foreach($xml->Category as $category){
   foreach($category->Site as $site){

  $sql_ck = "SELECT cb_id FROM cb_update WHERE id = '$site->Id' AND day_no = '$this_day'";
  $result_ck = mysql_query($sql_ck)
	or die("could not FIND ID in cb_update.". mysql_error());  

$num = mysql_num_rows($result_ck);

if($num == 0) {
    $sql_ins = "INSERT INTO cb_update ( cb_date, day_no, id, title, descrip, recurr, grav, earn, percent, totearn, rebill, refer, comm )
     VALUES ( '$today', '$this_day','$site->Id','$title', '$descrip', '$site->HasRecurringProducts', '$site->Gravity', '$site->EarnedPerSale', '$site->PercentPerSale', '$site->TotalEarningsPerSale', '$site->TotalRebillAmt', '$site->Referred', '$site->Commission' )";

     $result_ins = mysql_query($sql_ins) or die("could not execute INSERT to clicky.". mysql_error());  

     $sql_ins = "INSERT INTO cb_cat_update (id_cat, cat, pop, day_no_cat)
     VALUES ('$site->Id', '$category->Name', '$site->PopularityRank', '$this_day')";	

      $result_ins = mysql_query($sql_ins) or die("could not execute INSERT to cb_cat_update.". mysql_error());		

          }
    else {          //  If the SITE has already been written just write the cat and sub-cat

     $sql_ins = "INSERT INTO cb_cat_update (id_cat, cat, pop)
     VALUES ('$site->Id', '$category->Name', '$site->PopularityRank')";	

     $result_ins = mysql_query($sql_ins) or die("could not execute INSERT to cl_cat.". mysql_error());		 

	 }
 $cnt++;
   }
 }

This is the start of 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>
        * SITE INFO TAGS
      </Site>
      <Category>
         <Name>Education</Name>
         <Site>
         </Category>
         <Category>
         <Name>Publishing</Name>
         <Site>
            * SITE INFO TAGS
         </Site>
       </Category>
  </Category>
  <Category>
     <Name>Society &amp; Culture</Name>
     <Site>

As you can see, the "Category" Tag is used twice,
once for the main category andalso for the sub-category

A bit confusing.

I have updated my table to include the sub-category:
INSERT INTO cb_cat_update (id_cat, cat, sub-cat, pop, day_no_cat)

Would appreciate any help so that I can extract the
category AND sub category.

Thanks.

halojoy

Try to do a dump of $xml
And see what values it holds.

$xml = simplexml_load_file($file);
echo '<pre>';
print_r( $xml ); // dump contents of $xml
echo '</pre>';

$cnt = 0;

habbardone

Thanks ...
... but ...

.....you just made my computer blow up :mad:

Well, nearly

Fatal error: Allowed memory size of 50331648 bytes exhausted (tried to allocate 56 bytes) in /home/guru54gt5/public_html/sys/auto_cb_update.php on line 142

Anyway, there is a small eror in the xml .

It should read:

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE Catalog SYSTEM "marketplace_feed_v1.dtd">
<Catalog>
  <Category>
     <Name>Business to Business</Name>
     <Site>
        * SITE INFO TAGS
      </Site>
      <Category>
         <Name>Education</Name>
         <Site>
           * SITE INFO TAGS
         </Site>
         </Category>
         <Category>
         <Name>Publishing</Name>
         <Site>
            * SITE INFO TAGS
         </Site>
       </Category>
  </Category>
  <Category>
     <Name>Society &amp; Culture</Name>
     <Site>
        * SITE INFO TAGS
      </Site>

Any more help ?

Installer

Try using xpath:

$xml = simplexml_file($file);

foreach ($xml->xpath('/Catalog/Category') as $top_cat) {
    echo $top_cat->Name . '<br />';
    foreach ($top_cat->xpath('Category') as $sub_cat) {
        echo $sub_cat->Name . '<br />';
    }
}

habbardone

Thanks,
I will try that.

BTW

Do I need to use $xml = simplexml_load_file($file);

which is what I was using,
or is $xml = simplexml_file($file);

OK ?

Installer

That's just my typo. You'd get a fatal error with it, anyway.

habbardone

I made a nice list of the categoriesand sub-cats
with your code:

foreach ($xml->xpath('/Catalog/Category') as $top_cat) {
  echo "<b>$top_cat->Name</b> <br>";
    foreach ($top_cat->xpath('Category') as $sub_cat) {
       echo " ... $sub_cat->Name<br>";

But I am not sure how to reference the site tags

Is this the right thing to do ?

foreach ($xml->xpath('/Catalog/Category') as $top_cat) {
    foreach ($top_cat->xpath('Category') as $sub_cat) {
        foreach($sub_cat->Site as $site){

 }
   }
}

Or should that be:

foreach($sub_cat->xpath('Site') as $site){

I am a bit confused ... 😕

Installer

They're siblings of the Name nodes, so you'd access them the same way as Name:

foreach ($xml->xpath('/Catalog/Category') as $top_cat) {
    echo $top_cat->Name . '<br />';
    echo $top_cat->Site . '<br />';
    foreach ($top_cat->xpath('Category') as $sub_cat) {
        echo $sub_cat->Name . '<br />';
        echo $sub_cat->Site . '<br />';
    }
}

habbardone

Probably I did not give enough info
about the site tag. It does not contain data but just more tags.

<Category>
         <Name>Education</Name>
         <Site>
              <Id>REGEASY</Id>
              <PopularityRank>1</PopularityRank>
              <Title><![CDATA[A title here]]></Title>
             <Description><![CDATA[Some words here]]></Description>
            <HasRecurringProducts>false</HasRecurringProducts>
            <Commission>75</Commission>
        </Site>
 </Category>

So I think I must need something like a path to reference those
internal tags like <Title> ??

Installer

Yes, you can keep drilling down with xpath:

$xml = simplexml_load_file($file);
foreach ($xml->xpath('/Catalog/Category') as $top_cat) {
    echo $top_cat->Name . '<br />';
    echo $top_cat->Site . '<br />';
    foreach ($top_cat->xpath('Category') as $sub_cat) {
        echo $sub_cat->Name . '<br />';
        foreach ($sub_cat->xpath('Site') as $site) {
            echo $site->PopularityRank . '<br />';
            echo $site->Title . '<br />';
            echo $site->Description . '<br />';
            echo $site->HasRecurringProducts . '<br />';
            echo $site->Commission . '<br />';
        }
    }
}

One thing to remember is that SimpleXMLElement::xpath() returns an array of SimpleXMLElement objects. You can echo each of them as above because, when echoed, a __tostring() conversion is implicitly made. But if you want to store them as strings in variables, or use them in another way where the conversion isn't implied, make the conversion explicit.

e.g.

$name = (string) $top_cat->Name;

habbardone

Thanks,

Hopefully I will be able to complete it now 🙂

It's time for bed though, so do it tomorrow !

habbardone

I am just thinking,

Since there are SITES at both levels, I guess that I
need to put a path in twice ?

The loop you wrote will only pick out and echo the deepest
sites that are under sub-cats. the sites that are just one level deep,
i.e. only under categories do not appear to be referenced.
( unless I am mis-reading it )

$xml = simplexml_load_file($file);
foreach ($xml->xpath('/Catalog/Category') as $top_cat) {
    echo $top_cat->Name . '<br />';
    echo $top_cat->Site . '<br />';
    foreach ($top_cat->xpath('Category') as $sub_cat) {
        echo $sub_cat->Name . '<br />';
        foreach ($sub_cat->xpath('Site') as $site) {
            echo $site->PopularityRank . '<br />';
            echo $site->Title . '<br />';
            echo $site->Description . '<br />';
            echo $site->HasRecurringProducts . '<br />';
            echo $site->Commission . '<br />';
        }
    }
}

So should it be:

$xml = simplexml_load_file($file);
foreach ($xml->xpath('/Catalog/Category') as $top_cat) {
    echo $top_cat->Name . '<br />';
    echo $top_cat->Site . '<br />';
   foreach ($top_cat->xpath('Site') as $top_site) {
            echo $top_site->PopularityRank . '<br />';
            echo $top_site->Title . '<br />';
            echo $top_site->Description . '<br />';
            echo $top_site->HasRecurringProducts . '<br />';
            echo $top_site->Commission . '<br />';
        }
    foreach ($top_cat->xpath('Category') as $sub_cat) {
        echo $sub_cat->Name . '<br />';
        foreach ($sub_cat->xpath('Site') as $sub_site) {
            echo $sub_site->PopularityRank . '<br />';
            echo $sub_site->Title . '<br />';
            echo $sub_site->Description . '<br />';
            echo $sub_site->HasRecurringProducts . '<br />';
            echo $sub_site->Commission . '<br />';
        }
    }
}

Or have I gone too far ?

Installer

If it gets the data you want, I'd say you haven't gone too far. But, looking at the untested code I posted yesterday, I think I might have led you a bit off the path. This code is probably not ideal, but I think it will work much better:

$xml = simplexml_load_file($file);
$site_nodes = array('Id', 'PopularityRank', 'Title', 'Description', 
                    'HasRecurringProducts', 'Commission');

foreach ($xml->xpath('/Catalog/Category') as $top_cat) {
    echo $top_cat->Name . '<br />';
    foreach ($site_nodes as $node) {
        if (isset($top_cat->Site->$node)) {
            echo $top_cat->Site->$node . '<br />';
        }
    }
    foreach ($top_cat->Category as $sub_cat) {
        echo $sub_cat->Name . '<br />';
        foreach ($site_nodes as $node) {
            if (isset($sub_cat->Site->$node)) {
                echo $sub_cat->Site->$node . '<br />';
            }
        }
    }
}

habbardone

Thanks for your help,

I thought I had it it sorted but my little script managed to created over 500,000 records in the table !!

Rather than just echoing the data, what I am trying to do is
insert the data into a table, well two tables.

One table contains the product data, the other table contains which
categories the product is in. The product maybe in 3 or 4 categories or sub-categories.

I could not make it work with the site_nodes array, so I was trying to use
the more basic approach:

This is my code:

$xml = simplexml_load_file($file);

$cnt = 0;

foreach ($xml->xpath('/Catalog/Category') as $top_cat) {

   foreach ($top_cat->xpath('Site') as $top_site) {
	$sql_ck = "SELECT cb_id FROM cb_update WHERE id = '$top_site->Id' AND day_no = '$this_day'";
 	$result_ck = mysql_query($sql_ck)
		or die("could not FIND ID in cb_update.". mysql_error());  

$num = mysql_num_rows($result_ck);

if($num == 0) {                  // - so the product is not yet recorded.

         $title = mysql_real_escape_string($top_site->Title);
     $descrip = mysql_real_escape_string($top_site->Description);

      $sql_ins = "INSERT INTO cb_update ( cb_date, day_no, id, title, descrip, comm )
      VALUES 
		( '$today', '$this_day','$top_site->Id','$title', '$descrip', '$top_site->Commission' )";

      $result_ins = mysql_query($sql_ins) or die("could not execute INSERT to clicky.". mysql_error());  


// Also insert the category

	$sql_ins = "INSERT INTO cb_cat_update (id_cat, cat, sub_cat, pop, day_no_cat)
	VALUES ('$top_site->Id', '$top_cat->Name', ' ', '$top_site->PopularityRank', '$this_day')";	

	$result_ins = mysql_query($sql_ins) or die("could not execute INSERT to cb_cat_update.". mysql_error());		


	 }
 else {     //   So the product IS recorded, this must be another occurance in a different category - so we just record the extra category.

 $sql_ins = "INSERT INTO cb_cat_update (id_cat, cat, sub_cat, pop, day_no_cat)
VALUES ('$top_site->Id', '$top_cat->Name', ' ', '$top_site->PopularityRank', '$this_day')";	

$result_ins = mysql_query($sql_ins) or die("could not execute INSERT to cl_cat.". mysql_error());		 

 }
}

//  Now lets do the usb-categories

  foreach ($top_cat->xpath('Category') as $sub_cat) {

 foreach ($sub_cat->xpath('Site') as $sub_site) {

$sql_ck = "SELECT cb_id FROM cb_update WHERE id = '$sub_site->Id' AND day_no = '$this_day'";
$result_ck = mysql_query($sql_ck)
	or die("could not FIND ID in cb_update.". mysql_error());  

$num = mysql_num_rows($result_ck);

if($num == 0) {                  // - so the product is not yet recorded.

         $title = mysql_real_escape_string($sub_site->Title);
     $descrip = mysql_real_escape_string($sub_site->Description);

      $sql_ins = "INSERT INTO cb_update ( cb_date, day_no, id, title, descrip, comm )
      VALUES 
		( '$today', '$this_day','$sub_site->Id','$title', '$descrip', '$sub_site->Commission' )";

      $result_ins = mysql_query($sql_ins) or die("could not execute INSERT to clicky.". mysql_error());  


// Also insert the sub-category

	$sql_ins = "INSERT INTO cb_cat_update (id_cat, cat, sub_cat, pop, day_no_cat)
	VALUES ('$sub_site->Id', '$top_cat->Name', '$sub_cat->Name', '$sub_site->PopularityRank', '$this_day')";	

	$result_ins = mysql_query($sql_ins) or die("could not execute INSERT to cb_cat_update.". mysql_error());		

		 }
 else {    //   So the product IS recorded, this must be another occurance in a different sub-category - so we just record the extra category and sub-category.

 $sql_ins = "INSERT INTO cb_cat_update (id_cat, cat, sub_cat, pop, day_no_cat)
 VALUES ('$sub_site->Id', '$top_cat->Name', '$sub_cat->Name', '$sub_site->PopularityRank', '$this_day')";	

 $result_ins = mysql_query($sql_ins) or die("could not execute INSERT to cl_cat.". mysql_error());		 


	 }

 $cnt++;

     }
   }
 }

Well, maybe I have got the foreach loops mixed up, it seems logical to me,
but I can not get it working :o

Can anyone see what I have done wrong ?

Thanks

habbardone

Installer did a great job and helped me get to this point,
but I think Installers taking a break :eek:

So if any one can see what I have done wrong in the above
script I would really appreciate your help 🙂

habbardone

Hi,

I have tried a few changes to my
script, but I still can not see why it is
creating all these extra records 😕

When I take out the sub_cat section, it works ok
but of course it then does not record anything
except the records that are in the first category level.

Can someone who understands how to xml please help me
my little script working ...

I have been tinkering with it for days now and I just can
not get it working :o

thanks