SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    import excel file in xml format in to mysql table

    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

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    So... what problem do you have?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi it just a excel file save as xml format, currently i m develop a webpage for import excel file to mysql database table. i found this code on the web but it not working. The problem is the excel file is not import to my sql database table it only show the output on webpage.

  4. #4
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    K... your problem is that sprintf doesnt use ?'s.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •