Update using foreach()

russmichell

Hi there:

I want to be able to perform a MySQL table update using values from a dynamically generated form, using the GET method.
As the form is dynamic, the field names and the resultant query are and cannot be hard-coded.

I believe I'd need to use some sort of loop, and have indeed used such a method to perform looped inserts thus:

foreach($HTTP_POST_VARS as $key=>$val) {
$sql2 .= $key . ",";
$sql3 = substr($sql2,0,-1) . ")";
$sql4 = " VALUES ('" . $DBusername . "',";
$sql5 .= "'" . $val . "'" . ",";
$sql6 = substr($sql5,0,-1) . ")";
}//----end foreach
$sql = $sql1 . $sql3 . $sql4 . $sql6;

However updates as you know are slightly different in that the value follows immediately after the fieldname declaration in the SQL query thus:
"UPDATE <table> SET <fieldnames> = '<form_values>' WHERE X=Y";

Can anyone suggest a way of employing, such loop to construct the requred UPDATE query??

Many thanks.
Russ

herve

Using two arrays : one for the fields names
and the other for their respective values

$i=0;
foreach ($HTTP_POST_VARS as $key=>$val)
{
$fieldname [$i] = $key;
$values [$i] = $value

 ...

 $i++;

}

for ($n=0;$n<$i;$n++)
{
...
"UPDATE <table> set ".$fieldname[$n]."=
".values[$n]." WHERE ...."

}
}

russmichell

Herve:

Thank you very much! I modifed your asolution slightly, and this is what I now have which works great:

$sql1 = "UPDATE $table SET ";
$i=0;
foreach($HTTP_GET_VARS as $key=>$val) {
$fieldname[$i] = $key;
$values[$i] = $val;
$sql2 .= $fieldname[$i] . "=" . "'" . $values[$i] . "'" . ",";
$sql3 = substr($sql2,0,-1);
$i++;
}//----end foreach
$sql = $sql1 . $sql3 . " WHERE field='$field'";

Just one thing though: I have noticed that some people like yourself don't increment the counter in your loops until the end, what difference does it make whether it's at the start or beginning??

Thanks again Herve!
Russ

herve

Well, I suppose it's matter of habit
In most cases, it doesn't really matter.

Usually I type the "$i++" right after
the start (for() or do() or foreach() )
of the loop, then I insert the rest
of the code inside, so the incrementation
is pushed back to the end of the loop.

This way I don't forget the incrementation.

I can also explain that with how the CPU
deal with its own registers, and how
it test for the end of the loop, and may be putting the incrementation at the end help a
bit on a performance point of view, but with today 2 Ghz processors ...
I'm not really sure it matters a lot ;-) !