Updating existing records from HTML GET form

[elyob]

I'm just rewriting some backend management pages, in fact rewriting the
whole database too. One of these pages has a simple HTML form to edit
existing rows. I don't want to populate the form beforehand with all
existing data. I just want to check which of the 20 fields have changed and
update the data accordingly.

Before (I wrote the script years ago, forgive me!), I just had a check
whether any data in each field and each had its own update query .. i.e.

if ($email) {$res=mysql_query("update $table set email='$email' where
id='$id' ");}
if ($contact) {$res=mysql_query("update $table set contact='$contact'
where id='$id' ");}

Now, I must admit that this would still work, but there must be a much
tidier way of doing this ... any pointers appreciated ..

Nick

[elyob]

"elyob" <ne*********@gmail.com> wrote in message
news:DN****************@text.news.blueyonder.co.uk ...

I'm just rewriting some backend management pages, in fact rewriting the
whole database too. One of these pages has a simple HTML form to edit
existing rows. I don't want to populate the form beforehand with all
existing data. I just want to check which of the 20 fields have changed
and update the data accordingly.

Before (I wrote the script years ago, forgive me!), I just had a check
whether any data in each field and each had its own update query .. i.e.

if ($email) {$res=mysql_query("update $table set email='$email'
where id='$id' ");}
if ($contact) {$res=mysql_query("update $table set contact='$contact'
where id='$id' ");}

Now, I must admit that this would still work, but there must be a much
tidier way of doing this ... any pointers appreciated ..

Guess I should also mention I'm using PHP. I presume I should be taking the
information into an array, but not sure really .. as it's nearly 2am and I
should really be in bed.

[Bill Karwin]

elyob wrote:

if ($email) {$res=mysql_query("update $table set email='$email' where
id='$id' ");}
if ($contact) {$res=mysql_query("update $table set contact='$contact'
where id='$id' ");}

Now, I must admit that this would still work, but there must be a much
tidier way of doing this ... any pointers appreciated ..

Here's a trick...

Update is a no-op when you set a field to the same value it had before.
So you could skip the "if" logic, and just execute the update
regardless. Only execute one update statement, with all fields
corresponding to your web form parameters.

update $table set email = '$email', contact = '$contact', etc...
where id = '$id'

Regards,
Bill K.

[elyob]

Bill Karwin wrote:

elyob wrote:

if ($email) {$res=mysql_query("update $table set email='$email'
where id='$id' ");}
if ($contact) {$res=mysql_query("update $table set
contact='$contact' where id='$id' ");}

Now, I must admit that this would still work, but there must be a much
tidier way of doing this ... any pointers appreciated ..

Here's a trick...

Update is a no-op when you set a field to the same value it had before.
So you could skip the "if" logic, and just execute the update
regardless. Only execute one update statement, with all fields
corresponding to your web form parameters.

update $table set email = '$email', contact = '$contact', etc...
where id = '$id'

Regards,
Bill K.

Hi Bill,

That'd be the most straightforward way of doing it, although in original
post I mentioned I don't want to "populate the form beforehand with all
existing data". This is because the form is multi-functional, and does
all the inserts, updates and deletes from the databases. It's a simple
script and the only way I can think of getting around this is by using a
mysql enabled javascript call. i.e. enter the id, then choose <update>
which will populate the form.

Alternatively I was thinking of creating the mysql query on the fly,
however I can still see tons of IF statements. If this is the case, then
it'd be more straightforward to use my previous IF method as it is even
more straightforward.

Cheers

Nick

[Bill Karwin]

elyob wrote:

Alternatively I was thinking of creating the mysql query on the fly,
however I can still see tons of IF statements. If this is the case, then
it'd be more straightforward to use my previous IF method as it is even
more straightforward.

I have used this method too. One variation would be for each form
input, if it's non-blank, then push an associative array element. At
least that way it's easy to add another form field to your application.

# Create an associative array of all form inputs
# if they are present and have a non-blank value.
$form_field = array();
if ($email) { $form_field['email'] = $email; }
if ($contact) { $form_field['contact'] = $contact; }
....same for other fields...

The rest is boilerplate, even if the set of form fields changes.

# Convert the associative array into an array
# of key = 'value' strings.
$set_field = array();
foreach( $form_field as $key => $value )
{
$set_field[] = "$key = '$value'";
}

# Implode the list into a comma-separated string.
$csv_set_string = implode(',', $set_field);

# Execute the sql update, if there is anything to change.
if ($csv_set_string)
{
$res = mysql_query("update $table set $csv_set_string where id = '$id'");
}

Regards,
Bill K.

[elyob]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:d3********@enews1.newsguy.com...

elyob wrote:

Alternatively I was thinking of creating the mysql query on the fly,
however I can still see tons of IF statements. If this is the case, then
it'd be more straightforward to use my previous IF method as it is even
more straightforward.

I have used this method too. One variation would be for each form input,
if it's non-blank, then push an associative array element. At least that
way it's easy to add another form field to your application.

# Create an associative array of all form inputs
# if they are present and have a non-blank value.
$form_field = array();
if ($email) { $form_field['email'] = $email; }
if ($contact) { $form_field['contact'] = $contact; }
...same for other fields...

The rest is boilerplate, even if the set of form fields changes.

# Convert the associative array into an array
# of key = 'value' strings.
$set_field = array();
foreach( $form_field as $key => $value )
{
$set_field[] = "$key = '$value'";
}

# Implode the list into a comma-separated string.
$csv_set_string = implode(',', $set_field);

# Execute the sql update, if there is anything to change.
if ($csv_set_string)
{
$res = mysql_query("update $table set $csv_set_string where id =
'$id'");
}

Regards,
Bill K.

Regards Bill, but I had to get it sorted earlier. It really is a case of not
over compilating stuff isn't it!

if ($email) {$res=mysql_query("update $table set email='$email'
where id='$id' ");}
if ($contact) {$res=mysql_query("update $table set contact='$contact'
where id='$id' ");}

etc ...

The script is only a 10-100 times a day max manual script. KISS is sometimes
the best method! I did tidy it up for readability though ;)

Sometimes SQL is just purely simple! For 10,000+ calls a day I will rewrite
this stuff...!

Anyway, you are a very reliable member of this newsgroup, thanks Bill.

Regards

Nick