Hello, I just recently purchased this product and so far everything is going well. However, i've been stuck with this one part for quite a long time now and wondering if anyone can help shed some light. I have the following xml that has affiliate information and looks like the following:
<Catalog>
<Category>
<Name>Category1</Name>
<Category>
<Name>SubCategory1</Name>
<Site>
<Id>X</Id>
<Title>X</Title>
<Description>X</Description>
<EarnedPerSale>X</EarnedPerSale>
<Referred>X</Referred>
<Commission>X</Commission>
</Site>
<Site>
<Id>X</Id>
<Title>X</Title>
<Description>X</Description>
<EarnedPerSale>X</EarnedPerSale>
<Referred>X</Referred>
<Commission>X</Commission>
</Site>
</Category>
<Category>
<Name>SubCategory2</Name>
<Site>
<Id>X</Id>
<Title>X</Title>
<Description>X</Description>
<EarnedPerSale>X</EarnedPerSale>
<Referred>X</Referred>
<Commission>X</Commission>
</Site>
</Category>
</Category>
<Category>
<Name>Category2</Name>
<Category>
<Name>SubCategory1</Name>
<Site>
<Id>X</Id>
<Title>X</Title>
<Description>X</Description>
<EarnedPerSale>X</EarnedPerSale>
<Referred>X</Referred>
<Commission>X</Commission>
</Site>
<Site>
<Id>X</Id>
<Title>X</Title>
<Description>X</Description>
<EarnedPerSale>X</EarnedPerSale>
<Referred>X</Referred>
<Commission>X</Commission>
</Site>
</Category>
<Category>
<Name>SubCategory2</Name>
<Site>
<Id>X</Id>
<Title>X</Title>
<Description>X</Description>
<EarnedPerSale>X</EarnedPerSale>
<Referred>X</Referred>
<Commission>X</Commission>
</Site>
</Category>
</Category>
</Catalog>Now using the magicparse i was able to see the field names and whatnot. What i want to do is add the information into a database. Keep in mind that there are alot of affiliates so i'm assuming there would be mysql queries in the while loop and whatnot. The part that I'm having trouble with is how I would implement the correct Categories and Subcategories into the database according to info. I hope this isn't too confusing to comprehend but any help would be greatly appreciated.
Hey David,
Thanks for the quick reply. To answer your questions, yes every site is only stemming from Master Category > Sub Category > Site so for example, it'd be records like this:
Business to Business > Promotion > Affiliate Site Info
Business to Business > Money & Employment > Affiliate Site Info
and etc...
but there are other categories and subcategories as well like:
Health & Fitness > Fitness > Affiliate Site Info
Health & Fitness > Beauty > Affiliate Site Info
There are no other subcategories under the subcategories so the structure remains the same throughout. But for some reason, I'm having a very difficult time getting all the SITE info into a database ALONG with their corresponding Categories and Subcategories. Hope that clears up some stuff and hope to hear from you soon. Once again, thanks.
Hi Tommy,
Approximately how many site records are in this file altogether?
well there are approximately 8 Categories, with about 5 Subcategories in each and in those Subcategories, there are about 50 Affiliate Sites. So there's about 2000 site records in the file altogether i'd say. I'm just currently testing it out with 1 Category (about site 350 records) but i can't get it in the database with the category/subcategories.
i dunno if my other comment went through or not but it's not appearing on the forum. But to restate it, i'll have roughly around 2000 SITE records
Hi Tommy,
Sorry about the delay in your comments appearing - the forum is moderated i'm afraid so posts will sometimes not appear immediately if I am not online.
Before working a solution for you, have you considered how you wish to store this hierarchy along with the site records in your database?
As there are only 2 levels of hierarchy, and not a huge number of records, I would suggest a very simple flat structure, where the category and sub-category are stored directly within each site record. Therefore, your site records would have the fields (and my recommended data types):
id INT(11) AUTO-INCREMENT
cat VARCHAR(255)
subcat VARCHAR(255)
sid VARCHAR(255) (standing for site ID, to distinguish this field from the database ID)
title VARCHAR(255)
description TEXT
earnedpersale VARCHAR(255)
referred VARCHAR(255)
commission VARCHAR(255)
If some fields (e.g. earnedpersale) is always a standard decimal value, you could use DECIMAL(10,2) instead (the numbers in brackets are what you would put in the width field when designing the table using a tool such as phpMyAdmin).
What stage are you at with your database code so far? Have you (or can you) create a table, and write the code to insert a sample record?
Cheers,
David.
once again thanks for the quick replies. its great to see such good product support. But on to the code part, i have something like this so far:
$i = 0;
while(1) {
if ($i) $postfix = "@".$i;
if (!isset($record["CATEGORY/CATEGORY/SITE".$postfix])) break;
$category = $record["CATEGORY/NAME"];
$sub_category = $record["CATEGORY/CATEGORY/NAME".$postfix];
$id = $record["CATEGORY/CATEGORY/SITE/ID".$postfix];
$title = $record["CATEGORY/CATEGORY/SITE/TITLE".$postfix];
$description = $record["CATEGORY/CATEGORY/SITE/DESCRIPTION".$postfix];
$earned_per_sale = $record["CATEGORY/CATEGORY/SITE/EARNEDPERSALE".$postfix];
$referred = $record["CATEGORY/CATEGORY/SITE/REFERRED".$postfix];
$commission = $record["CATEGORY/CATEGORY/SITE/COMMISSION".$postfix];
echo "<h1>$i</h1><br><b>Key:</b> ".key($record)."<br>
<b>Category</b>: $category<br>
<b>Sub Category</b>: $sub_category<br>
<b>Id:</b> $id<br>
<b>$title</b><br>$description<p>
echo "<p>";
$i++;
}
}i just echoed it for now to see if the records are actually being displayed properly. All is good except i can't seem to get the values of the corresponding category and subcategory that each site record is under. This is the main part that i'm having troubles with because i can just easily replace that echo code with a mysql insert (would this take up alot of memory putting multiple insert queries in a giant loop? i'm just curious) so that's not a problem. I would just like to know how i can fetch these values so i can store them into the database accordingly. As for how my database looks, i actually had it setup exactly the way you suggested from the getgo so yea. once again, thanks alot.
Hi Tommy,
Great - if you're comfortable wit the DB code, i'll move on to how I would use Magic Parser to handle this file. As mentioned earlier, ignoring the categories you can extract all the site records using this Format String:
xml|CATALOG/CATEGORY/CATEGORY/SITE/So, my first parse would be to insert all sites into the database, or to just print them out you have the code:
<?php
require("MagicParser.php");
function myRecordHandler($record)
{
print "<p>ID: ".$record["ID"]."</p>";
print "<p>TITLE: ".$record["TITLE"]."</p>";
print "<p>DESCRIPTION: ".$record["DESCRIPTION"]."</p>";
print "<p>EARNEDPERSALE: ".$record["EARNEDPERSALE"]."</p>";
print "<p>REFERRED: ".$record["REFERRED"]."</p>";
print "<p>COMMISSION: ".$record["COMMISSION"]."</p>";
print "<hr />";
}
MagicParser_parse("yourfile.xml","myRecordHandler","xml|CATALOG/CATEGORY/CATEGORY/SITE/");
?>
Once you have this working, and can see all 2000(ish) records, I would move on to insert these into the database. Don't worry about category and sub-category for now, just leave those fields empty in the database - the next job is to set them.
So, moving on to the category and sub-category. Once the above is working and all the sites have been inserted into the database, the next block of code will parse the file again, but this time using the very top level format string. Within the record handler, we then simply scan through the entire record, making a note of the current category and sub-category. Whenever we get to an ID record, you can then update the site database with the category and sub-category for that site id. Here's the basic outline:
<?php
function myCategoryRecordHandler($record)
{
$currentCategory = "";
$currentSubCategory = "";
foreach($record as $k => $v)
{
if (strpos($k,"CATEGORY/NAME") === 0)
{
$currentCategory = $v;
}
if (strpos($k,"CATEGORY/CATEGORY/NAME") === 0)
{
$currentSubCategory = $v;
}
if (strpos($k,"CATEGORY/CATEGORY/SITE/ID") === 0)
{
$siteID = $v;
// now UPDATE the database and set category=$currentCategory
// and subcategory=$currentSubCategory where sid = $sid (sid is the site id)
}
}
}
MagicParser_parse("yourfile.xml","myCategoryRecordHandler","xml|CATALOG/");
?>
This would make your entire script look something like this:
<?php
require("MagicParser.php");
$filename = "yourfile.xml");
// Database: DELETE FROM sites
function myRecordHandler($record)
{
print "<p>ID: ".$record["ID"]."</p>";
print "<p>TITLE: ".$record["TITLE"]."</p>";
print "<p>DESCRIPTION: ".$record["DESCRIPTION"]."</p>";
print "<p>EARNEDPERSALE: ".$record["EARNEDPERSALE"]."</p>";
print "<p>REFERRED: ".$record["REFERRED"]."</p>";
print "<p>COMMISSION: ".$record["COMMISSION"]."</p>";
print "<hr />";
// DATABASE: INSERT INTO sites
}
MagicParser_parse($filename,"myRecordHandler","xml|CATALOG/CATEGORY/CATEGORY/SITE/");
function myCategoryRecordHandler($record)
{
$currentCategory = "";
$currentSubCategory = "";
foreach($record as $k => $v)
{
if (strpos($k,"CATEGORY/NAME") === 0)
{
$currentCategory = $v;
}
if (strpos($k,"CATEGORY/CATEGORY/NAME") === 0)
{
$currentSubCategory = $v;
}
if (strpos($k,"CATEGORY/CATEGORY/SITE/ID") === 0)
{
$siteID = $v;
// DATABASE: UPDATE sites SET category=$currentCategory,subcategory=$currentSubcategory WHERE sid=$siteID
}
}
}
MagicParser_parse($filename,"myCategoryRecordHandler","xml|CATALOG/");
?>
Hope this helps!
Cheers,
David.
hey David,
thanks for your help. It seems to be working pretty nicely! Was going insane trying to figure this out. Once again, thanks
Hello Tommy,
There are various ways to do this, although please note that Magic Parser is not really designed to handled nested structures like this - it is intended for "flat" files containing many records at the same level.
What I need to know, is every site Master Category > Sub Category > Site, or can there be more sub-categories?
The script can easily parse out each site using the format string:
xml|CATALOG/CATEGORY/CATEGORY/SITE/...as can be seen in this demo page (you can navigate through the 6 sites).
Extracting the category hierarchy is more complex, so I need to know if this is fixed...?
Cheers,
David.