PHP read XML and write in Database

Question

i have a XML file and need the data in a database after i checked them. so i display the data in a table and after that i try to write the data in a database. but with my code i get only the first set of data from the xml in my database. but there are at least 20 or more in each xml. How can i write all data in the database?

with this code i display the data from the xml

<form action="insert.php" method="post" />
    <table id="Wagen">
        <thead>
          <tr>        
            <th>Typ</th>
            <th>Kennzeichen</th>
            <th>Fahrer</th>
          </tr>
        </thead>

        <tbody>

    <?php
      $url = ('./cars.xml');
      $xml = simplexml_load_file( urlencode($url), null, true);


       foreach ( $xml->car as $cars ) :?>
    <tr>        
            <td><input type="hidden" name="name" value="<?php echo $cars->typ; ?>"><?php echo $cars->typ; ?></td>
            <td><input type="hidden" name="kfz-nr" value="<?php echo $cars->plate; ?>"><?php echo $cars->plate; ?></td>
            <td><input type="hidden" name="fahrer" value="<?php echo $cars->driver; ?>"><?php echo $cars->driver; ?></td>
    </tr>

<?php endforeach; ?>

       </tbody>
    </table> 
    <input type="submit" name="senden" value="XML speichern" />
</form>

and with this code i try to write the data in the database. but i get only the first row from my table. not the others.

<?php

     error_reporting(E_ALL); 
        $MYSQL_HOST = 'localhost'; 
        $MYSQL_USER = 'username'; 
        $MYSQL_PASS = 'password'; 
        $MYSQL_DATA = 'database'; 

        $connid = @mysql_connect($MYSQL_HOST, $MYSQL_USER, $MYSQL_PASS) OR die("Error: ".mysql_error());
        mysql_select_db($MYSQL_DATA) OR die("Error: ".mysql_error()); 

       if (isset($_POST['senden']))
    {
      $sql = 
              "INSERT INTO myTable 
              (fahrzeug,
               kennzeichen,
               fahrer
              )
              VALUES
              ('".mysql_real_escape_string(trim($_POST['name']))."',
               '".mysql_real_escape_string(trim($_POST['kfz-nr']))."',   
               '".mysql_real_escape_string(trim($_POST['fahrer']))."'); 
                mysql_query($sql) OR die("<pre>\n".$sql."</pre>\n".mysql_error()); 
                mysql_query("SET NAMES 'utf8'");
    }
?>

i hope some one can help me. :)

This is completely correct, that you have only one value. Cause you are sending only one value. — xAqweRx, May 12 '16 at 09:02

Ergo · Accepted Answer · 2016-05-12T09:23:16.483

In your foreach loop, you write multiple sets of hidden inputs with different values, but all of those have the same name so after submitting only one of the inputs gets sent with POST. So in your database code, you just add that single input that gets passed.

In conclusion, you need to have different names for each hidden input and a loop in your database insertion to loop through all sent results.

One approach would be to add [] behind input names, making it return an array.

<input type="hidden" name="name[]" value="<?php echo $cars->typ; ?>">
<input type="hidden" name="kfz-nr[]" value="<?php echo $cars->plate; ?>">
<input type="hidden" name="fahrer[]" value="<?php echo $cars->driver; ?>">

will result in something like:

$_POST[kfz-nr][0]=="ABC123";
$_POST[kfz-nr][1]=="KLM901";
$_POST[kfz-nr][2]=="RGB255";

and so on. Then in your insertion method you do:

if (isset($_POST['senden']))
{
$count = count($_POST['name']);
for($i=0;$i<$count;$i++){
  $sql = 
          "INSERT INTO myTable 
          (fahrzeug,
           kennzeichen,
           fahrer
          )
          VALUES
          ('".mysql_real_escape_string(trim($_POST['name'][$i]))."',
           '".mysql_real_escape_string(trim($_POST['kfz-nr'][$i]))."',   
           '".mysql_real_escape_string(trim($_POST['fahrer'][$i]))."'); 
            mysql_query($sql) OR die("<pre>\n".$sql."</pre>\n".mysql_error()); 
            mysql_query("SET NAMES 'utf8'");
}
}

thanks. both of you. i tried the methode from Ergo and it works perfect. :) — speeddrive, May 12 '16 at 09:31

score 0 · Answer 2 · edited May 23 '17 at 11:54

send data from html
save data as array, not one element ( php example below )
do not use mysql in php. mysqli or PDO

$values = array();
for( $i=0; $i < count( $_POST['names'] ); $i++ ){
    $values[] = "(" . 
             "\"" . mysql_real_escape_string(trim($_POST['name'][$i]).""\",".
             "\"" . mysql_real_escape_string(trim($_POST['kfz-nr'][$i])."\",".
             "\"" . mysql_real_escape_string(trim($_POST['fahrer'][$i])."\"".
             ")";
}

$sql = "INSERT INTO myTable (fahrzeug, kennzeichen, fahrer ) VALUES " . implode($values); 
mysql_query($sql) OR die("<pre><br>" . $sql . "</pre><br>".mysql_error()); 
mysql_query("SET NAMES 'utf8'");

PHP read XML and write in Database

2 Answers2