Good morning,
I am trying to extract data from an xml file to INSERT in my Mysql DB, I managed to define the variables (well I think)and inserted the code. My problem is that only the first record is written in the DB and my file contains actually 2 records (and more to come). Can you please tell me how to loop, in order to feed all records in DB, (please find full source of my file) :
<?php
session_start();
include('include/parseRessource.php');
include('include/datacon.php');
include('include/config.php');
include('admin/include/functions.php');
require("MagicParser.php");
function myRecordHandler($record)
{
// This is where you write your code to process each record, such as loading a database
// Alimentation BD
$prenom = mysql_real_escape_string($record["ORDER/UTILISATEUR/PRENOM"]);
$nom = mysql_real_escape_string($record["ORDER/UTILISATEUR/NOM"]);
$societe = mysql_real_escape_string($record["ORDER/UTILISATEUR/SOCIETE"]);
$query = "INSERT INTO `t04_NL` ( `prenom` ,`nom`,`societe` )
VALUES ('".$prenom."',
'".$nom."',
'".$societe."')
"; // ^^ don't forget the last one doesn't have a comma after ^^
$result = MYSQL_QUERY($query);
print $query;
// You can display the record contents using PHP's internal print_r() function:
// print_r($record);
// The following code will print out each field in your sample data:
// {code saved}
}
MagicParser_parse("{link saved}","myRecordHandler","xml|ORDERS/");
?>
Thank you for your help...
Best regrads,
Michel
PS : My DB is running on PHP4
Amazing David,
How fast your response and it actually works (not that I doubted)...
Is there a way that the screen display will be like your example (clean, each var is on different line with a turn page by ORDER), the one that I have is all var and records are on one line (a mess),
second question if you mind, how do I encode UTF-8 in order to display (i.e Argômédias I/O Argômédias)…
All the best and thank you for your help David.
Michel
PS : Sorry for my English for I am French ;-)
Hi Jason,
Firstly, to make sure you are outputting UTF-8, add this at the top of your script:
header("Content-Type: text/html;charset=utf-8");And to display all the fields in table like in the demo, use this code within your record handler function:
print "<table>";
foreach($record as $key => $value)
{
print "<tr>";
print "<th>".$key."</th>";
print "<td>".$value."</td>";
print "</tr>";
}
print "</table>";Hope this helps!
Cheers,
David.
David, the header worked on the display but my DB is stil feeding special caracteres (i.e. Argômédias, any suggestions please ?
On the other hand I will try the table later on, it will take a while ...
Best,
Jason
Hi Jason,
Make sure that your database table collation is "utf8_general_ci". If you're using phpMyAdmin you can check / change this from the table / field properties pages...
Cheers,
David.
It is possible to do it in version 5 but I couldn't find it in the structure of version 4...
Any way thanks David
No problem, Jason. Let me know if you need any more help.
Cheers,
David.
Hi David,
I need your help once again, I need to put a control (a condition if...)
on the xml file i'm reading, the records already INERTed in the DB need
not to be Inserted again, the controm var is "$numcommande_rdc".
My question is WHERE and HOW do I put the control to check if the record
is already in the DB before it INSERT it ? Please find below the whole code :
<?php
header("Content-Type: text/html;charset=utf-8");
session_start();
include('include/parseRessource.php');
include('include/datacon.php');
include('include/config.php');
include('admin/include/functions.php');
require("MagicParser.php");
function myRecordHandler($record)
{
// This is where you write your code to process each record, such as loading a database
// Alimentation BD
$datepaiement = mysql_real_escape_string($record["INFOCOMMANDE/DATE"]);
$date = $datepaiement;
$civilite = mysql_real_escape_string($record["UTILISATEUR/NOM-TITRE"]);
$nom = mysql_real_escape_string($record["UTILISATEUR/NOM"]);
$prenom = mysql_real_escape_string($record["UTILISATEUR/PRENOM"]);
$adresse1 = mysql_real_escape_string($record["UTILISATEUR/ADRESSE/RUE1"]);
$adresse2 = mysql_real_escape_string($record["UTILISATEUR/ADRESSE/RUE2"]);
$codepostal = mysql_real_escape_string($record["UTILISATEUR/ADRESSE/CPOSTAL"]);
$ville = mysql_real_escape_string($record["UTILISATEUR/ADRESSE/VILLE"]);
$telephone = mysql_real_escape_string($record["UTILISATEUR/TELHOME"]);
$email = mysql_real_escape_string($record["UTILISATEUR/EMAIL"]);
$civilite2 = mysql_real_escape_string($record["UTILISATEUR/NOM@1-TITRE"]);
$nom2 = mysql_real_escape_string($record["UTILISATEUR/NOM@1"]);
$prenom2 = mysql_real_escape_string($record["UTILISATEUR/PRENOM@1"]);
$adresse12 = mysql_real_escape_string($record["UTILISATEUR/ADRESSE/RUE1@1"]);
$adresse22 = mysql_real_escape_string($record["UTILISATEUR/ADRESSE/RUE2@1"]);
$codepostal2 = mysql_real_escape_string($record["UTILISATEUR/ADRESSE/CPOSTAL@1"]);
$ville2 = mysql_real_escape_string($record["UTILISATEUR/ADRESSE/VILLE@1"]);
$telephone2 = mysql_real_escape_string($record["UTILISATEUR/TELHOME@1"]);
$email2 = mysql_real_escape_string($record["UTILISATEUR/EMAIL@1"]);
$numcommande = mysql_real_escape_string($record["INFOCOMMANDE/REFID"]);
$total = mysql_real_escape_string($record["INFOCOMMANDE/MONTANT"]);
$fdp = mysql_real_escape_string($record["INFOCOMMANDE/TRANSPORT/MONTANT"]);
$validation = "N";
$moyenPaiement = "RDC";
$numcommande_rdc = $numcommande;
$query = "INSERT INTO `t03_commande_RDC` ( `t03_datePaiement_d` ,`T03_date_d`,`t03_civilite_va`,`t03_nom_va`,`t03_prenom_va`,`t03_adresse1_va`,`t03_adresse2_va`,`t03_codepostal_va`,`t03_ville_va`,`t03_telephone_va`,`t03_email_va`,`t03_civilite2_va`,`t03_nom2_va`,`t03_prenom2_va`,`t03_adresse12_va`,`t03_adresse22_va`,`t03_codepostal2_va`,`t03_ville2_va`,`t03_telephone2_va`,`t03_email2_va`,`t03_numcommande_va`,`t03_total_n`,`t03_fdp_n`,`t03_validationCommande_b`,`t03_moyenPaiement_va`,`t03_numcommande_rdc_va` )
VALUES ('".$datepaiement."','".$date."','".$civilite."','".$nom."','".$prenom."','".$adresse1."','".$adresse2."','".$codepostal."','".$ville."','".$telephone."','".$email."','".$civilite2."','".$nom2."','".$prenom2."','".$adresse12."','".$adresse22."','".$codepostal2."','".$ville2."','".$telephone2."','".$email2."','".$numcommande."','".$total."','".$fdp."','".$validation."','".$moyenPaiement."','".$numcommande_rdc."')
"; // ^^ don't forget the last one doesn't have a comma after ^^
$result = MYSQL_QUERY($query);
echo $query;
}
MagicParser_parse("{link saved}","myRecordHandler","xml|ORDERS/ORDER/");
?>
Once again thank you for your help David ...
Jason
Hello Jason,
By far the easiest way to do this is to put a UNIQUE index on the
t03_numcommande_rdc_va field on your table. That way, the query
will simply quietly fail if you try to INSERT a duplicate record.
If you're using MySQL, browse to the table, check the box alongside
the field you want to index, and then click the "U" (Unique) tool
just below the table.
Alternatively, you would need to perform a SELECT query beforehand
and return from the function if a row is returned. For example:
$query = "SELECT * FROM `t03_commande_RDC` WHERE t03_numcommande_rdc_va='".mysql_real_escape_string($record["INFOCOMMANDE/REFID"])."'";
$result = MYSQL_QUERY($query);
if (mysql_numrows($result)) return;Hope this helps!
Cheers,
David.
Hello Michel,
It looks to be just down to the Format String being used. Instead of:
xml|ORDERS/...if you use:
xml|ORDERS/ORDER/...then myRecordHandler will be called twice - once for each ORDER record.
To make this part of the change, simply replace your call to MagicParser_parse() with::
MagicParser_parse("{link saved}","myRecordHandler","xml|ORDERS/");(replace {link saved} with your URL of course)
This however will mean that the elements of the $record array won't have ORDER/ at the front, so you will need to change that section of your code as follows:
$prenom = mysql_real_escape_string($record["UTILISATEUR/PRENOM"]);$nom = mysql_real_escape_string($record["UTILISATEUR/NOM"]);
$societe = mysql_real_escape_string($record["UTILISATEUR/SOCIETE"]);
Hope this helps!
Cheers,
David.