Page 1 of 1

import excel file in xml format in to mysql table

Posted: Thu Dec 01, 2011 1:45 am
by chynphp
hi all,im new in php currently i'm facing the problem uploading excel file xml format to mysql table.Anyone pls help me below is my code any help will be appreciate.Thank you!

<?php
require_once( "dbConnection.php" );

$data = array();

$db = @mysql_connect('localhost', 'root', '') or die ( mysql_error() );
mysql_select_db('myboardband', $db);

function add_person( $NoID, $AgentID, $CustomerName, $OrderNumber, $State, $UNIFIPackage, $CompleteInstallationDate, $NewCustomer, $Migration, $Commission, $ReferenceList, $ValidationStatus, $AgentName )
{
global $data, $db;

$query = sprintf(
'INSERT INTO records1 VALUES( 0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )' ,
mysql_real_escape_string($NoID),
mysql_real_escape_string($AgentID),
mysql_real_escape_string($CustomerName),
mysql_real_escape_string($OrderNumber),
mysql_real_escape_string($State),
mysql_real_escape_string($UNIFIPackage),
mysql_real_escape_string($CompleteInstallationDate),
mysql_real_escape_string($NewCustomer),
mysql_real_escape_string($Migration),
mysql_real_escape_string($Commission),
mysql_real_escape_string($ReferenceList),
mysql_real_escape_string($ValidationStatus),
mysql_real_escape_string($AgentName)

);

mysql_query($query, $db);

$data []= array(
'NoID' => $NoID,
'AgentID' => $AgentID,
'CustomerName' => $CustomerName,
'OrderNumber' => $OrderNumber,
'State' => $State,
'UNIFIPackage' => $UNIFIPackage,
'CompleteInstallationDate' => $CompleteInstallationDate,
'NewCustomer' => $NewCustomer,
'Migration' => $Migration,
'Commission' => $Commission,
'ReferenceList' => $ReferenceList,
'ValidationStatus' => $ValidationStatus,
'AgentName' => $AgentName

);
}

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 )
{
$NoID = "";
$AgentID = "";
$CustomerName = "";
$OrderNumber = "";
$State = "";
$UNIFIPackage = "";
$CompleteInstallationDate = "";
$NewCustomer = "";
$Migration = "";
$Commission = "";
$ReferenceList = "";
$ValidationStatus = "";
$AgentName = "";

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

if ( $index == 1 ) $NoID = $cell->nodeValue;
if ( $index == 2 ) $AgentID = $cell->nodeValue;
if ( $index == 3 ) $CustomerName = $cell->nodeValue;
if ( $index == 4 ) $OrderNumber = $cell->nodeValue;
if ( $index == 5 ) $State = $cell->nodeValue;
if ( $index == 6 ) $UNIFIPackage = $cell->nodeValue;
if ( $index == 7 ) $CompleteInstallationDate = $cell->nodeValue;
if ( $index == 8 ) $NewCustomer = $cell->nodeValue;
if ( $index == 9 ) $Migration = $cell->nodeValue;
if ( $index == 10 ) $Commission = $cell->nodeValue;
if ( $index == 11) $ReferenceList = $cell->nodeValue;
if ( $index == 12) $ValidationStatus = $cell->nodeValue;
if ( $index == 13) $AgentName = $cell->nodeValue;


$index += 1;
}
add_person( $NoID, $AgentID, $CustomerName, $OrderNumber, $State, $UNIFIPackage, $CompleteInstallationDate, $NewCustomer, $Migration, $Commission, $ReferenceList, $ValidationStatus, $AgentName );
}
$first_row = false;
}
}
?>
<html>
<body>
These records have been added to the database:
<table>
<tr>
<th>NoID</th>
<th>AgentID</th>
<th>CustomerName</th>
<th>OrderNumber</th>
<th>State</th>
<th>UNIFIPackage</th>
<th>CompleteInstallationDate</th>
<th>NewCustomer</th>
<th>Migration</th>
<th>Commission</th>
<th>ReferenceList</th>
<th>ValidationStatus</th>
<th>AgentName</th>

</tr>
<?php foreach( $data as $row ) { ?>
<tr>
<td><?php echo( $row['NoID'] ); ?></td>
<td><?php echo( $row['AgentID'] ); ?></td>
<td><?php echo( $row['CustomerName'] ); ?></td>
<td><?php echo( $row['OrderNumber'] ); ?></td>
<td><?php echo( $row['State'] ); ?></td>
<td><?php echo( $row['UNIFIPackage'] ); ?></td>
<td><?php echo( $row['CompleteInstallationDate'] ); ?></td>
<td><?php echo( $row['NewCustomer'] ); ?></td>
<td><?php echo( $row['Migration'] ); ?></td>
<td><?php echo( $row['Commission'] ); ?></td>
<td><?php echo( $row['ReferenceList'] ); ?></td>
<td><?php echo( $row['ValidationStatus'] ); ?></td>
<td><?php echo( $row['AgentName'] ); ?></td>

</tr>
<?php } ?>
</table>
Click <a href="list.php">here</a> for the entire table.
</body>
</html>

Re: import excel file in xml format in to mysql table

Posted: Thu Dec 01, 2011 8:52 am
by twinedev
What is it doing now? Are you just getting an error executing the INSERT statement, which will error if any of your data is a string, as you do not have any strings in the insert code wrapped with quotes.

-Greg

Re: import excel file in xml format in to mysql table

Posted: Thu Dec 01, 2011 8:53 pm
by chynphp
Hi now the webpage it showing output successfully but it just cant import any data to mysql table :banghead:
the code dont have any error

Re: import excel file in xml format in to mysql table

Posted: Thu Dec 01, 2011 9:04 pm
by mikosiko
chynphp wrote:the code dont have any error
yes it has.... here...

Code: Select all

$query = sprintf( 
'INSERT INTO records1 VALUES( 0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )' , 
mysql_real_escape_string($NoID), 
mysql_real_escape_string($AgentID), 
mysql_real_escape_string($CustomerName), 
mysql_real_escape_string($OrderNumber),
mysql_real_escape_string($State), 
mysql_real_escape_string($UNIFIPackage), 
mysql_real_escape_string($CompleteInstallationDate), 
mysql_real_escape_string($NewCustomer), 
mysql_real_escape_string($Migration), 
mysql_real_escape_string($Commission), 
mysql_real_escape_string($ReferenceList), 
mysql_real_escape_string($ValidationStatus),
mysql_real_escape_string($AgentName) ); 
I count 14 ?'s and only 13 variables... your query should fail with an error.... control the possible error... at least add die() here to catch any error

Code: Select all

mysql_query($query) or die("Insert error: ". mysql_error());

Re: import excel file in xml format in to mysql table

Posted: Thu Dec 01, 2011 9:20 pm
by chynphp
Thank for the reply i added the code it show the insert errors:

Strict Standards: Non-static method DOMDocument::load() should not be called statically in C:\xampp\htdocs\Myboardband\import.php on line 53
Insert error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )' at line 1