XML Import to mysql via PHP

BLA BLA

<?php
#Database Connection: HOST USERNAME PASSWORD DATABASE
$conn = mysqli_connect("localhost", "DBUSERNAME", "DBPASSWORD", "DBNAME");

#Set affected rows to 0
$affectedRow = 0;

# Load XML Files
$file = glob('xmlfiles/*.xml');
foreach ($file as $input) {

// Strip Path from filename
$filename = preg_replace ( '/xmlfiles\//' , '' , $input );
// Add missing Seconds to DATETIME fields 
$seconds = ('00');

//SimpleXML For reading XML Files and output to SQL
$xml = simplexml_load_file("$input") or die("Error: Cannot create object");
foreach($xml->HUVUDUPPGIFTER as $HUVUDUPPGIFTER)
foreach ($xml->TRAVE as $TRAVE)	
foreach ($xml->ANT_TRAVE as $ANT_TRAVE)
{
    $Id = $filename;
	$T_METOD = $HUVUDUPPGIFTER->T_METOD;
	$RNR = $HUVUDUPPGIFTER->RNR;
	$VONUM = $HUVUDUPPGIFTER->VONUM;
	$LTERM = $HUVUDUPPGIFTER->LTERM;
	$INTNR2SLED1 = $HUVUDUPPGIFTER->INTNR2SLED1;
	$HKODS = $HUVUDUPPGIFTER->HKODS;
	$MOTTPL = $HUVUDUPPGIFTER->MOTTPL;
	$AVLPL = $HUVUDUPPGIFTER->AVLPL;
	$MDAT = $HUVUDUPPGIFTER->MDAT;
	$ANKOM_MPL = $HUVUDUPPGIFTER->ANKOM_MPL . $seconds;
	$AVGICK_MPL = $HUVUDUPPGIFTER->AVGICK_MPL. $seconds;
	$URSP = $HUVUDUPPGIFTER->URSP;
	$TMEDEL = $HUVUDUPPGIFTER->TMEDEL;
	$TFTAG = $HUVUDUPPGIFTER->TFTAG;
	$INTNR2TRP = $HUVUDUPPGIFTER->INTNR2TRP;
	$BILNR = $HUVUDUPPGIFTER->BILNR;
	$LASTID = $HUVUDUPPGIFTER->LASTID;
	$RNRPERLAST = $HUVUDUPPGIFTER->RNRPERLAST;
	$LKF = $HUVUDUPPGIFTER->LKF;
	$NAMNTRP = $HUVUDUPPGIFTER->NAMNTRP;
	$LNAMN = $HUVUDUPPGIFTER->LNAMN;
	$ANT_TRAVE = $ANT_TRAVE;
	$ISS = $TRAVE->ISS;
    $IT = $TRAVE->IT;
    $TVIKT_2 = $TRAVE->TVIKT_2;
    $TARA_2 = $TRAVE->TARA_2;
	$VEDVIKT_2 = $TRAVE->VEDVIKT_2;
	$FPROC1 = $TRAVE->FPROC1;
	$FPROC2 = $TRAVE->FPROC2;
	$FPROC3 = $TRAVE->FPROC3;
	$STYCK = $TRAVE->STYCK;

//SQL QUERRY
$sql = "INSERT INTO XMLIMPORT.UPPGIFTER (Id,T_METOD,RNR,VONUM,LTERM,INTNR2SLED1,HKODS,MOTTPL,AVLPL,MDAT,ANKOM_MPL,AVGICK_MPL,URSP,TMEDEL,TFTAG,INTNR2TRP,BILNR,LASTID,RNRPERLAST,LKF,NAMNTRP,LNAMN,ANT_TRAVE,ISS,IT,TVIKT_2,TARA_2,VEDVIKT_2,FPROC1,FPROC2,FPROC3,STYCK) VALUES ('" . $Id . "','" . $T_METOD . "','" . $RNR . "','" . $VONUM . "','" . $LTERM . "','" . $INTNR2SLED1 . "','" . $HKODS . "','" . $MOTTPL . "','" . $AVLPL . "','" . $MDAT . "','" . $ANKOM_MPL . "','" . $AVGICK_MPL . "','" . $URSP . "','" . $TMEDEL . "','" . $TFTAG . "','" . $INTNR2TRP . "','" . $BILNR . "','" . $LASTID . "','" . $RNRPERLAST . "','" . $LKF ."','" . $NAMNTRP . "','" . $LNAMN . "','" . $ANT_TRAVE . "','" . $ISS . "','" . $IT . "','" . $TVIKT_2 . "','" . $TARA_2 . "','" . $VEDVIKT_2 . "','" . $FPROC1 . "','" . $FPROC2 . "','" . $FPROC3 . "','" . $STYCK . "')";

// MYSQL Warning Logs 
// create logfile incase sometjing gives a warning.
$logfile = "logs/$filename.log";
$to = 'mail@domain';
$subject = "$logfile";
$from = 'mail@domain';
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: '.$from."\r\n".
	    'Reply-To: '.$from."\r\n" .
	        'X-Mailer: PHP/' . phpversion();
$result = mysqli_query($conn, $sql);
    
    if (! empty($result)) {
        $affectedRow ++;
    } else {
        $error_message = mysqli_error($conn) . "\n";
    } 
    if (mysqli_warning_count($conn)) {
   $warning = mysqli_get_warnings($conn);
   do {
	   echo file_put_contents($logfile, "Warning: $warning->errno: $warning->message\n", FILE_APPEND | LOCK_EX);
    } while ($warning->next());
	   $message = file_get_contents ("$logfile");
	mail($to, $subject, $message, $headers);
}
}
}
// How Many records was entered into database or why did it fail.
if ($affectedRow > 0) {
    $message = $affectedRow . " records inserted\n";
} else {
    $message = "No records inserted\n";
}
echo "\n";
echo $message;
if (! empty($error_message)) {
echo ($error_message);
} 
?>

Code for creating the Database

CREATE TABLE IF NOT EXISTS XMLIMPORT.UPPGIFTER (
        Id VARCHAR(255) NOT NULL,
        T_METOD VARCHAR(255) NULL,
        RNR INT NULL,
        VONUM INT NULL,
        LTERM VARCHAR(255) NULL,
        INTNR2SLED1 VARCHAR(255),
        HKODS VARCHAR(255),
        MOTTPL VARCHAR(255),
        AVLPL VARCHAR(255),
        MDAT DATE,
        ANKOM_MPL DATETIME,
        AVGICK_MPL DATETIME,
        URSP VARCHAR(255),
        TMEDEL VARCHAR(255),
        TFTAG INT NULL,
        INTNR2TRP INT NULL,
        BILNR VARCHAR(255) NULL,
        LASTID INT NULL,
        RNRPERLAST VARCHAR(255),
        LKF INT NULL,   
        NAMNTRP VARCHAR(255) NULL,
        LNAMN VARCHAR(255) NULL,
        ANT_TRAVE VARCHAR(255),
        ISS VARCHAR(255),
        IT VARCHAR(255),
        TVIKT_2 DECIMAL(6, 2),
        TARA_2 DECIMAL(6, 2),
        VEDVIKT_2 DECIMAL(6, 2),
        FPROC1 INT NULL,
        FPROC2 INT NULL,
        FPROC3 INT NULL,
        STYCK INT NULL,        
        PRIMARY KEY (Id)
        )
    ENGINE = INNODB
    CHARACTER SET utf8
    COLLATE utf8_general_ci
    COMMENT = 'XML IMPORT DATABASE';

Leave a comment

Your email address will not be published. Required fields are marked *