Updating multiple records from one form

[Ojoshiro]

Hello me, it's me again,

I have hung around on this board for a while now and I have seen some problems pop up over and over again. I'd like to try and tackle one here: the problem of updating many records (in a database in this example) from one HTML form.

Imagine we have this table in the database:

Code: [Select]

Table: User
Id
Username
Password
First_name
Last_name
Birthdate
Email
Subscr_date
 
Imagine you're writing something for the administrator so that they can reset all passwords from one form.
( you need a reason, right? )
For every User-record in the database we want a line with the username, first_name/last_name, subscription date and a 'Reset password' checkbox.

PHP Code: [Select]


$dbh = mysql_connect( $host, $user, $pass ); // Connect to a database
if ( mysql_error() )
{   echo "Cannot connect to database on [$host] as [$user] : ".mysql_error(); 
    exit;
}
mysql_select_db( $database ); // Select the correct database

$query = "select id, username, first_name, last_name, subscr_date from user";

$result = mysql_query( $query );
if ( mysql_error() )
{   echo "Cannot run query [$query] : ".mysql_error(); 
    exit;
}
// Create the form
echo '<form name="send_new_pass" action="sendnewpass.php" method="post">';
echo '<table>';
echo '<tr><td>Reset</td><td>Username</td><td>Name</td><td>Join date</td></tr>';
while ( $row = mysql_fetch_assoc( $result ) )
{   echo '<tr>';
    echo '<td><input type="hidden" name="id[]" value="'.$row['id'].'"/>'.
         '<input type="check" name=".$row['id']. '_reset" value="on"/></td>';
    echo '<td>'. $row['username']. '</td>';
    echo '<td>'. $row['first_name']. ' '. $row['last_name']. '</td>';
    echo '<td>'. $row['subscr_date']. '</td>';
    echo '</tr>';
}
echo '</table>';
echo '<input type="submit" name="submit" value="Engage"/>';
echo '</form>';

As you can see the 'id's are passed as an array.

That done you need to write the PHP script that is going to process this form. We named it 'sendnewpass.php'.

PHP Code: [Select]


$id_array = $_POST['id'];     // First get all the id's
foreach( $id_array as $id )   // Loop through them
{   if ( array_key_exists( $id.'_reset', $_POST ) && $_POST[$id.'_reset'] == 'on' )
    {   resetPasswordAndSendMail( $id );   // This is a function that resets the password and sends an email
    }                                      // to the user.
}
...

function resetPasswordAndSendMail( $id )
{   $password = "CatchATurian";
    $query = "update User set password='$password' where id='$id'";
    $result = mysql_query( $query );
    if ( mysql_error() )
    {   echo "Cannot run query [$query] : ".mysql_error(); 
        exit;
    }
    // and here some code that digs up the emailadres and sends a notification with the new password.
    // ( I leave that to you )
}

In this example I only access the checkbox via the id, but I hope you see how you can keep lots of form-fields together this way.
I hope this is going to help someone. If you need more explanation, contact me.

Good luck!
O.