Updating multiple users data with different values at the same time using PHP and MySQL

Question

Hi I'm extremely new to php programming and i'm trying to create a table which shows multiple users data, but will also allow the data within the table to be edited. Then once any changes to the data has been made I want the user to able to click an 'update' button and the new updated changes will be stored in the database. I came up with this:

echo "<form action=UpdateInfo.php method=post>
  <p>Edit the table below and click the update button in order to update the information.</p>";
  echo "<div id = 'table_heading'>
    <table class = 'heading'>
      <tr>
        <th class = 'odd'> Title </th>
        <th> UniID </th>
        <th class = 'odd'> First Name </th>
        <th> Surname </th>
        <th class = 'odd'> House Number </th>
        <th> Address Line One </th>
        <th class = 'address_line_two'> Address Line Two </th>
        <th> City </th>
        <th class = 'odd'> Post Code </th>
        <th> County </th>
        <th class = 'odd'> Phone Number </th>
        <th> Email </th>
        <th class = 'odd'> Username </th>
        <th class = 'far_right'> Password </th>
      </tr>
      ";
      while ($return = mysql_fetch_assoc($result))    {
        $phonenumber = "$return[PhoneNumber]";
        $number = str_pad($phonenumber, 11, "0", STR_PAD_LEFT);
        echo "
        <tr class='data'>
          <td class = 'title'><input class = 'title' type='text' name='title' value= '".$return['Title']."' /></td>
          <td class = 'uniid'><input class = 'uniid' type='text' name='uniid' value= '".$return['UniID']."' /></td>
          <td class = 'first_name'><input class = 'first_name' type='text' name='firstname' value= '".$return['FirstName']."' /></td>
          <td class = 'surname'><input class = 'surname' type='text' name='surname' value= '".$return['Surname']."' /></td>
          <td class = 'house_number'><input class = 'house_number' type='text' name='housenumber' value= '".$return['HouseNumber']."' /></td>
          <td class = 'address_line_one'><input class = 'address_line_one' type='text' name='addresslineone' value= '".$return['AddressLineOne']."' /></td>
          <td class = 'address_line_two'><input class = 'address_line_two' type='text' name='addresslinetwo' value= '".$return['AddressLineTwo']."' /></td>
          <td class = 'city'><input class = 'city' type='text' name='city' value= '".$return['City']."' /></td>
          <td class = 'postcode'><input class = 'postcode' type='text' name='postcode' value= '".$return['PostCode']."' /></td>
          <td class = 'county'><input class = 'county' type='text' name='county' value= '".$return['County']."' /></td>
          <td class = 'phonenumber'><input class = 'phonenumber' type='text' name='phonenumber' value= '" .$number. "' /></td>
          <td class = 'email'><input class = 'email' type='text' name='email' value= '".$return['Email']."' /></td>
          <td class = 'username'><input class = 'username' type='text' name='username' value= '".$return['Username']."' /></td>
          <td class = 'password'><input class = 'password' type='text' name='password' value= '".$return['Password']."' /></td>
        </tr>";    
      }
      echo "</table>
      <input class='submit' type='submit' value='Update Info' />
    </form>
  </div>";

This creates the table as I had hoped and allows the data within to be edited. However when the update button is clicked and the user is taken to the 'updateinfo.php' page the following query is performed:

$sql = "UPDATE completeinfo SET Title='".$_POST['title']."',
FirstName='".$_POST['firstname']."',
Surname='".$_POST['surname']."',
AddressLineOne='".$_POST['addressone']."',
AddressLineTwo='".$_POST['addresstwo']."',
County='".$_POST['county']."',
City='".$_POST['city']."',
PostCode='".$_POST['postcode']."',
Email='".$_POST['email']."',
Username='".$_POST['username']."',
Password='".$_POST['password']."',
UserType='".$_POST['usertype']."'";

This query here updates all users in the database to have the same values as the last user to be printed out in the loop, rather than just applying the specific changes I make to each users data. I know why this is happening but I have no idea how I can achieve what am I after. Does anyone know how I can possibly have a table which holds multiple users information but each users data can be edited and updated on an individual bases rather than having all users given the same values. Sorry for any lack of clarity and thanks in advance for any help.

Answer 1

The problem is you need to have a WHERE clause in the UPDATE statement. Without a where clause, the update statement always updates all the rows. Similar to SELECT selecting all rows if you do not use a WHERE clause.

If usernames are unique, you can do the update like this:

<input type='hidden' name='username_orig' value= '".$return['username']."' />

and the sql like this:

$sql="UPDATE completeinfo SET 
Title='".$_POST['title']."', 
FirstName='".$_POST['firstname']."',
Surname='".$_POST['surname']."', 
AddressLineOne='".$_POST['addressone']."',
AddressLineTwo='".$_POST['addresstwo']."', 
County='".$_POST['county']."', 
City='".$_POST['city']."',
PostCode='".$_POST['postcode']."', 
Email='".$_POST['email']."', 
Username='".$_POST['username']."',
Password='".$_POST['password']."', 
UserType='".$_POST['usertype']."'"
. "WHERE username = " $_POST['username_orig'];

username_orig would be a hidden field in your form, value set to username before the update. Or, you can not include username as an updatable field, but make it a hidden field in the form.

