Update Values of Entire Table

This code assumes you are connected to a MySQL database which has a table with Names and Emails. The idea is that it will output a table of every single value from that table, as text inputs. You can then alter the values of these inputs and re-submit, updating all the values in the database.

//get data from db
$sql = mysql_query("SELECT * FROM table");
$count=mysql_num_rows($sql);

//start a table
echo '<form name="form1" method="post" action="">
<table width="292" border="0" cellspacing="1" cellpadding="0">';

//start header of table
echo '<tr>
<th>&nbsp;</th>
<th>Name</th>
<th>Email</th>
</tr>';

//loop through all results
while($r=mysql_fetch_object($sql)){

//print out table contents and add id into an array and email into an array
echo '<tr>
<td><input type="hidden" name="id[]" value='.$r->id.' readonly></td>
<td>'.$r->name.'</td>
<td><input name="email[]" type="text" id="price" value="'.$r->email.'"></td>
</tr>';
}

//submit button
echo'<tr>
<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</form>';


// if form has been submitted, process it
if($_POST["Submit"])
{
       // get data from form
       $name = $_POST['name'];
       // loop through all array items
   foreach($_POST['id'] as $value)
       {
       // minus value by 1 since arrays start at 0
               $item = $value-1;
               //update table
       $sql1 = mysql_query("UPDATE table SET email='$email[$item]' WHERE id='$value'") or die(mysql_error());
   }

// redirect user
$_SESSION['success'] = 'Updated';
header("location:index.php");
}

Submitted values are not cleaned in this example, as it is assumed only an admin would have access to this type of powerful entry system.

Comments

  1. User Avatar
    Zoran
    Permalink to comment#

    This is cool, actually i have found your website before, but today i found out how many cool stuff you got. I recently did a project that required me to update rows that had different id’s but same value for the name.

    $ids = array(1, 2, 3, 4, 5, 6);
    $name = 'Some name';
    $sql = "UPDATE table SET name ='".$name."' WHERE id IN ('".implode(',', $ids)."')";

    I think mysql is being underestimated by web developers for some reason, cause they are using only small part of it, essential for most of the applications, but is powerful tool indeed, especially if you learn how to manage triggers, views and stored procedures.
    Thank you for your tips, they are very helpful, especially the CSS stuff, you are very good at it.

  2. User Avatar
    Lafaz
    Permalink to comment#

    Good tricks!

  3. User Avatar
    niroshan
    Permalink to comment#

    thanks for this tutorial. This was very helpful to design my web page…

Posting Code

You may write comments in Markdown. This makes code easy to post, as you can write inline code like `<div>this</div>` or multiline blocks of code in triple backtick fences (```) with double new lines before and after.

Code of Conduct

Absolutely anyone is welcome to submit a comment here. But not all comments will be posted. Think of it like writing a letter to the editor. All submitted comments will be read, but not all published. Published comments will be on-topic, helpful, and further the discussion or debate.

Want to tell us something privately?

Feel free to use our contact form. That's a great place to let us know about typos or anything off-topic.

Submit a Comment

icon-closeicon-emailicon-linkicon-menuicon-searchicon-tag