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

Thanks David! And thanks to everyone that helped out. This last suggestion I tried worked beautifully--(and even better, I think I understand why :-). ...Rene --- Rene Fournier renefournier@yahoo.com > -----Original Message----- > From: David Balatero [mailto:db@reactiongaming.com] > Sent: Monday, October 01, 2001 7:22 PM > To: René Fournier; php-general@lists.php.net; php-db@lists.php.net > Subject: 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 > > > >