My php script is not picking up the xml info.

[jeddiki]

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.