Database not updating in html table

Question

i have html table which output all my database :

Edit(button)
pkg | kodsk | namask | tahun | makmal |  catatan | murid | netbook
a   |   b   |    c   |   d   |    e   |    f     |   g   | h

After user click edit button above table, user can change all the data in table. My problem is, only some row can be edited. For example my database have 8 row, only row number 8 and 7 can be edit. Other row if try to change the data, nothing happen. My code is ok without any error, so i don't know where the problem is. Please someone help me,i just learn for fun.

  <?php
session_start();
include("connections.php");
?>

<meta http-equiv="refresh" content="10";>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
       <table border="3" style= "background-color: #84ed86; color: #761a9b; margin: 5 auto;" >
         <?php
            $result = $connect->query("SELECT * FROM data2017 INNER JOIN pengguna USING (pkg)
                                        WHERE pengguna.username = '$_SESSION[username]'");

               echo "<tr>";
               echo "<th>pkg</th>";
               echo "<th>kodsk</th>";
               echo "<th>sek</th>";
               echo "<th>tahun</th>";
               echo "<th>makmal</th>";
               echo "<th>catatan</th>";
               echo "<th>murid</th>";
               echo "<th>netbook</th>";
               echo "</tr>";

            while($row = $result->fetch(PDO::FETCH_ASSOC)){
                echo "<tr>";
                echo "<input type='hidden' name='bil' value='".$row['bil']."' />";
                echo "<td><input type='text' name='pkg' value='".$row['pkg']."' /></td>";
                echo "<td><input type='text' name='kodsk' value='".$row['kodsk']."' /></td>";
                echo "<td><input type='text' name='namask' value='".$row['namask']."' /></td>";
                echo "<td><input type='text' name='tahun' value='".$row['tahun']."' /></td>";
                echo "<td><input type='text' name='makmal' value='".$row['makmal']."' /></td>";
                echo "<td><input type='text' name='catatan' value='".$row['catatan']."' /></td>";
                echo "<td><input type='text' name='murid' value='".$row['murid']."' /></td>";
                echo "<td><input type='text' name='netbook' value='".$row['netbook']."' /></td>";
                echo "</tr>";
               }
            echo "<input type='submit' name='update' value='UPDATE' />";
         ?>
      </table>
   </form>

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

    $bil = $_POST['bil'];
    $pkg = $_POST['pkg'];
    $kodsk = $_POST['kodsk'];
    $namask = $_POST['namask']; 
    $tahun = $_POST['tahun'];
    $makmal = $_POST['makmal'];
    $catatan = $_POST['catatan'];
    $murid = $_POST['murid'];
    $netbook = $_POST['netbook'];


    $sql =  "UPDATE `data2017` SET `pkg`=:pkg,`kodsk`=:kodsk,`namask`=:namask,`tahun`=:tahun,`makmal`=:makmal,`catatan`=:catatan,`murid`=:murid,`netbook`=:netbook WHERE `bil`=:bil";

    $stmt = $connect->prepare($sql);
    $pdoExec = $stmt->execute(array(":pkg"=>$pkg,":kodsk"=>$kodsk,":namask"=>$namask,":tahun"=>$tahun,":makmal"=>$makmal,":catatan"=>$catatan,":murid"=>$murid,":netbook"=>$netbook,":bil"=>$bil));

    if($pdoExec)
    {
        echo 'Data Updated';
    }
    else
    {
        echo 'Fail To Update';
    }

}
?>

Looks like the inputs have the same names in every row. They'll overwrite each other and only the last row will be posted. To troubleshoot, try outputting the posted data: `echo "
".print_r($_POST,true)."
";` — showdev, Feb 22 '18 at 00:00
How many rows are displayed at the same time? just one? or many? — Kenneth, Feb 22 '18 at 00:14
Shouldn't `if(!isset($_POST['update']))` be `if(isset($_POST['update'])) ` — Kenneth, Feb 22 '18 at 00:16
it output all row from my database nicely as i want based on session. But when i want update in html table, some row not changed. Sory sir i forgot delete the ! on isset. Already done taht but still same problem. — Dikwan, Feb 22 '18 at 00:18
Put it where your `$_POST` values are available. But "where" is less important than understanding a critical concept: the inputs have the same names for every row, so the row values overwrite each other and only the last row is effectively posted. The debugging code was just intended to demonstrate that problem. — showdev, Feb 22 '18 at 00:21
It output this sir : Array ( [update] => UPDATE [bil] => 203 [pkg] => BAGAN SERAI [kodsk] => 1000 [namask] => 2000 [tahun] => 101010 [makmal] => 1111 [catatan] => Peralatan ICT Terhad [murid] => 500 [netbook] => 15 ) When i do on other row, nothing happen. it just print the same . — Dikwan, Feb 22 '18 at 00:23

Kenneth · Accepted Answer · 2018-02-22T01:11:30.320

The problem is that you are not uniquely identifying each and every form element. If you have 8 rows, then you have 8 values named pkg, but there can only be one $_POST['pkg'] This is why the last row usually wins and is the only one updated.

You are going to have to add the bil field to every input name, then separate it out later.

