[SOLVED] Importing xml into A mysql database using php

Colton.Wagner · September 24, 2009

Here is my problem I am trying to import an xml file that is basically formatted like this.

<?xml version="1.0" encoding="UTF-8"?>
<AvailableBatch>
  <Date>09/23/2009</Date>
  <UDT>1253751301</UDT>
  <Type>Full</Type>
  <Available>
    <Sku>427890</Sku>
    <Part>1001BSG</Part>
    <Qty>38</Qty>
    <Time>20:18:11</Time>
  </Available>
  <Available>
    <Sku>427870</Sku>
    <Part>1003BSS</Part>
    <Qty>218</Qty>
    <Time>20:18:11</Time>
  </Available>

All i need to import are the values of Sku Part and Qty.

Here is what I have programmed so far but it doesn't seem to work for me.

<html>
<head>
	<link rel="shortcut icon" href="/favicon.ico" />
	<title>
		Title here i took it out =p
	</title>
</head>

<body>
<?php

$con = mysql_connect("localhost","root","tookitout=p");

if (!$con){
die("Connection Error:" . mysql_error());
}
$parser = xml_parser_create();

function start($parser, $element_name, $element_attrs)
{
	$result = mysql_query($query) or die(mysql_error());

	if(!$result) {
		echo mysql_error();
	} 
	else 
	{
			mysql_select_db("Products", $con);
			mysql_query("INSERT INTO products (id) VALUES ('')");
		switch($element_name)
			{
				case "SKU":
					'"));mysql_query("INSERT INTO products sku VALUES ("';
				break;
				case "PART":
					'"));mysql_query("INSERT INTO products item_id VALUES ("';
				break;
				case "QTY":
					'"));mysql_query("INSERT INTO products quanity VALUES ("';
				break;
					'"));mysql_query("INSERT INTO products time VALUES ("';
				case "TIME":
				break;
				default:
					'"))';
			}
		}
}

function stop($parser, $element_name)
	{
	}

function char($parser,$data)
  {
  echo $data;
  }

xml_set_element_handler($parser, "start", "stop");
xml_set_character_data_handler($parser, "char");

$fp=fopen("http://morris.morriscostumes.com/out/available_batchnynyy_001.xml","r");

while ($data=fread($fp,4096))
  {
  xml_parse($parser,$data,feof($fp)) or
  die (sprintf("XML Error: %s at line %d",
  xml_error_string(xml_get_error_code($parser)),
  xml_get_current_line_number($parser)));
  }

xml_parser_free($parser);
?>
	</body>
</html>

I've been looking around and there isn't a whole lot of people that can do this. So please help me.

Colton.Wagner · September 28, 2009

Bump

PFMaBiSmAd · September 28, 2009

The following is quick and dirty code that is specific to the data you showed. You need to put back in your actual mysql code (see the comments, two places) -

<?php
session_start(); 
$_SESSION['parts'] = array(); // cheat and share a global session variable between the different xml handler functions
?>
<html>
   <head>
      <link rel="shortcut icon" href="/favicon.ico" />
      <title>
         Title here i took it out =p
      </title>
   </head>
   
   <body>
<?php

// your mysql_connect() and mysql_select_db() goes here ...

$parser = xml_parser_create();

function start($parser, $element_name, $element_attrs)
   {
         switch($element_name)
            {
               case "SKU":
				// this is the start of a set of data
				$_SESSION['parts'] = array(); // create an empty set
               break;
               case "TIME":
				// this is the tag that is after the end of the stated set of data, complete and execute the query here -
				// code to build the whole query statement and your mysql_query() goes here ...
				print_r($_SESSION['parts']); // contains the three values for sku, part, qty
				echo "<br />";
               break;
               default:
            }
   }

function stop($parser, $element_name)
      {
      }
      
function char($parser,$data)
{
$data = trim($data);
if($data != ''){
	if(!isset($_SESSION['parts']['SKU'])){
		$_SESSION['parts']['SKU'] = $data;
	} elseif(!isset($_SESSION['parts']['PART'])){
		$_SESSION['parts']['PART'] = $data;
	} elseif (!isset($_SESSION['parts']['QTY'])){
		$_SESSION['parts']['QTY'] = $data;
	}
}
}

