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

Hi again, Let me make sure that I've got what you want to do. I think I might have suggested a form setup that you're not looking for. Here goes: You'd like to have a number of web forms that work on different tables and columns. And you'd like to have all the forms call a single PHP script that updates the appropriate tables and columns in the database. Am I close? If so, here's another approach: You have a simple form that basically adds or edits a single table record. (We're just going to deal with the update part of it right now though.) So you might have a form like: <FORM METHOD="post" ACTION="yourphpscript"> <INPUT TYPE="hidden" NAME="table" VALUE="sometable"> <TABLE> <TR><TD>ID:</TD> <TD><INPUT TYPE="text" NAME="sometable.id" SIZE=30></TD></TR> <TR><TD>Pet:</TD> <TD><INPUT TYPE="text" NAME="sometable.pet" SIZE=30></TD></TR> <TR><TD>Name:</TD> <TD><INPUT TYPE="text" NAME="sometable.name" SIZE=30></TD></TR> <TR><TD></TD> <TD><INPUT TYPE="submit" NAME="submit" VALUE="Submit"></TD></TR> </TABLE> </FORM> and your all-purpose "yourphpscript" might have code like: if (isset($submit)) { // if there is an ID, then it's an update if (isset($id)) { reset($HTTP_POST_VARS); $comma = ""; $set = ""; // Form variable names that start with "$table." // represent column data to add to SET. while(list($name,$value) = each($HTTP_POST_VARS)) { if (ereg("^$table\.", $name)) { $set .= "$comma$name='$value'"; $comma = ","; } } if ($set == "") { print "Nothing to update"; exit; } $sql = "UPDATE $table SET $set WHERE id=$id"; $result = mysql_query($sql); } } Hope it helps, -Joe P.S. Code is once again untested... > -----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: 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 > > > > > > > > > -- >