Using a PHP generated webform and mySQL to update multiple rows in a table

Question

I have created a database to store results in a competition along witha php generated web-page that lists all the competitors followed by a textbox in which to record their score.

When I press the submit button I want it to update all of the rows in the table.

I'm new to php and mySQL, I can do it for one of them at a time but I don't want them to have to press 30+ individual submit buttons to handle each row.

The following code generates the form:

  <?php

   $field_event_id = $sports_day_id = "";
   if ( $_SERVER[ 'REQUEST_METHOD' ] == 'POST' )


    $field_event_id = $_POST["field_event"];
    $sports_day_id = $_POST["sportsday"];

    $query = "SELECT * FROM student JOIN participants ON student.student_ID = participants.student_ID WHERE `Field_Event_ID`= $field_event_id";


    $result = mysqli_query( $dbc, $query ) ; 


     echo '<body>';
if ( mysqli_num_rows( $result ) > 0 )
{
    echo'<header> <h1> Please enter the results below </h1> </header>';
      echo '<table align="center" border="1" cellpadding="10px" bgcolor="#DCF8FE">
        <tr>
            <th>Forename</th>
            <th>Surname</th>
            <th>Year Group</th>
            <th>Score</th>
        </tr>

<tr>';
     while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ))
    {
        echo '<form action="Add_Scores_Field_Event_Register.php" method="post">
            <td> <strong>' . $row['Forename'] .'</strong><br> </td>
            <td> <strong>' . $row['Surname'] .'</strong><br> </td>
            <td> <strong>' . $row['Year_Group'] .'</strong><br> </td>
            <td> <strong>   <input type="text" name="notes_input"> </strong><br> </td>           
            </tr>
            </form>';
     }
     echo '</table>';
         echo '<p><input type="submit" value="Enter scores" ></p>';

}
else 
{ 
    echo '<p>There are currently no students in this event.</p>' ; 
}

{

     # Close database connection.
     mysqli_close( $dbc ) ; 
}


# close body section.
echo '</body>';
# close HTML section.
echo '</html>';

mysqli_close($dbc);

?>

_"I'm new to php and mySQL"_ Excellent, now is the time to avoid learning bad habits. Your first task, avoid [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks by using prepared statements with bound parameters, via either the [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) driver. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Feb 23 '18 at 15:14

Tyler · Accepted Answer · 2018-02-26T16:00:42.873

According to this post you can set the name attribute of elements to an array.

<input type="text" name="inputs[]" value="bob">
<input type="text" name="inputs[]" value="jim">

If you submit this through a form. You should be able to access the values like:

$_POST["inputs"][0]; // bob
$_POST["inputs"][1]; // jim

So, if you output your information inside a form and setup the name attributes for the scores properly. When you submit the form, you should be able to grab all the scores (editted or not) and update your database with the values. You will have to keep track of which record in the database to change.

The code I provided is untested; however, according to the linked post, it should work.

Don't forget to learn about prepared statements as Alex Howansky had commented. These are really important.

If you try this out, you would need to move your form tags to wrap the entire table; instead of, particular rows.

EDIT: With a little more though I see 2 solutions:

Place the student ID inside a hidden tag and use the name attribute to send it along with the other data.

<input type="hidden" name="studentIds[]" value="<place student id here>">

I would place the input tag in the td for your forename; because, it is the first td and one of the tds that uniquely represents the row.

OR

Use the Student ID as the index for your index and the score as the value.

<input type="text" name="notes_input[<place student id here>]">

You can then iterate through notes_input and get the key and value:

foreach ($array as $key => $value) {
    //code
}

See documentation for more information.

Thanks for the idea and I have moved the form, I'm getting closer but need a tiny bit more help. How can I pass the student_ID along with their scores? I have sucessfully passed the array of scores. (Code below) — user9402199, Feb 26 '18 at 15:13
while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC )) { echo ' ' . $row['Student_ID'] .'
' . $row['Forename'] .'
' . $row['Surname'] .'
' . $row['Year_Group'] .'

'; } — user9402199, Feb 26 '18 at 15:17

Using a PHP generated webform and mySQL to update multiple rows in a table

1 Answers1