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 "<pre>".print_r($_POST,true)."</pre>"; — showdev
– showdev, Commented Feb 22, 2018 at 0:00
How many rows are displayed at the same time? just one? or many? — Kenneth
– Kenneth, Commented Feb 22, 2018 at 0:14
Shouldn't if(!isset($_POST['update'])) be if(isset($_POST['update'])) — Kenneth
– Kenneth, Commented Feb 22, 2018 at 0: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
– Dikwan, Commented Feb 22, 2018 at 0:18

Kenneth · Accepted Answer · 2018-02-22 01:11:30Z

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

showdev · Accepted Answer · 2018-02-22 00:58:59Z

0

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)
    );

  }

}

edited Feb 22, 2018 at 0:58

answered Feb 22, 2018 at 0:44

showdev

29.3k37 gold badges61 silver badges82 bronze badges

2 Comments

Kenneth Over a year ago

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!

Dikwan Over a year ago

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.

Collectives™ on Stack Overflow

Database not updating in html table

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related