xml_set_element_handler($parser, "start", "stop");
xml_set_character_data_handler($parser, "char");

$fp=fopen("http://morris.morriscostumes.com/out/available_batchnynyy_001.xml","r");

while ($data=fread($fp,4096))
  {
  xml_parse($parser,$data,feof($fp)) or
  die (sprintf("XML Error: %s at line %d",
  xml_error_string(xml_get_error_code($parser)),
  xml_get_current_line_number($parser)));
  }

xml_parser_free($parser);
?>
      </body>
</html>

Colton.Wagner · October 5, 2009

Thanks that helped out a lot I still have been unable to parse the XML into the database but I feel that I am on the brink of understanding the problem. I never thought of looking at it in a session array. You have been very helpful I would rep you but I'm not sure if I can or it exists on this smf forum.

Colton.Wagner · October 5, 2009

I can't seem to get it into the database still. It throws no errors or exceptions. Here is what I got.

<?php
session_start(); 
$_SESSION['parts'] = array();
?>
<html>
   <head>
      <link rel="shortcut icon" href="/favicon.ico" />
      <title>
         Page Title Goes Here
      </title>
   </head>
   
	<body>
<?php

mysql_connect('connection', 'username', 'password');
mysql_select_db('products');

$parser = xml_parser_create();

function start($parser, $element_name, $element_attrs)
   {
         switch($element_name)
            {
               case "SKU":
               // this is the start of a set of data
               $_SESSION['parts'] = array(); // create an empty set
               break;
		   case "TIME";
		   mysql_query("INSERT INTO products (sku, part, qty) VALUES ('$data')");
		   echo "<p>Completed Successfully!</p>";
               break;
               default:
            }
   }

function stop($parser, $element_name)
      {
      }
      
function char($parser,$data)
{
   $data = trim($data);
   if($data != ''){
      if(!isset($_SESSION['parts']['SKU'])){
         $_SESSION['parts']['SKU'] = $data;
      } elseif(!isset($_SESSION['parts']['PART'])){
         $_SESSION['parts']['PART'] = $data;
      } elseif (!isset($_SESSION['parts']['QTY'])){
         $_SESSION['parts']['QTY'] = $data;
      }
   }
}

xml_set_element_handler($parser, "start", "stop");
xml_set_character_data_handler($parser, "char");

$fp=fopen("http://morris.morriscostumes.com/out/available_batchnynyy_001.xml","r");

while ($data=fread($fp,4096))
  {
  xml_parse($parser,$data,feof($fp)) or
  die (sprintf("XML Error: %s at line %d",
  xml_error_string(xml_get_error_code($parser)),
  xml_get_current_line_number($parser)));
  }

xml_parser_free($parser);
?>
      </body>
</html>

redarrow · October 5, 2009

Put this at the top off your page see what the error is please....

<?php error_reporting(E_ALL); ?>

also split the insert up, so you can echo it out and use mysql_error() function.

Colton.Wagner · October 5, 2009

Notice: Undefined variable: data in

PFMaBiSmAd · October 5, 2009

The three values are in the array, just put them into the query and execute it -

               case "TIME":
				// this is the tag that is after the end of the stated set of data, complete and execute the query here -
				// code to build your whole query statement and your mysql_query() goes here ...
				// print_r($_SESSION['parts']); // contains the three values for sku, part, qty
				$query = sprintf("INSERT INTO products (sku, part, qty) VALUES ('%s','%s',%d)",
					mysql_real_escape_string($_SESSION['parts']['SKU']),
					mysql_real_escape_string($_SESSION['parts']['PART']),
					(int)$_SESSION['parts']['QTY']);
				mysql_query($query);
               break;

Colton.Wagner · October 5, 2009

PFMaBiSmAd you are a life saver thank you for your help.

redarrow · October 5, 2009

So the switch is doing no good? if that correct.

but why?

if the word TIME is called then should up date..

if you of raped this around the query

mysql_query($query); added mysql_error()

would you off got the correct error statement....

Sign In

[SOLVED] Importing xml into A mysql database using php

Recommended Posts

Colton.Wagner

Link to comment

Share on other sites

Colton.Wagner

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

Colton.Wagner

Link to comment

Share on other sites

Colton.Wagner

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

Colton.Wagner

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

Colton.Wagner

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information