PHP and mysql update statement

There's four options (ok, maybe more ;-) )

1. Fill all empty variable with the current values of the database record. That way, fields that are not changed, effectivly update the field to the same value
2. Like above, but put the current values in the form. You can then choose to modify the values or explicitly clear them
3. Build a specific query, updating only the fields you want to change. Only the fields included in the SET clause of your statement are updated.
4. Use a trigger to prevent a value being updated to null. Make it use the current value. This seems like the solution in 1 and 2, but has the advantage that you do not overwrite values of others when more than one is editing the same record.

I would choose 2 or 3.

• 2 because it allows you to edit or clear the fields.
• 3 because it makes you update only the relevant fields. You can easily detect if any change is made at all and skip the update alltogether if there's not.

1. $id is undefined. Add a line like $id =$_POST ['id']; to solve this.
2. At the end of the UPDATE query, add LIMIT 1 to ensure only one row is updated
3. Extra credit: Sanitize/escape the POST data to prevent SQL injection attacks. :p

You could dynamically create the SQL query by looping through the fields and only including references to fields that require changing.

NOTE: I haven't tested this and you should include data cleansing to prevent SQL injection. You would also need to handle the possibility that no fields have been updated.

<?php

include("misc.inc"); 
$id = $_GET['id']; 
$db = 0; 
$db = mysql_connect($db_host . ":" . $db_port, $db_user, $db_pwd); 
if ($db == 0) {die("Cannot connect, Error <b>" . mysql_errno() . "</b>: " . mysql_error());}; 
echo "Database connection has been established successfully<br />\n"; 
mysql_query("USE " . $db_db); 
if (mysql_errno() != 0) {die("Cannot USE database, Error <b>" . mysql_errno() . "</b>: " . mysql_error());}; 
echo "Database has been selected successfully<br />\n"; 

if (isset($_POST['submit'])): 

$fields = array('itemName','item_Description', 'price', 'contactName', 'phone', 'email', 'website');

$updates = '';

foreach ($fields as $key) {
    if (isset($_POST['$key'])) {
              $value = $_POST['$key'];
        if ($updates != '') {
            $updates = $key . " = \'" . $value . "\'";
        } else {
            $updates = $updates . ", " . $key . " = \'" . $value . "\'";
        }
    }
}

if ($updates != '') {

    $sql = "UPDATE items SET $updates WHERE items.itemID='$id'";

    if (@mysql_query($sql)) { 
        echo('<p>Item editted successfully!</p>'); 
    } else { 
        echo('<p>Error editting item!<br />Error <b>' . mysql_errno() . '</b>'. mysql_error() . '</p>'); 
    }
}

?>
<p><a href="<? $_SERVER['PHP_SELF']?>" method="POST"></a></p> 
<p><a href="items.php">Return to Front Page</a></p> 
</ul> 

I tried this code but It didn't work, i'm sure it was something simple that i needed to changed. instead i used this startement: $product = mysql_fetch_object($result); and I put the variable in the forms so it displays the old records when the user want to update the rows. something like this <b>Phone Number: </b><input type="text" name="phone" id="phone"value="<?php echo $product->phone; ?>" size="20" maxlength="255"/><br /><br /> Thanks for your help