RE: [PHP-DB] RE: [PHP] How to do a dynamic UPDATE SET

Try this: <?php if ($submit) { // if there is an ID, then it's an update if ($id) { $comma = ""; for ($i = 1; $i < $columns; $i++) { $fld = mysql_field_name($fields, $i); $val = $$fld; $set .= $comma."$fld='".$val."'"; $comma = ", "; } $set .= " "; echo $set, "<p>"; // run SQL against the DB $sql = "UPDATE events SET $set WHERE id=$id"; echo $sql, "<p>"; $result = mysql_query($sql); } echo "<span class=adminnormal>Record updated"; } ?> -- David Balatero, db@reactiongaming.com -----Original Message----- From: René Fournier [mailto:rene.fournier@markada.com] Sent: Monday, October 01, 2001 4:05 PM To: php-general@lists.php.net; php-db@lists.php.net Subject: [PHP-DB] RE: [PHP] How to do a dynamic UPDATE SET Thanks for the suggestions--I tried yours and Vincent's, but I'm still getting the same problem. Here's the code I'm using: ---------------------------------------------------------------------------- -------------------- if ($submit) { // if there is an ID, then it's an update if ($id) { $comma = ""; for ($i = 1; $i < $columns; $i++) { $fld = mysql_field_name($fields, $i); $set .= $comma."$fld='$".$fld."'"; $comma = ", "; } $set .= " "; echo $set, "<p>"; // run SQL against the DB $sql = "UPDATE events SET $set WHERE id=$id"; echo $sql, "<p>"; $result = mysql_query($sql); } echo "<span class=adminnormal>Record updated"; } ---------------------------------------------------------------------------- -------------------- And here's the echo'ed values for $sql and $set: ---------------------------------------------------------------------------- -------------------- lang='$lang', record='$record', date='$date', what='$what', link='$link', location='$location', details='$details' UPDATE events SET lang='$lang', record='$record', date='$date', what='$what', link='$link', location='$location', details='$details' WHERE id=1 ---------------------------------------------------------------------------- -------------------- The result: PHP updates the values of lang, record, date, what (etc.) to [LITERALLY] $lang, $record, $date, etc--NOT the values of those fields submitted by the form. I'm kinda lost as to what to do... Any suggestions?? ...Rene --- Rene Fournier renefournier@yahoo.com > -----Original Message----- > From: Joe Kaiping [mailto:kaiping@phreedom.com] > Sent: Monday, October 01, 2001 4:05 PM > To: 'René Fournier'; php-general@lists.php.net > Subject: RE: [PHP] How to do a dynamic UPDATE SET > > > Something like this might work for you. (Just typed in the code > and didn't > test it, so take with a grain of salt. It doesn't really take > into account > all types of data, but maybe it will help with an idea.) > > Have groups of the following in your form: > > <TR> > <TD> > <SELECT NAME="column[]"> > <OPTION VALUE="column_name1">column_name1 > <OPTION VALUE="column_name2">column_name2 > </SELECT> > </TD> > <TD><INPUT NAME="col_value[]" TYPE="text" VALUE="" > SIZE=20></TD> > </TR> > > and process it like: > > $set_clause = "SET "; > $comma = ""; > for ($i=0; $i<count($column); $i++) { > $set_clause .= $comma . $column[$i] . "=" . $col_value[$i]; > $comma = ","; > } > > $sql = "UPDATE $table $set_clause WHERE id=$id"; > > -Joe > > > -----Original Message----- > > From: René Fournier [mailto:rene.fournier@markada.com] > > Sent: Monday, October 01, 2001 2:33 PM > > To: php-general@lists.php.net > > Subject: [PHP] How to do a dynamic UPDATE SET > > > > > > I'm having a REALLY hard time with something that's probably > > easy to do (for > > someone :-)... > > > > Normally, to perform an update on a table with data from a > > submitted form, I > > would use something like: > > > > $sql = "UPDATE $table SET pet='$pet', name='$name' WHERE > > id=$id"; > > $result = mysql_query($sql); > > > > And it would work. Of course, if I wanted to update a > > different table (with > > different columns/fields), I would need to only change the > > SET part of the > > SQL (since the value for $table is dynamically generated). > > For example, > > > > $sql = "UPDATE $table SET car='$car', year='$year' WHERE > > id=$id"; > > $result = mysql_query($sql); > > > > But here's what I want to do now: I want to use the same two > > lines of code > > for any possible form data that might be submitted--in other > > words, I don't > > want to have to create unique $sql/$result lines for each and > > every table in > > my database. I want this .php code to accept whatever number > > and type of > > form elements/data submitted, and make the appropropriate SET values. > > Anyone know how I can do that? I actually have tried several > > things to pass > > the form data strcuture (number and names of columns) over to > > this php code, > > but haven't been able to get anything working. Can anyone > > help?? Much > > thanks if you can.. > > > > ...Rene > > > > --- > > Rene Fournier > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > > For additional commands, e-mail: php-general-help@lists.php.net > > To contact the list administrators, e-mail: > > php-list-admin@lists.php.net > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net