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 & 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 :)
4 1577
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.
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 !
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.
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 :(
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Damien Renwick |
last post by:
I have a php script which simply stops midway through a while loop
that processes records returned by a MySQL query. The HTML page
continues trying to load the page but the php has stopped running...
|
by: Steve Holden |
last post by:
For quite a while now I've been filling part of the navigation-bar on my
home page with Python-related news extracted from O'Reilly's meerkat
service.
I've been experiencing intermittent...
|
by: Nathan Sokalski |
last post by:
I am writing an SQL script for Oracle 9i. I have a table of which one field
is of type BLOB. I would like to create an SQL script to add records to this
table. What do I do to take a file and use...
|
by: MC |
last post by:
I am writing a script to retrieve data records of MarinLif table. The
problem is that i have a column MarinLif_Picture of type image so the
insert is wrong in this way. How do i correct it?
...
|
by: Dr. Know |
last post by:
I am working on an ASP page that writes to several databases, ranging
from MDBs to x-base.
One of the tasks involves using an existing highest value from the DB
and incrementing it before...
|
by: Alvaro G Vicario |
last post by:
I have a list built on HTML and CSS:
<ul>
<li>Foo</li>
<li>Bar
<ul>
<li>Gee</li>
</ul>
</li>
</ul>
|
by: Jeff Pritchard |
last post by:
I have an MDB that contains a single table. Checkweigher data is being
continuously written to this MDB from a dedicated workstation over a network
into the MDB on the server at the rate of about...
|
by: flit |
last post by:
Hello All,
I am trying to get information from a form and send it to a python
script without success..
Here is my objective:
User enters data in form --form send variables to python script...
|
by: beporter |
last post by:
I've been searching the internet for some kind of script that will utilize dig or similar to dump ALL A, CNAME, MX, TXT, etc. records for a given domain. Is there any way to retrieve all of the...
|
by: sandipon |
last post by:
I have used the following Pagination script to display database records in a series of pages, but only the first page shows up properly, and on clicking the links of subsequent pages or the NEXT ,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| | |