import supplier xml through php to mysql

Question

yaginuk 0 Newbie Poster

9 Years Ago

Hi,
i have had made in the past a php file to download a supplier list in xml format, import it to a temp database with a datestamp
then the file will check to see if product exists or not, has been removed from the file since last time or not and if the price has changed.
that supplier went bust, i am trying to tweak the file to produce the same results from a different supplier feed.
i have it logging in and downloading a xml file to local xml file.
when it starts processing the file i get an error message:-

.Error database when insert targetproduct : INSERT INTO `tl_target_importproducts` (`table_id`, `target_id`, `zen_product_id`, `creation_date` , `target_product_name` , `product_description`, `product_image`, `product_price`, `product_status`, `product_lastmodification_number`, 'target_description', `target_extendeddescription`)VALUES (NULL, NPNET-WNAP210200, -1, NULL, '0', '

i have ran mysql and executed 'LOAD XML LOCAL INFILE' to take local file in to a test database which i have already added.
and it works.
here is a snippet of my xml datafile excluding data, the only spaces are at the top after <items>

<?xml version="1.0" encoding="UTF-8"?>
<items>

<item>
<stockcode></stockcode>
<description></description>
<extendeddescription></extendeddescription>
<imageurl></imageurl>
<thumbnailurl></thumbnailurl>
<largeimageurl></largeimageurl>
<manufacturer></manufacturer>
<manupartcode></manupartcode>
<weight></weight>
<stock></stock>
<price></price>
<price5off></price5off>
<price20off></price20off>
<category></category>

</item>

mysql php xml

2 Contributors
7 Replies
238 Views
1 Day Discussion Span
Latest Post 9 Years Ago Latest Post by diafol

diafol

9 Years Ago

any chaance of seeing the php? or are we supposed to guess?

diafol

9 Years Ago

Any relevant code, whether that's one or many files. Please don't include any css, and unrelated html and guff like that.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

yaginuk 0 Newbie Poster · Answer 1 · 2015-04-15T16:01:02+00:00

lol. sorry.
do you wantr to see the whole file or a snippet?

yaginuk 0 Newbie Poster · Answer 2 · 2015-04-16T13:36:40+00:00

ok, i have removed passwords and unused lines.

<?php               
 require('includes/application_top.php');
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<!--
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET;
?>
">-->
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
<title>
<?php echo TITLE; ?>
</title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript">
<!--  function init()  {
    cssjsmenu('navbar');
    if (document.getElementById)
    {
      var kill = document.getElementById('hoverJS');
      kill.disabled = true;             
    }
  }
  // -->
</script>             
</head>
<body onLoad="init ()">
<div id="spiffycalendar" class="text"></div>
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!--BOF added by tlwebmaster for taking 2 xml feeds from target and combining to 1 file-->
<?php
// re write of target product import - login to ftp site download file and then import products
echo    "Now for setting local/remote filename";?><br><?php
echo    $folder_path = "/tlsystems/AdminFolder/";?><br><?php
echo    $local_file = "targetxmlexport.xml";?><br><?php
echo    $server_file = "tagetpricelist.xml.xml";?><br><?php
echo    "Success";
?><br><br>
<?php echo "login to target ftp server"?><br><?php

//connection settings
echo    $ftp_server = "ftp.*******";?><br><?php
echo    $ftp_user_name = "*****"; // login name?><br><?php
echo    $ftp_user_pass = "*****"; //login password?><br><?php
echo    "Success";?><br><?php
//set up basic connection
$conn_id = ftp_connect($ftp_server);

//login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

//try to download $server_file and save to $local_file
if (ftp_get($conn_id, $local_file, $server_file, FTP_BINARY)) {
        echo "Successfully written to $local_file\n";
} else {
        echo "There was a problem\n";
}

//close the connection
ftp_close($conn_id);
?>

<!-- EOF end of combine xml files -->

<div style="margin: 30px 0px 15px 0px; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 20px; font-weight: bold;">
Import TARGET Components File
</div>
<div style="text-align: center;">
<form action="" method="GET">
<input type="submit" name="Import" value="Import" />
</form>
</div>

<?php

//Check if the TARGET product already exists

function checkTARGETProductExist($target_idprod){

$request = "Select target_id from tl_target_importproducts where target_id = ".$target_idprod;
$productExist = false;

 if($result = mysqli_query($request)){
   if($rowArray = mysqli_fetch_array($result)){
     $productExist = true;
   }
   mysqli_free_result($result);
 }
return $productExist;
}

//Get the target product identified by target_idprod
function getTARGETProduct($target_idprod, &$oldprice, &$oldstatus){

$isok = false;
$request = "SELECT `product_price`, `product_status` FROM `tl_target_importproducts` where target_id = ".$target_idprod;
if($result = mysqli_query($request)){
   if($rowArray = mysqli_fetch_array($result)){
     $oldprice = $rowArray["product_price"];
     $oldstatus = $rowArray["product_status"];
     $isok = true;
   }
   mysqli_free_result($result);
 }
return $isok;
}

//Update the target product identified by target_idprod
function updateTARGETProduct($updatestatement, $target_idprod){

  $request = "UPDATE `tl_target_importproducts` SET ".$updatestatement." WHERE target_id = ".$target_idprod;
  mysqli_query ($request) or die('Error database modify target product');
}

if (isset($_GET['Import'])){

    @set_time_limit(240);

    $xmlfilename = '/var/www/html/tlsystems/AdminFolder/targetxmlexport.xml';
    $nbnewprod = 0;
    $nbpricechange = 0;


    echo "Downloading file....";
    if ($stream = fopen($xmlfilename, "rb")) {
    echo "Ok!<br/>";

      //file dowloaded successfully
      $contents = stream_get_contents($stream);
      fclose($stream);

      if(strlen($contents) > 0){
        $contents = str_ireplace("<br></br>","<br />", $contents);
        $contents = str_ireplace(chr(149),"<br />&#149; ", $contents);

        //other special chars

        for ($i=128; $i<256; $i++) {
          $tmpstr = "&#".$i.";";
        $contents = str_ireplace(chr($i), $tmpstr, $contents);
        }

     // echo $contents;

        if(simplexml_load_string($contents) || isset($_GET['action'])){

          $unvalidformat = false;
          if(!simplexml_load_string($contents)){
            //TARGET XML File unvalid format
            $unvalidformat = true;
            echo "<div style='margin: 10px 0px; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 13px; font-weight: bold; color: red;'>\n <br /> TARGET XML File Format is not valid!!! \n </div>";
          }


    $contents = str_ireplace("<description>", "<description><![CDATA[", $contents);
    $contents = str_ireplace("</description>", "]]></description>", $contents);
        for ($i=0; $i<16; $i++) {
           }
           if($simplexmlobject = simplexml_load_string($contents)){

            //TARGET XML file modified valid format
            if($unvalidformat){
              echo "<div style='margin: 10px 0px; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 13px; font-weight: bold; color: blue;'>\n <br /> TARGET XML File has been modified. XML format of the new file is now valid. \n </div>";
            }


            //connexion to the database
            mysqli_connect(DB_SERVER,DB_SERVER_USERNAME,DB_SERVER_PASSWORD) or die("Connexion failure");
            mysqli_select_db(DB_DATABASE) or die("Connexion failure to the database");

            mysqli_query('SET NAMES utf8');

            //Insert a new row in tl_target_lastmodification in order to record
            //the time of this new import and to detect products which need to be deleted.
            $request = "INSERT INTO `tl_target_lastmodification` (`table_id`, `lastmodification_time`) VALUES (NULL, CURRENT_TIMESTAMP);";
            mysqli_query ($request) or die('Error database insert in table called tl_target_lastmodification');
            $lastmodifnumber = mysql_insert_id();

    echo "Processing: "?><br><?php ;
            //Processing the file
            foreach ($simplexmlobject->item as $currentproduct) {
        echo ".";
              if(checkTARGETProductExist($targetprodid)){

                $oldprice = 0;
                $newprice = trim($currentproduct->price);
                $oldstatus = "";

                //get the TARGET product from the database
                if(getTARGETProduct($targetprodid, $oldprice, $oldstatus)){
                  if($oldstatus != "ignore"){
                    if(settype($oldprice, "float") || settype($newprice, "float")){

                      //if the price changed
                      if($oldprice != $newprice){
                        $newstatus = "price";
                        if($oldstatus == "new"){
                          $newstatus = "new";
                        }
                        $updatestatement = "`product_status` = '".$newstatus."', `product_price` = ".$newprice.", `product_lastmodification_number` = ".$lastmodifnumber;
                        updateTARGETProduct($updatestatement, $targetprodid);
                        $nbpricechange++;
                      }
                      //if the price is still the same: update product last modified number
                      else {
                        $updatestatement = "`product_lastmodification_number` = ".$lastmodifnumber;
                        updateTARGETProduct($updatestatement, $targetprodid);
                      }
                    }
                  }
                  //if the product has been ignored: update product last modified number
                  else {
                    $updatestatement = "`product_lastmodification_number` = ".$lastmodifnumber;
                    updateTARGETProduct($updatestatement, $targetprodid);
                  }
                }
              }
              else {
        //echo trim($targetprodid)."<br/>";
                //create a new TARGETproduct

                $request = "INSERT INTO `tl_target_importproducts` (`table_id`,  `target_id`, `zen_product_id`, `creation_date` , `target_product_name` , `product_description`,  `product_image`, `product_price`, `product_status`, `product_lastmodification_number`, 'target_description', `target_extendeddescription`)";

                $request .= "VALUES (NULL, ".trim($currentproduct->stockcode.", -1, NULL, '".  0, 64)."', '".mysql_real_escape_string(trim($currentproduct->extendeddescription))."', '".mysql_real_escape_string(trim($currentproduct->imageurl))."', '".trim($currentproduct->price)."', 'new', $lastmodifnumber, '".mysql_real_escape_string(trim($currentproduct->extendeddescription))."')";

                //echo $request;
        try {
            //echo ".................<br/>";
            mysqli_query ($request) or die('Error database when insert targetproduct : '.$request);
        } catch (Exception $e) {;
        echo '<br/>Caught exception: ',  $e->getMessage(), "\n";
        }
                $nbnewprod++;
              }
           }
    echo "Ok!</br>";
            mysql_close();
            if(!$unvalidformat){
              echo "<div style='margin: 20px 0px; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 13px;'>";
              echo "For information: nb of new products: ".$nbnewprod." and number of price's change: ".$nbpricechange."<br /><br /><br />\n";

              echo "<a href='tlimporttargetaction.php'>Click here to manage the TARGET products</a></div>";
            }
          }
          else{
            echo "<div style='margin: 10px 0px; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 13px; font-weight: bold; color: red;'>\n <br /> TARGET XML File has been modified but XML format of the new file is still not valid! \n </div>";
          }
        }
        else {
          echo "<div style='margin: 10px 0px; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 13px; font-weight: bold; color: red;'>\n <br /> TARGET XML File Format is not valid!!! Check the file with your webbrother: <a href='".$xmlfilename."' TARGET='_blank'>Click here</a><br />You should phone TARGET in order to fix their mistake. <br /><br />If TARGET can't do anything, click on the following link,<br /> and it is possible that the programme fixes temporarly the TARGET file by modifying it. <a href='".$_SERVER['PHP_SELF']."?Import=Import&action=continue'>Click Here</a> \n </div>";
        }
      }
      else {
         echo "<div style='margin: 10px 0px; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 13px; font-weight: bold; color: red;'>\n The xml file <a href='".$xmlfilename."' TARGET='_blank'>".$xmlfilename."</a> is empty. \n </div>";
      }
    }
    else {
      echo "<div style='margin: 10px 0px; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 13px; font-weight: bold; color: red;'>\n Impossible to read the content of <a href='".$xmlfilename."' TARGET='_blank'>".$xmlfilename."</a> \n </div>";
    }
}
else {
  //connexion to the database
  mysqli_connect(DB_SERVER,DB_SERVER_USERNAME,DB_SERVER_PASSWORD) or die("Connexion failure");
  mysqli_select_db(DB_DATABASE) or die("Connexion failure to the database");

  //get the last import time
  $lastimporttime = "";
  $request = "SELECT * FROM `tl_target_lastmodification` ORDER BY `lastmodification_time` DESC LIMIT 1";

  if($result = mysqli_query($request)){
   if($rowArray = mysqli_fetch_array($result)){
     $lastimporttime = $rowArray["lastmodification_time"];
   }
   mysqli_free_result($result);
  }
  mysql_close();

  $displaylastimporttime = false;
  if(strlen($lastimporttime) == 19){
    $lastimporttimearray = date_parse($lastimporttime);
    $lastimporttimeobject = date_create();
    $lastimporttimeobject->setDate($lastimporttimearray['year'], $lastimporttimearray['month'], $lastimporttimearray['day']);
    $lastimporttimeobject->setTime($lastimporttimearray['hour'], $lastimporttimearray['minute'], $lastimporttimearray['second']);
    $displaylastimporttime = true;
  }
  echo "<div style='margin: 10px 0px 0px 0px; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 12px; font-weight: bold; color: #000000;'>\n Click on the button only once and wait until the end of the treatment... At the end, a new text will appear. </div>";
  if($displaylastimporttime){
    echo "<div style='margin: 0; text-align: center; font-family: verdana, arial, helvetica, sans-serif; font-size: 12px; color: #000000;'>\nLast Import: ".$lastimporttimeobject->format("l d/m/Y")." at ".$lastimporttimeobject->format("H:i:s").". </div>";
  }
}
?>

yaginuk 0 Newbie Poster · Answer 3 · 2015-04-16T15:48:07+00:00

an ammendment to lines 175, 176, 270 and 271 i have been updating deprecated extensions, i have changed it back to mysql_connect and mysql_select_db as it failed to connect.
this file being an admin only page, for importing xml to main database.
I also didnt state its for zencart

yaginuk 0 Newbie Poster · Answer 4 · 2015-04-16T15:49:26+00:00

Congratulations! You're no longer a DaniWeb newbie.<br /> <br />
Your DaniWeb account has just been upgraded from newbie status and now you have the ability to take advantage of everything the community has to offer.<br /> <br />
You can now enjoy an advertisement-free DaniWeb by ticking the checkbox to Disable Ads in your profile. You will no longer have to fill out the human verification check when you post. You can also now send unlimited private messages, participate in live chat, opt-in to mailing list-style notifications which allows you to read and contribute posts via email, contribute new code snippets, contribute editorial, access Area 51 and tag articles with never-before-used tags.

diafol · Answer 5 · 2015-04-16T19:11:08+00:00

300 lines of code is a little extreme. It's also giving me a king-sized headache. I don't know how you manage to maintain this with all the conditionals and php-held html. Sorry yag, this will take more time that I currently have. I usually set aside round 10 minutes for these things.