Importing Excel XML data into a table.

MySQL database
#1

I am expanding on an example I found that is intended to import Excel XML data into a database table. The database has been created, as has the table. I can see them via phpAdmin. And the XML file seems fine also.

While I’ve changed some things up in the example to reflect my situation I believe I’ve been careful not to mess anything up, but nothing happens when I run it. No errors. No nothing.

One line of code in particular (in Block 2) isn’t making complete sense yet, and that line is “if ( $_FILES[‘file’][‘tmp_name’] )”, and another line with the same “$_Files” reference. I’ve read up on that superglobal but this particular use of it is still unclear. I believe I know what “[‘file]’” refers to, but can someone explain to me what “[‘tmp_name]’” refers to?

If it isn’t obvious, code block 1 initiates code block 2. Take it easy on me please. I’m new to PHP. And I apologize for the many lines. Just figured I’d leave it all there.

Does anyone see any glaring errors?

Thanks!

Block 1
[php]

Choose file:
[/php]

Block 2 import_facilities.php
[php]<?php

$data = array();

$con=mysqli_connect(“localhost”,“root”,“password”,“facilities_db”);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();

function add_facility( $Facility_ID, $District, $PO_Name, $Unit_Name, $Street, $County, $City, $State, $ZIP_3, $ZIP_Code, $Ownership, $Maint_Resp, $Occupy_Date, $Int_SF, $Site_SF, $Annual_Rent )
{
global $data, $db;

$sth = $db->prepare( “INSERT INTO Facilities VALUES( 0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)” );
$db->execute( $sth, array( $Facility_ID, $District, $PO_Name, $Unit_Name, $Street, $County, $City, $State, $ZIP_3, $ZIP_Code, $Ownership, $Maint_Resp, $Occupy_Date, $Int_SF, $Site_SF, $Annual_Rent ) );

$data []= array(
‘Facility_ID’ => $Facility_ID,
‘District’ => $District,
‘PO_Name’ => $PO_Name,
‘Unit_Name’ => $Unit_Name,
‘Street’ => $Street,
‘County’ => $County,
‘City’ => $City,
‘State’ => $State,
‘ZIP_3’ => $ZIP_3,
‘ZIP_Code’ => $ZIP_Code,
‘Ownership’ => $Ownership,
‘Maint_Resp’ => $Maint_Resp,
‘Occupy_Date’ => $Occupy_Date,
‘Int_SF’ => $Int_SF,
‘Site_SF’ => $Site_SF,
‘Annual_Rent’ => $Annual_Rent
);
}

if ( $_FILES[‘file’][‘tmp_name’] )
{
$dom = DOMDocument::load( $_FILES[‘file’][‘tmp_name’] );
$rows = $dom->getElementsByTagName( ‘Row’ );
$first_row = true;
foreach ($rows as $row)
{
if ( !$first_row )
{
$Facility_ID = “”;
$District = “”;
$PO_Name = “”;
$Unit_Name = “”;
$Street = “”;
$County = “”;
$City = “”;
$State = “”;
$ZIP_3 = “”;
$ZIP_Code = “”;
$Ownership = “”;
$Maint_Resp = “”;
$Occupy_Date = “”;
$Int_SF = “”;
$Site_SF = “”;
$Annual_Rent = “”;

 $index = 1;
 $cells = $row->getElementsByTagName( 'Cell' );
 foreach( $cells as $cell )
 {
   $ind = $cell->getAttribute( 'Index' );
   if ( $ind != null ) $index = $ind;

	   if ( $index == 1 ) $Facility_ID = $cell->nodeValue;
	   if ( $index == 2 ) $District = $cell->nodeValue;
	   if ( $index == 3 ) $PO_Name = $cell->nodeValue;
	   if ( $index == 4 ) $Unit_Name = $cell->nodeValue;
	   if ( $index == 5 ) $Street = $cell->nodeValue;
	   if ( $index == 6 ) $County = $cell->nodeValue;
	   if ( $index == 7 ) $City = $cell->nodeValue;
	   if ( $index == 8 ) $State = $cell->nodeValue;
	   if ( $index == 9 ) $ZIP_3 = $cell->nodeValue;
	   if ( $index == 10 ) $ZIP_Code = $cell->nodeValue;
	   if ( $index == 11 ) $Ownership = $cell->nodeValue;
	   if ( $index == 12 ) $Maint_Resp = $cell->nodeValue;
	   if ( $index == 13 ) $Occupy_Date = $cell->nodeValue;
	   if ( $index == 14 ) $Int_SF = $cell->nodeValue;
	   if ( $index == 15 ) $Site_SF = $cell->nodeValue;
	   if ( $index == 16 ) $Annual_Rent = $cell->nodeValue;

   $index += 1;
 }
 add_facility( $Facility_ID, $District, $PO_Name, $Unit_Name, $Street, $County, $City, $State, $ZIP_3, $ZIP_Code, $Ownership, $Maint_Resp, $Occupy_Date, $Int_SF, $Site_SF, $Annual_Rent );

}
$first_row = false;
}
}
?>

