David,
Thanks for all the wonderful help last week, I hope you had a great weekend. I worked with MagicParser over the weekend and made some good head way in reaching my goal, but have hit a road block with a couple items and instead of e-mailing I wanted to post to the forum so others who learn by example like myself can benifit.
Search Relpace:
I need to replace multiple strings in a single field from my XML. i.e. alpha becomes 1, beta become 2, gamma becomes 3 each of the search strings show up in the entire array, but I only want to change them in the field $record["MFGNAME"], also some search strings could contain 2 words like delta zeta becomes 10
This works great for the array can I narrow it to the field? or is there a different approach
$search = array("alpha","\n");
foreach($record as $k => $v)
{
$record[$k] = str_replace($search,"1",$v);
}continuing on from above I want to update mysql db from the xml and I see verious examples of this, but I must be missing something that is hanging me up.
<?php
$xml = "myxmlfile.xml";
$link = mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db("dbtable",$link) or die(mysql_error());
require("MagicParser.php");
function myRecordHandler($record)
{
// Insert search replace string here from above
global $link;
$sql = "SELECT id FROM items WHERE name='".mysql_escape_string($record["SKURCRD"])."'";
if (mysql_num_rows(mysql_query($sql))
{
$sql = "UPDATE items SET
two='".mysql_escape_string($record["MFGNAME"])."',
three='".mysql_escape_string($record["MFGNO"])."',
// continue list
fourtythree='".mysql_escape_string($record["CATEGORYID"])."'
WHERE
one='".mysql_escape_string($record["SKURCRD"])."'
";
}
else
{
$sql = "INSERT INTO items SET
one='".mysql_escape_string($record["SKURCRD"])."',
two='".mysql_escape_string($record["MFGNAME"])."',
// continue list
fourtythree='".mysql_escape_string($record["CATEGORYID"])."'
";
}
mysql_query($sql);
print $sql;
print "<br />";
print "MySQL Error Message: ".mysql_error();
}
MagicParser_parse($xml,"myRecordHandler","xml|items/vwGSProdsXML/");
?>
Thank you in advance,
Craig
David,
All your help has been great. I will give this a try later today.
Thanks,
Craig
Hello David,
Search and replace works just great. Thank you. Now I am struggling with updating. The comment about checking a different field was right on and cut and paste works if you understand what to change and obviously I am missing something. I think I am missing identifying the table, but the combinations I try are just not getting it.
My db_table name is translate_GS_to_SS
XML recourds are SKURCRD, MFGNAME, MFGNO, CATEGORYID
table item header find is sku
other table names are MfgName, MfgNo, catid
<?php
$xml = "myxmlfile.xml";
$link = mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db("dbnamee",$link) or die(mysql_error());
require("MagicParser.php");
function myRecordHandler($record)
{
global $link;
$sql = "SELECT sku FROM translate_GS_to_SS WHERE
sku = '".mysql_escape_string($record["SKURCRD"])."'";
if (mysql_num_rows(mysql_query($sql))
{
$sql = "UPDATE translate_GS_to_SS SET
MfgName = '".mysql_escape_string($record["MFGNAME"])."',
MfgNo = '".mysql_escape_string($record["MFGNO"])."',
// continue list
catid = '".mysql_escape_string($record["CATEGORYID"])."'
WHERE
sku = '".mysql_escape_string($record["SKURCRD"])."'
";
}
else
{
$sql = "INSERT INTO translate_GS_to_SS SET
sku = '".mysql_escape_string($record["SKURCRD"])."'
MfgName = '".mysql_escape_string($record["MFGNAME"])."',
// continue list
catid = '".mysql_escape_string($record["CATEGORYID"])."'
";
}
mysql_query($sql);
print $sql;
print "<br />";
print "MySQL Error Message: ".mysql_error();
}
MagicParser_parse($xml,"myRecordHandler","xml|items/vwGSProdsXML/");
?>
Thanks again for your assistance
Hello Craig,
On the face of it, your code looks fine - I can't see any logical errors in the above at all. It shouldn't be necessary, but may be worth adding the $link parameter to the calls to mysql_query, e.g.
mysql_query($sql,$link)Otherwise, I notice that you have debug code in place - how does the output compare to what you are expecting?
For a value of sku that is already in the database, are you seeing an "UPDATE" query, and if so, do the error messaged being displayed point to the problem at all?
Cheers,
David.
Hello David,
I updated my code to this
$sql = "SELECT sku FROM translate_GS_to_SS WHERE sku = '".mysql_escape_string($record["SKURCRD"])."'";
if (mysql_num_rows(mysql_query($sql,$link))
{
$sql = "UPDATE translate_GS_to_SS SET }
mysql_query($sql,$link);
print $sql;
print "<br />";
print "MySQL Error Message: ".mysql_error();
}
MagicParser_parse($xml,"myRecordHandler","xml|items/vwGSProdsXML/");
?>I do not get an error printed on the screen, it was blank before and now, but now when I go into phpMYAdmin I get the following error when I select the table
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
I could insert this same xml file directly into the db when it starts empty or by empting it before I start an upload with out issues. Now it seems that both convert directly to db or update db is doing the same thing. So I may have bigger issues.
Hello Craig,
If phpMyAdmin is generating an error when you simply try to browse to the table, that would indicate that perhaps the table is corrupted.
If it is feasible, could you try DROPing and then re-creating the table from scratch, and then see how your script performs after that...
Cheers,
David.
I also started a new table and the error went away. Which is a good thing, I though I was sliding backwards
I can now empty the table again and add info from the xml, but getting nothing when I try to update the info. Blank screen after running the php file. I am going to e-mail you the working convert-to-db, sql insert, and full-update.
I am pulling my hair out.
Hi Craig,
Can you clarify on "getting nothing" when trying a second run that should update the table; are you
i) seeing no change - in which case is there definitely a change in the XML data over the period
ii) is the table being changed in some way that is not consistent with what you are expecting..
Thanks,
David.
Hello Craig,
Regarding search and replace - sure that can be applied to a single field; there's no need for the foreach loop, simply apply the str_replace() call to the field directly; for example:
$search = array("alpha","beta","gamma","delta zeta");$replace = array("1","2","3","10");
$record["MFGNAME"] = str_replace($search,$replace,$record["MFGNAME"]);
Regarding your code; I noticed one possible error; in the UPDATE $sql, whilst you are checking whether a record exists using name=$record["SKURCRD"], in the UPDATE query you are WHERE one=$record["SKURCRD"] (a different field in the WHERE clause)...
Hope this helps!