ID and NAME tokens must begin with a letter ([A-Za-z]) and may be followed by any number of letters, digits ([0-9]), hyphens ("-"), underscores ("_"), colons (":"), and periods ("."). Source

Try this:

edit 1: I had to edit this when I realized placing $bil at the beginning of the input name would violate the statement I placed above. I moved it to the right side of the name.

edit 2: Modified my method to build the array in html like showdev taught me

<?php
session_start();
include("connections.php");
?>

<meta http-equiv="refresh" content="10";>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
       <table border="3" style= "background-color: #84ed86; color: #761a9b; margin: 5 auto;" >
         <?php
            $result = $connect->query("SELECT * FROM data2017 INNER JOIN pengguna USING (pkg)
                                        WHERE pengguna.username = '$_SESSION[username]'");

               echo "<tr>";
               echo "<th>pkg</th>";
               echo "<th>kodsk</th>";
               echo "<th>sek</th>";
               echo "<th>tahun</th>";
               echo "<th>makmal</th>";
               echo "<th>catatan</th>";
               echo "<th>murid</th>";
               echo "<th>netbook</th>";
               echo "</tr>";

            while($row = $result->fetch(PDO::FETCH_ASSOC)){
                $bil = $row['bil'];
                echo "<tr>";
                echo "<input type='hidden'   name='row[$bil][:bil]'     value='$bil' />";
                echo "<td><input type='text' name='row[$bil][:pkg]'     value='".$row['pkg']."' /></td>";
                echo "<td><input type='text' name='row[$bil][:kodsk]'   value='".$row['kodsk']."' /></td>";
                echo "<td><input type='text' name='row[$bil][:namask]'  value='".$row['namask']."' /></td>";
                echo "<td><input type='text' name='row[$bil][:tahun]'   value='".$row['tahun']."' /></td>";
                echo "<td><input type='text' name='row[$bil][:makmal]'  value='".$row['makmal']."' /></td>";
                echo "<td><input type='text' name='row[$bil][:catatan]' value='".$row['catatan']."' /></td>";
                echo "<td><input type='text' name='row[$bil][:murid]'   value='".$row['murid']."' /></td>";
                echo "<td><input type='text' name='row[$bil][:netbook]' value='".$row['netbook']."' /></td>";
                echo "</tr>";
               }
            echo "<input type='submit' name='update' value='UPDATE' />";
         ?>
      </table>
   </form>

<?php
   if(isset($_POST['update'])) 
   {
        $sql =  "UPDATE `data2017` SET `pkg`=:pkg,`kodsk`=:kodsk,`namask`=:namask,`tahun`=:tahun,`makmal`=:makmal,`catatan`=:catatan,`murid`=:murid,`netbook`=:netbook WHERE `bil`=:bil";

        $stmt = $connect->prepare($sql);

        foreach($_POST['row'] as $data)
        {
            $pdoExec = $stmt->execute($data);

            if($pdoExec) { echo 'Data Updated'; } else { echo 'Fail To Update'; }
        }
    }
?>

thanks bro, it worked. Now i know the problem is at name='......' Thanks very much with all the explanation and edit my code. May god bless u — Dikwan, Feb 22 '18 at 02:15

showdev · Answer 2 · 2018-02-22T00:58:59.057

The inputs have the same names for every row, so the row values overwrite each other and only the last row is effectively posted.

One method is to structure posted data as an array, including a row identifier to distinguish values for different rows. Ideally, the row identifier is a numeric PRIMARY index.

For reference, see How do I create arrays in a HTML <form>?

Here's an example:

while($row = $result->fetch(PDO::FETCH_ASSOC)){
  echo '<tr>
          <td>
            <input type="text" name="row['.$row['bil'].'][pkg]" value="'.$row['pkg'].'">
          </td>
          <td>
            <input type="text" name="row['.$row['bil'].'][kodsk]" value="'.$row['kodsk'].'">
          </td>
        </tr>";
}

Then you'll end up with a posted array like this:

Array
(
    [row] => Array
        (
            [1] => Array
                (
                    [pkg] => stuff
                    [kodsk] => things
                )

            [2] => Array
                (
                    [pkg] => other
                    [kodsk] => another
                )

        )

)

And you can iterate through that array to update the database, something like this:

if (!empty($_POST['row'])) {

  $sql =  "UPDATE `data2017` SET `pkg`=:pkg, `kodsk`=:kodsk WHERE `bil`=:bil";
  $stmt = $connect->prepare($sql);

  foreach ($_POST['row'] as $bil => $row) {

    $pdoExec = $stmt->execute(
      array(":pkg"=>$row['pkg'],
            ":kodsk"=>$row['kodsk'],
            ":bil"=>$bil)
    );

  }

}

I learned something new today. I had no idea you could populate the `$_POST` array like that. After 15 years in PHP, how I didn't know that blows my mind! — Kenneth, Feb 22 '18 at 00:58
Thanks bro, now i know where the problem. Such a great explanation and help. Both codes from u two work perfectly. May god bless u two. — Dikwan, Feb 22 '18 at 02:18

Database not updating in html table

2 Answers2