Best way to avoid overwriting mysql data with dynamically POSTED php data?

[galvin]

This could be PHP or MySql so putting it in PHP forum for now...

I have code below (last code listed) which processes a dynamically created Form which could have anywhere from 0 to 6 fields.  So I clean all fields whether they were posted or not and then I update the mySQL table.

 

The problem with this code below is that if, say, $cextra was not posted (i.e. it wasnt on the dynamically created form), then this code would enter a blank into the table for $cextra (i.e. if there was already a value in the table for $cextra, it gets overwritten, which is bad).

 

What is the best way to handle this?  I'm thinking i have to break my SQL query into a bunch of if/else statements like this...

 

$sql = "UPDATE cluesanswers SET ";
if (isset($_POST['ctext'])){
echo "ctext='$ctext',";
} else {
//do nothing
}
and so on 5 more times....

 

That seems horribly hackish/inefficient.  Is there a better way?

 

if (isset($_POST['hidden']) && $_POST['hidden'] == "edit") {

$cimage=trim(mysql_prep($_POST['cimage']));
$ctext=trim(mysql_prep($_POST['ctext']));
$cextra=trim(mysql_prep($_POST['cextra']));
$atext=trim(mysql_prep($_POST['atext']));
$aextra=trim(mysql_prep($_POST['aextra']));
$aimage=trim(mysql_prep($_POST['aimage']));


//update the answer edits
$sql = "UPDATE cluesanswers SET ctext='$ctext', cextra='$cextra', cimage='$cimage', atext='$atext', aextra='$aextra', aimage='$aimage'";
		$result = mysql_query($sql, $connection);
		if (!$result) { 
		die("Database query failed: " . mysql_error());
		} else {
		}

 

[Psycho]

Use a single set of code along with an array of all the field names. If you add/remove fields later, you only need to modify the array.

if (isset($_POST['hidden']) && $_POST['hidden'] == "edit")
{
    //List of input/db fields
    $fieldList = array('cimage', 'ctext', 'cextra', 'atext', 'aextra', 'aimage');

    //Create SET statements for query
    $setParts = array();
    foreach($fieldList as $fieldName)
    {
        if(isset($_POST[$fieldName)]))
        {
            $value = mysql_prep(trim($_POST[$fieldName)]));
            $setParts[] = "`{$fieldName)}` = '{$value}'";
        }
    }

    //If at least one field was passed generate and run query
    if(count($setParts)>0)
    {
        $sql = "UPDATE cluesanswers SET " . implode(', ', $setParts);
        $result = mysql_query($sql, $connection);
        if (!$result)
        {
            die("Database query failed: " . mysql_error());
        }
        echo "Record updated.";
    }
    else
    {
        echo "No fields were passed.";
    }
}

[galvin]

thanks mjdamato! makes perfect sense and works great as well!  I really gotta start using arrays and foreach more, as they seem to big time savers code-writing wise.

thanks again!