Updating multiple rows the same table in a webform populated using PHP and SQL

Question

My aim is to create a dynamic webpage that returns all of the records in a table meeting a given criteria and uses them to populate a webform. The user can then make changes as they wish and update the entire table with a single button press.

In the example below I'd like to list all the events, the start time will appear in an editable text box and when I press submit it should update all the values.

I've created a mock-up below:

$query = "SELECT * FROM Events";
$result = mysqli_query( $dbc, $query ) ;  

if ( mysqli_num_rows( $result ) > 0 )
    {
     echo '<form action="update_events.php" method="post">>';
     echo '<table><tr>';

     while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ))
     {

        echo '<td>' . $row['Event_Name'] .' </td>'.
             '<td>' . $row['Event_Date'] .'</td>'.
             '<td><input name="'. $row['Event_ID'] .'" type="text" value="'$row['Event_Start_Time'] .'"></td>';
        echo '</tr>';
     }
     echo '</table>';
     echo '  <input type="submit" value="Submit"></form>';

     mysqli_close( $dbc ) ;
}
else
{
    echo '<p>There are currently no events.</p>' ;
}

I cannot figure our how to get the processing on the update_events.php to work, any help would be appreciated.

foreach(????){
   $sql = "UPDATE Events SET Event_Start='$Event_Start' WHERE id='$Event_ID'";
   mysqli_query($dbc, $sql)
}

a simple start would be fetch all records again in update.php and then iterate them in loop and you can get required value from $_POST['$id_from_db'] like pattern — RohitS
– RohitS, Commented Dec 2, 2017 at 17:19

miknik · Accepted Answer · 2017-12-02 19:45:31Z

1

You really want to keep your POST variables static, it makes life easier. Don't try and use one variable to store two values, give them one each.

As you are submitting multiple values to the server which you want to loop over then it makes sense to submit them in arrays, so something like this:

$i=0;
while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ))
{
    echo '<td>' . $row['Event_Name'] .' </td>'. 
        '<td>' . $row['Event_Date'] .'</td>'.   
        '<td><input name="event['.$i.'][start]" type="text" value="'$row['Event_Start_Time'] .'"><input name="event['.$i.'][ID]" type="hidden" value="'. $row['Event_ID'] .'"></td>';
    echo '</tr>';
    $i++;
}

Now your form will submit all the events as a multidimensional array which you can retrieve in $_POST['event'] and loop over it to do your database updates like this:

$stmt = $this->mysqli->prepare("UPDATE Events SET Event_Start=? WHERE id=?");
$stmt->bind_param('ss', $start, $id);
foreach ($_POST['event'] as $event) {
    $start = $event['start'];
    $id = $event['ID'];
    $stmt->execute();
}

This code uses a prepared statement for the database insert, the method you are using is insecure and leaves you vulnerable to SQL injection. You should read up on Mysqli prepared statements and start using them.

answered Dec 2, 2017 at 19:45

miknik

5,9571 gold badge14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2956578 Over a year ago

Thank you for this, I'll start reading up on prepared statements.

Collectives™ on Stack Overflow

Updating multiple rows the same table in a webform populated using PHP and SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related