Php MySQL update html form

Question

There is a table:

|id name surname email    |
|1  john surjohn @mail.com|
|2  peter pet    @mail.com|                   
|.........................|

PHP:

<?php
if(isset($_POST['update']))
{
...
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());

}

$id = $_POST['id'];
$name = $_POST['name'];
$surname = $_POST['surname'];
$mail = $_POST['mail'];

$sql = "UPDATE emps ".
       "SET name= '$name', surname'$surname', email='$mail' ".
       "WHERE Id = $id" ;
mysql_select_db('dbase');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "blablablabla ! \n";
mysql_close($conn);
}
else

{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
    <fieldset style="width: 350px" >
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="100">Id </td>
<td><input name="id" type="text" id="id" value=""></td>
</tr>
<tr>
<td width="100">name</td>
<td><input type="text" maxlength="15" name="name" value="" ></td>
</tr>
<tr>
<td width="100">surname</td>
<td><input type="text" maxlength="40" name="surname" value="" ></td>
</tr>
<tr>

<td width="100"> </td>
<td>
<input name="update" type="submit" id="update" value="update">
</td>
</tr>
</table>
        </fieldset>
</form>
<?php
}
?>
   }

In this form i need update all fields otherwise, updated table can have null values. I want for example, update name field, but leave surname, email existing values. ideas?

Best way to do it is populate the input values with the values from the database, that way if someone changes the information it will update, if they don't it will just update with whatever was in the database anyway. — Karl, Feb 27 '13 at 12:43
What he's saying (I think) is that when he submits the form, if the person didn't complete the "surname" field it would update blank in the database, rather than keeping the content. — Karl, Feb 27 '13 at 12:45
He wants username available or not like check and some validations. I think?? — عثمان غني, Feb 27 '13 at 12:50
In this code, when i'm updating table, i must enter values in all form fields. if i don't do that, then not entered form field becomes NULL. i want update just one field for example:name, and leave existing values for surname and email. — Inoracle, Feb 27 '13 at 12:54
Try to avoid SQL injection by validating and sanitizing the user input. Futhermore, use mysqli or PDO instead of mysql for new code. — Mr. Radical, Feb 27 '13 at 12:57
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Feb 27 '13 at 13:01

Karl · Answer 1 · 2013-02-27T13:03:32.843

Could try something like this:

$id = $_POST['id'];
$name = $_POST['name'];
$surname = $_POST['surname'];
$mail = $_POST['mail'];

$sql = "UPDATE emps SET";
$moresql = '';

if(isset($name) && !empty($name)) {
    $moresql .= " name = '$name'";  
}

if(isset($surname) && !empty($surname)) {
    if ($moresql) $moresql .= ',';
    $moresql .= " surname = '$surname'";    
}

if(isset($mail) && !empty($mail)) {
    if ($moresql) $moresql .= ',';
    $moresql .= " mail = '$mail'";  
}

$sql .= $moresql;
$sql .= " WHERE Id = '$id'";

This is untested though.

score 0 · Answer 2 · answered Feb 27 '13 at 13:09

Karl's idea is the way to go, but it can be refactored this way:

$id = $_POST['id'];

$sql = "UPDATE emps SET";
$fieldValuePairs = array();

foreach($_POST as $key => value)
    if($key != 'id')
       $fieldValuePairs[] = "'$key' = '$value'";

$sql .= " ". implode(',', $fieldValuePairs)." WHERE id = $id";

Note: this works only if you use input names (in the form) equal the column names (in the database).

Have a great day.

Php MySQL update html form

2 Answers2