Php XML worksheet reader.

shankar · 06-03-2009, 03:55 AM

This might be useful, its a script to read XML files and add these values to the database.

To achieve this we need
1.An html form to upload your XML worksheets.
2.A mysql db table with entities equal to first cell in XML.
3.Code to extract values of each cell in XML and add to the db.

-----------------------------------------------------------------
1.Create an upload form. (index.php)
-----------------------------------------------------------------

html4strict Code:

<html>
<body>
<form enctype="multipart/form-data" 
  action="import.php" method="post">
  <input type="hidden" name="MAX_FILE_SIZE" value="2000000" />
  <table border="0px">
  <tr>
  <td>Excel File:</td>
  <td><input type="file" name="file" /></td>
  <td><input type="submit" value="Upload" /></td>
  </tr>
  </table>
  </form>
</body>
</html>

-----------------------------------------------------------------
2.A db with entities corresponding to the first cell in XML.
-----------------------------------------------------------------

sql Code:

CREATE DATABASE `stud_db` ;

CREATE TABLE `stud_db`.`stud_details` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`fname` VARCHAR( 25 ) NOT NULL ,
`mname` VARCHAR( 25 ) NOT NULL ,
`lname` VARCHAR( 25 ) NOT NULL 
) ENGINE = MYISAM ;

-- -------------------------------------------[...]
-----------------------------------------------------------------
3.Process the form and add data to the db. (import.php)
-----------------------------------------------------------------

php Code:

$data = array();
  
  function add_person( $first, $middle, $last)
  {
  global $data;
  
  $data []= array(
  'first' => $first,
  'middle' => $middle,
  'last' => $last,
  );
  }
  
  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 )
  {
  $first = "";
  $middle = "";
  $last = "";
  $index = 1;
  $cells = $row->getElementsByTagName( 'Cell' );
  foreach( $cells as $cell )
  { 
  $ind = $cell->getAttribute( 'Index' );
  if ( $ind != null ) $index = $ind;
  
  if ( $index == 1 ) $first = $cell->nodeValue;
  if ( $index == 2 ) $middle = $cell->nodeValue;
  if ( $index == 3 ) $last = $cell->nodeValue;
  
  $index += 1;
  }
  add_person( $first, $middle, $last );
  }
  $first_row = false;
  }
  }
  ?>
  <html>
  <body>
  <center><h1>All these values are added to the database.</h1></center>
  <table>
  <tr>
  <th>First</th>
  <th>Middle</th>
  <th>Last</th>
  </tr>
  <?php 
    include("connect.php");
    foreach( $data as $row ) 
    {
    $first = $row['first'];
    $middle = $row['middle'];
    $last = $row['last'];
    $query = mysql_query("INSERT INTO `stud_database`.`stud_details` (`fname`,`mname`,`lname`)VALUES('$first','$middle','$last')");
    mysql_query($query);
    }
    
    foreach( $data as $row ) { 
?>
  <tr>
  <td><?php echo $row['first'] ; ?></td>
  <td><?php echo $row['middle'] ; ?></td>
  <td><?php echo $row['last']; ?></td>
  </tr>
  <?php
  }
  ?>
  </table>
  
  <?php //echo $_FILES['file']['type'];?>
  </body>
  </html>

-----------------------------------------------------------------

*Make sure the id attr in the table is set to auto_incrment.

Hope this helped. Thank you for viewing this post.