These records have been added to the database: <?php foreach( $data as $row ) { ?> < < < < < < < < < < < < < < < < <?php } ?>
Facility_ID District PO_Name Unit_Name Street County City State ZIP_3 ZIP_Code Ownership Maint_Resp Occupy_Date Int_SF Site_SF Annual_Rent
<?php echo( $row['Facility_ID'] ); ?><?php echo( $row['District'] ); ?><?php echo( $row['PO_Name'] ); ?><?php echo( $row['Unit_Name'] ); ?><?php echo( $row['Street'] ); ?><?php echo( $row['County'] ); ?><?php echo( $row['City'] ); ?><?php echo( $row['State'] ); ?><?php echo( $row['ZIP_3'] ); ?><?php echo( $row['ZIP_Code'] ); ?><?php echo( $row['Ownership'] ); ?><?php echo( $row['Maint_Resp'] ); ?><?php echo( $row['Occupy_Date'] ); ?><?php echo( $row['Int_SF'] ); ?><?php echo( $row['Site_SF'] ); ?><?php echo( $row['Annual_Rent'] ); ?>
Click here for the entire table. [/php]
#2

My apologies. The real culprit was a missing “}”. Rookie mistake.

There are still a couple of issues, but at least I’m getting something now.

#3

Just thought I’d post my solution. I went the simple route for now and removed the “prepare” and “execute” statements, but will likely work them back in as I get better. And all that “index” stuff in the original was irrelevant as there are no index tags in the xml document. Just clean rows and cells.

Anyway, this code works like a charm, even if it is a little less than optimal. Function first, elegance in time.

[php]<?php

$con=mysqli_connect(“localhost”,“root”,“passwordhere”,“facilities_db”);
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
else
{
echo “Connection made.”;
echo “
”;
}

if ( $_FILES['file']['tmp_name'] )
{
	
	$dom = DOMDocument::load( $_FILES['file']['tmp_name'] );
	
	$rows = $dom->getElementsByTagName('Row');

	foreach( $rows as $row )
		{
			$cells = $row->getElementsByTagName( "Cell" );
				
			$Facility_ID = $cells->item(0)->nodeValue;
			$District = $cells->item(1)->nodeValue;
	   		$PO_Name = $cells->item(2)->nodeValue;
	   		$Unit_Name = $cells->item(3)->nodeValue;
	   		$Street = $cells->item(4)->nodeValue;
	   		$County = $cells->item(5)->nodeValue;
	   		$City = $cells->item(6)->nodeValue;
	   		$State = $cells->item(7)->nodeValue;
	   		$ZIP_Code = $cells->item(8)->nodeValue;

mysqli_query($con,“INSERT INTO Facilities (Facility_ID, District, PO_Name, Unit_Name, Street, County, City, State, ZIP_Code)
VALUES (’$Facility_ID’, ‘$District’, ‘$PO_Name’, ‘$Unit_Name’, ‘$Street’, ‘$County’, ‘$City’, ‘$State’, ‘$ZIP_Code’)”);
}
}
else
{
echo “File not found.”;
}

mysqli_close($con);

?>[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service