Hi David, how are you? fine I hope ;-)
I've got a question:
Is it possible to aspire some xml data from a xml file and insert it into a mysql_base?
I'm explained:
I have a database which contains a product catalog. And for each product I've got a xml datasheet.
I'd like to aspire some data (ex: $record["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"];) and insert it into a specific table of my base?
For ex:
<?php
require("magicparser.php");
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("catalogmagic") or die(mysql_error());
function myProductRecordHandler($product)
{
$sql =
"INSERT INTO oxarticles (OXARTNUM ,OXLONGDESC) VALUES
'".mysql_real_escape_string($product["ID"])."',
'".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"])."'";
mysql_query($sql);
}
$url = "http://localhost/magic/450221.xml";
MagicParser_parse($url,"myProductRecordHandler","xml|INTERFACE/PRODUCT/PRODUCTDESCRIPTION/");
?>
But each record has to be inserted with the corresponding product...
Do you think is it possible?
Thanks
Umbro
Hi David,
When I said "But each record has to be inserted with the corresponding product..." I mean:
each product is assigned to a number, and this number is include inside the file.xml under ["PRODUCT/PRODUCTDESCRIPTION-ID"]
So I need to insert $record["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"]; into my mysql-db(catalogmagic dbase)inside
my oxarticles_TABLE --> OXLONGDESC_SQL
I hope my explanation is better this time ?? :-)
Cheers,
Umbro
Hi,
I'm so sorry Umbro but i'm still not understanding what you need to do.
Is oxarticles the only table in your database?
When you read the XML, do want to try and UPDATE an existing record if one already exists for that ARTNUM?
Hope I can help you more...
Cheers,
David
Hi David, thank you for the time you spend for me ;-)
First oxarticles isn't the only table in my database (there're 54 tables)
But in the oxarticles_table, there're a lot of fields and of which: OXLONGDESC and OXARTNUM.
And as you said in your reply, When I read the XML, I want to try and UPDATE an existing record if one already exists for that ARTNUM!
I hope my english was good, sorry... ;-)
Cheers
Umbro
Hi,
Yes - that makes sense!
The basic pattern for doing that is as follows (this is taken from your record handler function in the first message):
<?php
function myProductRecordHandler($product)
{
// see if oxartnum already exists
$sql = "SELECT FROM oxarticles WHERE OXARTNUM = '".mysql_real_escape_string($product["ID"])."'";
$result = mysql_query($sql);
if (mysql_num_rows($result))
{
// the record exists, so UPDATE
$sql = "UPDATE oxarticles SET OXLONGDESC='".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"])."' WHERE OXARTNUM='".mysql_real_escape_string($product["ID"])."'";
}
else
{
// the record DOES NOT exist, so INSER
$sql = "INSERT INTO oxarticles (OXARTNUM ,OXLONGDESC) VALUES '".mysql_real_escape_string($product["ID"])."' ,'".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"])."'";
}
mysql_query($sql);
}
?>
Hope this helps!
Cheers,
David.
Hi David,
I'm trying your code by reading my importxml.php, but I have a blank page and nothing is updated into my database :-(.
Have an idea??
Cheers
Umbro
Hi,
Can you post your whole code, and I will show you what debug code to add to it...!
Cheers,
David.
Hi,
Here's my code:
---------------
<?php
require("magicparser.php");
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("catalogmagic") or die(mysql_error());
function myProductRecordHandler($product)
{
// see if oxartnum already exists
$sql = "SELECT FROM oxarticles WHERE OXARTNUM = '".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-ID"])."'";
$result = mysql_query($sql);
if (mysql_num_rows($result))
{
// the record exists, so UPDATE
$sql = "UPDATE oxarticles SET OXLONGDESC='".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"])."' WHERE OXARTNUM='".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-ID"])."'";
}
else
{
// the record DOES NOT exist, so INSER
$sql = "INSERT INTO oxarticles (OXARTNUM ,OXLONGDESC) VALUES '".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-ID"])."' ,'".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"])."'";
}
mysql_query($sql);
}
$url = "http://localhost/magic/450221.xml";
MagicParser_parse($url,"myProductRecordHandler","xml|INTERFACE/");
?>
Hello Umbro,
The first thing you must do is add some code to print the record out, so you can check that myProductRecordHandler IS being called:
<?php
require("magicparser.php");
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("catalogmagic") or die(mysql_error());
function myProductRecordHandler($product)
{
print_r($record);
// see if oxartnum already exists
$sql = "SELECT FROM oxarticles WHERE OXARTNUM = '".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-ID"])."'";
$result = mysql_query($sql);
if (mysql_num_rows($result))
{
// the record exists, so UPDATE
$sql = "UPDATE oxarticles SET OXLONGDESC='".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"])."' WHERE OXARTNUM='".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-ID"])."'";
}
else
{
// the record DOES NOT exist, so INSER
$sql = "INSERT INTO oxarticles (OXARTNUM ,OXLONGDESC) VALUES '".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-ID"])."' ,'".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"])."'";
}
mysql_query($sql);
}
$url = "http://localhost/magic/450221.xml";
MagicParser_parse($url,"myProductRecordHandler","xml|INTERFACE/");
?>
If this script prints nothing, then it means that the parse is not happening, so check this line:
MagicParser_parse($url,"myProductRecordHandler","xml|INTERFACE/");(in particular the format string which you are using which is xml|INTERFACE/. This particular format string will only ever return 1 record - is that what you are expecting?)
Once you know that records are being returned, the next thing I do to debug is to print out the SQL, and then any MySQL error message, like this:
<?php
require("magicparser.php");
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("catalogmagic") or die(mysql_error());
function myProductRecordHandler($product)
{
// see if oxartnum already exists
$sql = "SELECT FROM oxarticles WHERE OXARTNUM = '".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-ID"])."'";
$result = mysql_query($sql);
if (mysql_num_rows($result))
{
// the record exists, so UPDATE
$sql = "UPDATE oxarticles SET OXLONGDESC='".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"])."' WHERE OXARTNUM='".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-ID"])."'";
}
else
{
// the record DOES NOT exist, so INSER
$sql = "INSERT INTO oxarticles (OXARTNUM ,OXLONGDESC) VALUES '".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-ID"])."' ,'".mysql_real_escape_string($product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"])."'";
}
mysql_query($sql);
print $sql;
print "<br />";
print "MySQL Error Message: ".mysql_error();
}
$url = "http://localhost/magic/450221.xml";
MagicParser_parse($url,"myProductRecordHandler","xml|INTERFACE/");
?>
If there is no error message, study the SQL that is printed out to see if there are any mistakes in it. If there is an error, it should explain what is wrong with the SQL code...
Cheers,
David.
Hi David,
I try it but the script prints nothing??
However, when I try this code below in another file.php, it prints right?
I don't understand why?
Have you an idea ;-)
<?php
require("MagicParser.php");
function myProductRecordHandler($product)
{
$product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"];
print "<p>".$product["PRODUCT/PRODUCTDESCRIPTION-ID"]."</p>";
print "<p>".$product["PRODUCT/PRODUCTDESCRIPTION-LONGDESC"]."</p>";
}
// first parse to extract the top level product information
$url="http://localhost/magic/450221.xml";
MagicParser_parse($url,"myProductRecordHandler","xml|INTERFACE/");
?>
Hi,
The only difference as far as I can see is the SQL code. Are you sure it is not existing the script at the top where you try to make the database connection?
Try commenting out parts of your new script (the database code) to see if it makes a difference.
The other way to go about it is to slowly add your database code to the script that works to see if that helps show where it stops....
Cheers,
David.
Hello Umbro,
This shouldn't be any problem - your code looks fine.
I am not sure what you mean with this line:
"But each record has to be inserted with the corresponding product..."
Can you explain a bit more...
Thank you,
David.