Do the records in the table have keys, beside the username? After trying a lot of approaches, I can recommend always having a primary key field in every table set to autoincrement. That way, it always is an integer and reliably available to identify the record to be updated. Again, you would have the record key in the form as a hidden field, like this:

<input type='hidden' name='recordKey' value= '".$return['recordKey']."' />

And the SQL like this:
$sql="UPDATE completeinfo SET 
Title='".$_POST['title']."', 
FirstName='".$_POST['firstname']."',
Surname='".$_POST['surname']."', 
AddressLineOne='".$_POST['addressone']."',
AddressLineTwo='".$_POST['addresstwo']."', 
County='".$_POST['county']."', 
City='".$_POST['city']."',
PostCode='".$_POST['postcode']."', 
Email='".$_POST['email']."', 
Username='".$_POST['username']."',
Password='".$_POST['password']."', 
UserType='".$_POST['usertype']."'"
. "WHERE recordKey = " $_POST['recordKey'];

fyi, the form tag needs quotation marks around post and action, like this

<form action='updateInfo.php' method='post'>

Hope that helps.

Answer 2

This assumes that UniID is a unique identifier (primary key). If it isn't then just replace UniID with whatever the primary key is for that table.

    echo "
    <tr class='data'>
      <td class = 'title'><input class = 'title' type='text' name='title[".$return['UniID']."]' value= '".$return['Title']."' /></td>
      <td class = 'uniid'><input class = 'uniid' type='text' name='uniid[".$return['UniID']."]' value= '".$return['UniID']."' /></td>
      <td class = 'first_name'><input class = 'first_name' type='text' name='firstname[".$return['UniID']."]' value= '".$return['FirstName']."' /></td>
      <td class = 'surname'><input class = 'surname' type='text' name='surname[".$return['UniID']."]' value= '".$return['Surname']."' /></td>
      <td class = 'house_number'><input class = 'house_number' type='text' name='housenumber[".$return['UniID']."]' value= '".$return['HouseNumber']."' /></td>
      <td class = 'address_line_one'><input class = 'address_line_one' type='text' name='addresslineone[".$return['UniID']."]' value= '".$return['AddressLineOne']."' /></td>
      <td class = 'address_line_two'><input class = 'address_line_two' type='text' name='addresslinetwo[".$return['UniID']."]' value= '".$return['AddressLineTwo']."' /></td>
      <td class = 'city'><input class = 'city' type='text' name='city[".$return['UniID']."]' value= '".$return['City']."' /></td>
      <td class = 'postcode'><input class = 'postcode' type='text' name='postcode[".$return['UniID']."]' value= '".$return['PostCode']."' /></td>
      <td class = 'county'><input class = 'county' type='text' name='county[".$return['UniID']."]' value= '".$return['County']."' /></td>
      <td class = 'phonenumber'><input class = 'phonenumber' type='text' name='phonenumber[".$return['UniID']."]' value= '" .$number. "' /></td>
      <td class = 'email'><input class = 'email' type='text' name='email[".$return['UniID']."]' value= '".$return['Email']."' /></td>
      <td class = 'username'><input class = 'username' type='text' name='username[".$return['UniID']."]' value= '".$return['Username']."' /></td>
      <td class = 'password'><input class = 'password' type='text' name='password[".$return['UniID']."]' value= '".$return['Password']."' /></td>
    </tr>";

<?php

    foreach($_POST['uniid'] as $userID)
    {
        $sql = "UPDATE completeinfo
        SET Title='".$_POST['title'][$userID]."',
            FirstName='".$_POST['firstname'][$userID]."',
            Surname='".$_POST['surname'][$userID]."',
            AddressLineOne='".$_POST['addressone'][$userID]."',
            AddressLineTwo='".$_POST['addresstwo'][$userID]."',
            County='".$_POST['county'][$userID]."',
            City='".$_POST['city'][$userID]."',
            PostCode='".$_POST['postcode'][$userID]."',
            Email='".$_POST['email'][$userID]."',
            Username='".$_POST['username'][$userID]."',
            Password='".$_POST['password'][$userID]."',
            UserType='".$_POST['usertype'][$userID]."'
        WHERE UniID = '".$_POST['uniid'][$userID]."'";

        // Execute your $sql here
    }
?>

Now, that being said, it is absolutely terrible practice to inject form data into SQL statements like this. You should cleanse/sanitize/escape the values first, and it would also be good to parameterize them using prepared statements.

Answer 3

You need a WHERE clause in your MySQL query.

Here's a good tutorial:

http://www.w3schools.com/php/php_mysql_where.asp

Answer 4

I'm not sure if this is want you want but I think you need to use a unique field from your table to specify the record you want to update. For that you need to modify your update query to include a where clause. For eg. say we assume that Username is a unique field, your update query will be something like this:

$sql="UPDATE completeinfo SET Title='".$_POST['title']."', FirstName='".$_POST['firstname']."',
    Surname='".$_POST['surname']."', AddressLineOne='".$_POST['addressone']."',
    AddressLineTwo='".$_POST['addresstwo']."', County='".$_POST['county']."', City='".$_POST['city']."',
     PostCode='".$_POST['postcode']."', Email='".$_POST['email']."', Username='".$_POST['username']."',
    Password='".$_POST['password']."', UserType='".$_POST['usertype']."' WHERE Username='".$_POST['username']."'";