INSERT INTO multiple rows from a dynamic html table to MYsql using PHP

Question

The Scenario

I have a scoring card that is populated from another table in the MTSQL with an unknown amount of players. each week the players needs to be scored and those scores needs to be input into the mySQL.

The dynamic html table create for the Mysql table call member.

 <form method="post" action="scores-add" id="member-add-form"  enctype="multipart/form-data">
  <div class="table-responsive">
   <table id="data-table-1" class="table table-striped table-bordered">
    <thead>
     <tr>
      <th class="align-middle">Name</th>
      <th class="align-middle">Team</th>
      <th class="align-middle">Week</th>
      <th class="align-middle">Score 1</th>
      <th class="align-middle">Score 2</th>
      <th class="align-middle">Score 3</th>
      <th class="align-middle">Score 4</th>
      <th class="align-middle">Score 5</th>
      <th class="align-middle">Score 6</th>
      <th class="align-middle">Score 7</th>
      <th class="align-middle">Score 8</th>
     </tr>
    </thead>
    <tbody>
    <?php
     $sql = "SELECT * FROM member ORDER BY  member_name ASC";
     $result = mysqli_query($db,$sql) or die("Database access failed: " . mysqli_error());
     while ($team = mysqli_fetch_assoc($result)) {  
    ?>
    <tr >
     <td class="align-middle"><input type="text" name="scoring_1" value="<?php echo $team["member_name"]; ?>" readonly hidden>  <?php echo $team["member_name"]; ?></td>
     <td class="align-middle"><input type="text" name="scoring_2" value="<?php echo $team["member_team"]; ?>" readonly hidden>  <?php echo $team["member_team"]; ?></td>
     <td class="align-middle"><input type="text" name="scoring_3" value="" readonly hidden> </td>
     <td class="align-middle"><input type="checkbox" name="scoring_4" value="5"></td>
     <td class="align-middle"><input type="checkbox" name="scoring_5" value="10"></td>
     <td class="align-middle"><input type="checkbox" name="scoring_6" value="5"></td>
     <td class="align-middle"><input type="checkbox" name="scoring_7" value="5"></td>
     <td class="align-middle"><input type="checkbox" name="scoring_8" value="5"></td>
     <td class="align-middle"><input type="checkbox" name="scoring_9" value="5"></td>
     <td class="align-middle"><input type="checkbox" name="scoring_10" value="20"></td>
     <td class="align-middle"><input type="checkbox" name="scoring_11" value="50"></td>
    </tr>
        <?php
        }
        ?>
    </tbody>
   </table>
  </div>
   <div class="form-row mb-3">
    <button type="submit" class="btn btn-primary btn-block" name="score_add_btn">Add Score</button>
   </div>
 </form>

The function to add the table to the SQL

/ call the team_edit() function if team_edit_btn is clicked
if (isset($_POST['score_add_btn'])) {
    score_add();
}

// LOGIN USER
function score_add(){
    global $db, $errors, $member_id, $member_name_edit, $member_team_edit, $members_view_url;
    // grap form values
    $scoring_1 = $_POST['scoring_1'];
    $scoring_2 = $_POST['scoring_2'];
    $scoring_3 = $_POST['scoring_3'];
    $scoring_4 = $_POST['scoring_4'];
    $scoring_5 = $_POST['scoring_5'];
    $scoring_6 = $_POST['scoring_6'];
    $scoring_7 = $_POST['scoring_7'];
    $scoring_8 = $_POST['scoring_8'];
    $scoring_9 = $_POST['scoring_9'];
    $scoring_10 = $_POST['scoring_10'];
    $scoring_11 = $_POST['scoring_11'];
    // make sure form is filled properly
    if (empty($scoring_1)) {
        array_push($errors, "Name is required");
    }
    if (count($errors) == 0)    {
        $query = "INSERT INTO scoring (scoring_1, scoring_2, scoring_3, scoring_4, scoring_5, scoring_6, scoring_7, scoring_8, scoring_9, scoring_10, scoring_11 )
        VALUES ('$scoring_1', '$scoring_2', '$scoring_3', '$scoring_4', '$scoring_5', '$scoring_6', '$scoring_7', '$scoring_8', '$scoring_9', '$scoring_10', '$scoring_11')";
        mysqli_query($db, $query);
        $_SESSION['success']  = "<h4>Done</h4>";
        header("location: $leaderboard_menmber_url"); 
    }else {
        $_SESSION['failure']  = "Error updating record: " . $db->error;
        header("location: $leaderboard_menmber_url");
    }
    $db->close();

}

The Problem

The function only adds the last line from the dynamic html table to the database.

The desired outcome.

Each entry in the dynamic HTML table needs to be added to the MySQL as its own entry.

You have an error. [`mysqli_error()`](https://www.php.net/manual/en/mysqli.error.php) needs one argument. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Aug 05 '20 at 22:38
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 05 '20 at 22:38

score 0 · Answer 1 · answered Aug 05 '20 at 08:08

<input type="text" name="scoring_1" value="<?php echo $team["member_name"]; ?>

here you set your variable name to scoring_1 but the next row of table will override that (Instead the browser may send the data, but php will create a variable scoring_1 and assign one of the given values to them. To solve this problem set the name to scoring_1[] <input type="text" name="scoring_1[]" value="<?php echo $team["member_name"]; ?>

This say php to make an array and store the values there. Futher you can inprove your code when you use something like the team id as row index and used a fixed number as column in a 2 dimensional array

<input type="text" name="scoring[<?php echo $team["id"];>][1]" value="<?php echo $team["member_name"]; ?>

Thank you for your assistance. but still only one entry is added to the SQL not the entire tables of content and is not added as a array — MFWebMaster, Aug 05 '20 at 09:25

INSERT INTO multiple rows from a dynamic html table to MYsql using PHP

1 Answers1