How To Get The Sql Insert Statement To Recognise Which Text Fields Have Been Changed.

[andrewmoir]

Hello there,

You may have seen it before: when in PhpMyAdmin, when editing a row in your database, a page is displayed with all the database fields and the all the current values for each field. If you change one of the values in a row (e.g. 'status' field) and press the 'GO' (submit) button, the submit page is displayed with the following SQL statement:

e.g.
UPDATE `sample` SET `status` = '2' WHERE `sampno` = '153' LIMIT 1 ;

However there were lots of other fields which I did not change, these were not included in the submit query.

How does the script know which fields were changed (i.e. status field) , how does it manage to generate a SQL statement tailored to include only those fields which have changed?


(-I know how to construct basic php submit forms-if its too much effort to type giving me a basic idea will suffice.)

Please ask me to clarify if something is not clear.

regards

Andrew

[nogray]

You can do this in different ways, one is to set 2 arrays, one for the new values and one for the old values. Then you loop around them and if the values are different, you just change add the new value to the query.

for example, you html form can look like this
[code]
<form method="post" action="php_file.php">
<input type="hidden" name="id" value="1" />
<input type="text" name="new[field_name]" />
<input type="text" name="new[another_field]" />
<input type="submit" />
</form>
[/code]

and your php file
[code]
<?PHP
// connect to db here

$result = mysql_query("select * from `Table` where `id` = '".$_POST['id']."' limit 1") or die("couldn't select " . mysql_error());

$old = mysql_fetch_array($result);

$new = $_POST['new'];

foreach($new as $key=>$val){
     if ($old[$key] != $val){
        $query .= "`$key` = '".mysql_real_escape_string($val)."', ";
    }
}

$query = "update `Table` set ".trim($query, ", ")." where `id` = '".$_POST['id']."'";

$result = mysql_query($query);
?>
[/code]

I haven't tested this, just typed it right now, so you may have to adjust it a bit.