Why is my script writing 500,000 records ?

[jeddiki]

Hi,
I am trying to get an xml file into my mysql tables and I am
struggling with its structure.

This is how the xml looks

(Notice that it has two levels of category)

<?xml version="1.0" encoding="ISO-8859-1"?>
<Catalog>
  <Category>
     <Name>Business to Business</Name>
     <Site>
          <Id>PRODUCT01</Id>
          <Popularity>18</Popularity>
          <Title><![CDATA[A title here]]></Title>
          <Description><![CDATA[Some words here]]></Description>
          <Commission>75</Commission>
        </Site>
      <Category>
         <Name>Education</Name>
         <Site>
            <Id>PRODUCT02</Id>
            <Popularity>2</Popularity>
            <Title><![CDATA[A title here]]></Title>
            <Description><![CDATA[Some words here]]></Description>
            <Commission>75</Commission>
         </Site>
        </Category>
        <Category>
         <Name>Publishing</Name>
         <Site>
            <Id>PRODUCT03</Id>
            <Popularity>6</Popularity>
            <Title><![CDATA[A title here]]></Title>
            <Description><![CDATA[Some words here]]></Description>
            <Commission>75</Commission>
         </Site>
       </Category>
  </Category>
  <Category>
     <Name>Society &amp; Culture</Name>
     <Site>
etc, etc

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

I have two tables,
One table contains the product data, the other table contains which
categories the product is in. The reason the category table is that
the product maybe in 3 or 4 categories or sub-categories.

The script firts checks to see if the product already exists, if it
does then only the category table is updated with the category
data.

I have done two loops, one to work through the first level
categories and the second to handle the sub-categories. ıt all
looks logical to me, but it is not working :(

Could do with some help :)


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->Popularity', '$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->Popularity', '$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->Popularity', '$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->Popularity', '$this_day')";    
            
     $result_ins = mysql_query($sql_ins) or die("could not execute INSERT to cl_cat.". mysql_error());        

            
         }
            
     $cnt++;
          
         }
       }
     }

I am hoping that someone can see where I have gone wrong
with my script so that it created half a million records !!!

It should only create about 15,000 records.

Would appreciated some help as I feel I am nearly there but
I have got stuck !!

Thanks :)

[Markus]

At a glance, I would say it's because your XML looks wrong. You have multiple <Category> elements nested inside other <Category> elements. As far as I can remember, this is a no-no! But I'll leave Dormilich to confirm that.

[jeddiki]

Thanks Markus,

But this is the xml file given out by clickbank.

You can see the file for yourself here

(Although that is a zipped file, so needs unzipping)

I am sure hundreds of others are using it fine, but I have
the problem with these sub_cats. I don't know why they didn't label
them "<sub-cat>" !!!

Anyway I have to use what they give me !

[Dormilich]

At a glance, I would say it's because your XML looks wrong. You have multiple <Category> elements nested inside other <Category> elements. As far as I can remember, this is a no-no! But I'll leave Dormilich to confirm that.

from the point of XML that doesn’t matter. take XHTML as example, there are similar nestings possible and that even has a DTD.

[jeddiki]

Thanks for clearing that up :9

I took the sub_cat section out and although it
works, I am missing all the sub-categories.


If anyone is able to see what I am doing wrong
please let me know - I have been trying to get this right for days :(