Christian Hennig
Christian Hennig

Reputation: 61

Updating multiple rows with PHP

I want to export my mysql-database into a php-form, and update it, if I make any changes to the input-boxes, through the submit-button. My main-problem is, that I want to update multiple rows at once. Here is my code; I don't get any errors, it just doesn't work.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd">
<html>
  <head>
    <style> 
      <!-- 
        A:link {text-decoration: none; color:#005BA2}
        A:visited {text-decoration: none; color:#005BA2}
        A:active {text-decoration: underline; color:#FF0000}
        A:hover { color: #FF0000; line-height: normal; text-decoration: underline; background-color: #FFFFFF }
        input.center { text-align:center; }
        body { color: #0061AE; font-size: 13px; font-family: Arial, Helvetica, sans-serif; }

      -->
    </style>
  </head>
  <body>
    <center>
      <form method="post" action="#">
        <?php

        // Fehlermeldungen ausschalten wegen undef. Variablen
     //     error_reporting(E_ERROR | E_PARSE);

        // Datenbankvariablen setzen
          $hostname = "localhost";
          $database = "intranet";
          $username = "intranet";
          $password = "intranet";
          $table = "arbeitsanweisungen";

        // Verbindung mit Datenbank herstellen
          $db_connect = mysql_connect($hostname, $username, $password);
          mysql_select_db("$database") or die();

        // Datenbankabfrage
          $sql = "SELECT * FROM $table ORDER BY kennung";
          $result = mysql_query($sql);


        // Zeilen zählen
          $count = mysql_num_rows($result);

        ?>

        <table border="1" cellspacing="0" cellpadding="2">

          <tr>
            <td align="center"><strong>ID</strong></td>
            <td align="center"><strong>Kennung</strong></td>
            <td align="center"><strong><img src="images/german.jpg"></strong></td>
            <td align="center"><strong><img src="images/usa.jpg"></strong></td>
            <td align="center"><strong>deutscher Titel</strong></td>
            <td align="center"><strong>englischer Titel</strong></td>
            <td align="center"><strong>Mitarbeiter</strong></td>
          </tr>

          <?php
            while($rows=mysql_fetch_array($result)){
          ?>

              <tr>
                <td align="center">
                  <?php 
                    $id[]=$rows['id']; ?><?php echo $rows['id']; 
                  ?>
                    </td>
                    <td align="center">
                      <input name="kennung[]" size="15" type="text" id="kennung" value="<?php echo $rows['kennung']; ?>">
                    </td>
                    <td align="center">
                      <input name="german[]" class="center" size="1" type="text" id="german" value="<?php echo $rows['german']; ?>">
                    </td>
                    <td align="center">
                      <input name="english[]" class="center" size="1" type="text" id="english" value="<?php echo $rows['english']; ?>">
                    </td>
                    <td align="center">
                      <input name="nameDE[]" size="50" type="text" id="nameDE" value="<?php echo $rows['nameDE']; ?>">
                    </td>
                    <td align="center">
                      <input name="nameEN[]" size="50" type="text" id="nameEN" value="<?php echo $rows['nameEN']; ?>">
                    </td>
                    <td align="center">
                      <input name="mitarbeiter[]" size="25" type="text" id="mitarbeiter" value="<?php echo $rows['mitarbeiter']; ?>">
                    </td>
                  </tr>

                  <br>

          <?php
            };
          ?>

        </table>
        <input type="submit" name="Submit" value="Submit">

        <?php
          if(isset($_POST['Submit'])){

            $kennung = $_POST['kennung'];
            $german = $_POST['german'];
            $english = $_POST['english'];
            $nameDE = $_POST['nameDE'];
            $nameEN = $_POST['nameEN'];
            $mitarbeiter = $_POST['mitarbeiter'];


            for($i=0;$i<$count;$i++){
              mysql_query("UPDATE $table SET kennung = '$kennung[$i]', german = '$german[$i]', english = '$english[$i]', nameDE = '$nameDE[$i]', nameEN = '$nameEN[$i]', mitarbeiter = '$mitarbeiter[$i]' WHERE  id = '$id[$i]'");
            }
          }

          // Verbindung schließen
            mysql_close();
        ?>
      </form>
    </center>
  </body>
</html>     

Upvotes: 0

Views: 125

Answers (4)

dipendra
dipendra

Reputation: 251

In side if(isset($_POST['Submit'])){ } condition, please write following code and check again:

 for($i=0;$i<$count;$i++){
          mysql_query("UPDATE $table SET kennung = '".$_POST[$kennung[$i]]."', german = '".$_POST[$german[$i]]."', english = '".$_POST[$english[$i]]."', nameDE = '".$_POST[$nameDE[$i]]."', nameEN = '".$_POST[$nameEN[$i]]."', mitarbeiter = '".$_POST[$mitarbeiter[$i]]."' WHERE  id = '".$id[$i]."'");
        }

Upvotes: 1

Nishant Solanki
Nishant Solanki

Reputation: 2128

if you want to update multiple rows in a single query than you need to use WHERE id IN (); in your query...

so your query will be like

UPDATE $table SET kennung = '$kennung[$i]', german = '$german[$i]', english = '$english[$i]', nameDE = '$nameDE[$i]', nameEN = '$nameEN[$i]', mitarbeiter = '$mitarbeiter[$i]' WHERE  id IN ($id_array)

and your $id_array will contain all the ids which needs to be updated

like

$id_array = array(`1,2,3,4,5`);

Upvotes: 0

Barmar
Barmar

Reputation: 780852

You need to put $rows['id'] into the form:

<td align="center">
  <?php echo $rows['id'] . '<input type="hidden" name="id[]" value="' . $rows['id'] . '">';
  ?>
</td>

Then when you're processing the form, you have to use

$id = $_POST['id'];

Upvotes: 0

Daniel W.
Daniel W.

Reputation: 32280

Don't put arrays directly into a quoted string:

mysql_query("UPDATE $table SET kennung = '$kennung[$i]', ger....")

Properly concatenate it using a .:

mysql_query("UPDATE " . $table . " SET kennung = '" . $kennung[$i] . "', ger..");

Also, enable error reporting:

<?php 
// Put these lines to the top of your script
error_reporting(E_ALL);
ini_set('display_errors', true);
ini_set('display_startup_errors', true);
ini_set('xmlrpc_errors', true);

And check for mysql_last_error(); or mysql_error(); (not sure atm).

mysql_* is deprecated, use mysqli or PDO.

Upvotes: 0

Related Questions