Hi,
I am parsing XML file to MySQL. Each record has some unique elements and then some recurring which are inside other recurring elements and the data I need to get are in their attributes. Example of such XML can be found here: {link saved}
But basically I have records like this:
<Zajezd id="155496">
<Foto id="305615" src="http://i.ck.cz/f/3056/15.jpg" alt="typové foto"/>
<Foto id="305616" src="http://i.ck.cz/f/3056/16.jpg" alt="typové foto"/>
<Foto id="305617" src="http://i.ck.cz/f/3056/17.jpg" alt="pláž"/>
<Terminy>
<Termin id="4507448" datum="20091107" delka="8">
<Cena id="75580" typ="ZC" nazev="AP 1/2 - Dvoulůžkový apartmán" kod="AP 1/2" cena="3790"/>
<Cena id="75581" typ="ZC" nazev="AP 1/2 - Dvoulůžkový apartmán s přistýlkou" kod="AP 1/2+1" cena="3790"/>
</Termin>
<Termin id="4507449" datum="20091114" delka="8">
<Cena id="75580" typ="ZC" nazev="AP 1/2 - Dvoulůžkový apartmán" kod="AP 1/2" cena="3790"/>
<Cena id="75581" typ="ZC" nazev="AP 1/2 - Dvoulůžkový apartmán s přistýlkou" kod="AP 1/2+1" cena="3790"/>
<Cena id="75597" typ="ZC" nazev="AP 1/4 - Čtyřlůžkový apartmán" kod="AP 1/4" cena="2990"/>
</Termin>
</Terminy>
</Zajezd>And then I have 3 MySQL tables:
FOTO - consisting of corresponding ZAJEZD-ID, FOTO-SRC and FOTO-ALT
TERMINY - corresponding ZAJEZD-ID, TERMIN-ID, TERMIN-DATUM and TERMIN-DELKA
CENA - corresponding ZAJEZD-ID, corresponding TERMIN-ID, CENA-TYP, CENA-NAZEV, CENA-CENA
I have managed to create insert into FOTO like this:
foreach($record as $key => $value)
{
if (strpos($key,"FOTO")!==FALSE && (strpos($key,"ALT")!==FALSE or strpos($key,"SRC")!==FALSE))
{
if (strpos($key,"FOTO")!==FALSE && strpos($key,"SRC")!==FALSE)
{
$foto_url = $value;
}
if (strpos($key,"FOTO")!==FALSE && strpos($key,"ALT")!==FALSE)
{
$foto_alt = $value;
$sql = "INSERT INTO `foto` SET foto_zajezd_id='".mysql_real_escape_string($record["ZAJEZD-ID"])."', foto_url='".$foto_url."', foto_alt='".mysql_escape_string($foto_alt)."' ";
echo $sql;
mysql_query($sql);
?> <br> <?
}
}
}But I think I may have chosen the wrong approach, because I am stuck with this method once try to process CENA records inside TERMINY records... Because with each CENA element I also need the TERMIN-ID that it's inside and also the ZAJEZD-ID. Can you please give me a hint on how to do this? Do I need to call the myRecordHandler function several times as mentioned somewhere else on this forum?
Many thanks,
David
Thanks David!
At first I want to say your support is absolutely breathtaking! I really appreciate your work and your code helped me a lot!
As I am not really that familiar with arrays, so please allow me one stupid question:
You mentioned using count() like you do in your code
if (count($currentFOTO)==3 && ($currentFOTO["ID"] <> $lastFOTO["ID"]))In my XML the number of each CENA, TERMINY and FOTO varies per record. For example some have 5 photos, some records have none. However, even if I left the above line untouched, your code is parsing no matter how many records correctly. Can you please explain what am I missing? Because I expected it to insert only the first 3 photos of each record.
Many thanks,
David
Hi David,
It's not counting the number of FOTO or CENA etc. sections within the entire $record; that line of code is only concerned with the number of elements within (for example) the $currrentFOTO array itself, and serves to make sure that you don't try to insert that FOTO section of the record into your database until you have all the "fields", which in the case of FOTO are ID, SRC and ALT - i.e. 3 of them - that's where the 3 comes from, not the fact that there are 5 FOTO records altogether!
This means that the actual order that those elements appear in the XML does not matter, and it would not break your code if the XML changed slightly and ID happened to be the last attribute in the FOTO element instead of the first.
Hope this helps!
Cheers,
David.
Thanks for explaining David!
As I've said, your support is amazing! Keep up the great work!
Cheers,
David
Hi David,
There's a slightly different coding structure that I would recommend for this style of XML that enables you to keep track of the hierarchy of values needed when, for example, INSERTing each CENA with the higher level ZAJEZD-ID and TERMIN-ID.
The method uses arrays to hold the current value of each field as you traverse through the record; and then when you have the full compliment of fields - using count() - you can then construct the SQL and INSERT the record.
It's always easiest to explain by example; so have a go with something like this;
<?phpfunction myRecordHandler($record)
{
$lastFOTO = array();
$lastTERMINY = array();
$lastCENA = array();
foreach($record as $key => $value)
{
// FOTO
if (strpos($key,"FOTO")!==FALSE && (strpos($key,"ID")!==FALSE)
{
$currentFOTO = array();
$currentFOTO["ID"] = $value;
}
if (strpos($key,"FOTO")!==FALSE && (strpos($key,"SRC")!==FALSE)
$currentFOTO["SRC"] = $value;
if (strpos($key,"FOTO")!==FALSE && (strpos($key,"ALT")!==FALSE)
$currentFOTO["ALT"] = $value;
// TERMINY
if (strpos($key,"TERMINY")!==FALSE && (strpos($key,"ID")!==FALSE)
{
$currentTERMINY = array();
$currentTERMINY["ID"] = $value;
}
if (strpos($key,"TERMINY")!==FALSE && (strpos($key,"DATUM")!==FALSE)
$currentFOTO["DATUM"] = $value;
if (strpos($key,"TERMINY")!==FALSE && (strpos($key,"DELKA")!==FALSE)
$currentFOTO["DELKA"] = $value;
// CENA
if (strpos($key,"CENA")!==FALSE && (strpos($key,"ID")!==FALSE)
{
$currentCENA = array();
$currentCENA["ID"] = $value;
}
if (strpos($key,"CENA")!==FALSE && (strpos($key,"TYP")!==FALSE)
$currentFOTO["TYP"] = $value;
if (strpos($key,"CENA")!==FALSE && (strpos($key,"NAZEV")!==FALSE)
$currentFOTO["NAZEV"] = $value;
if (strpos($key,"CENA")!==FALSE && (strpos($key,"COD")!==FALSE)
$currentFOTO["COD"] = $value;
if (strpos($key,"CENA")!==FALSE && (strpos($key,"CENA")!==FALSE)
$currentFOTO["CENA"] = $value;
// DATABASE INSERTS
if (count($currentFOTO)==3 && ($currentFOTO["ID"] <> $lastFOTO["ID"]))
{
$sql = "INSERT INTO `foto` SET
foto_zajezd_id='".mysql_real_escape_string($record["ZAJEZD-ID"])."',
foto_url='".mysql_escape_string($currentFOTO["URL"])."',
foto_alt='".mysql_escape_string($currentFOTO["ALT"])."'";
echo $sql;
mysql_query($sql);
$lastFOTO["ID"] = $currentFOTO["ID"];
}
if (count($currentTERMINY)==3 && ($currentTERMINY["ID"] <> $lastTERMINY["ID"]))
{
$sql = "INSERT INTO `terminy` SET
terminy_zajezd_id='".mysql_real_escape_string($record["ZAJEZD-ID"])."',
terminy_datum='".mysql_escape_string($currentTERMINY["DATUM"])."',
terminy_delka='".mysql_escape_string($currentTERMINY["DELKA"])."'";
echo $sql;
mysql_query($sql);
$lastTERMINY["ID"] = $currentTERMINY["ID"];
}
if (count($currentCENA)==5 && ($currentCENA["ID"] <> $lastCENA["ID"]))
{
$sql = "INSERT INTO `cena` SET
cena_zajezd_id='".mysql_real_escape_string($record["ZAJEZD-ID"])."',
cena_termin_id='".mysql_escape_string($currentTERMINY["ID"])."',
cena_typ='".mysql_escape_string($currentCENA["TYP"])."',
cena_navez='".mysql_escape_string($currentCENA["NAVEZ"])."',
cena_cod='".mysql_escape_string($currentCENA["COD"])."',
cena_cena='".mysql_escape_string($currentCENA["CENA"])."'";
echo $sql;
mysql_query($sql);
$lastCENA["ID"] = $currentCENA["ID"];
}
}
}
?>
I may have got some of the datatbase field names incorrect in the `terminy` and `cena` tables so that may need correcting in order to match your database. Note how the construction of the SQL for each CENA refers to $currentTERMINY as well as it's own array....
Hope this helps!
Regards,
David.