Create UPDATE SQL stmt from $_POST vars

ruthsimon

Looking for code to create the SET portion of a SQL UPDATE statement from the values of my $_POST variables. For example:

Form has the fields: Location, Street, City
Another form has fields: Name, Phone, Fax

I'd like to only code the UPDATE statement once and have it pull the $_POST variables to populate the SQL.

Instead of:

UPDATE Location 
  SET Location = '$Location',
  Street = '$Street', 
  City = '$City',
  Zip = '$Zip'
WHERE LocationID = '$LocationID'

UPDATE Name
  SET Name = '$Name',
  Phone = '$Phone', 
  Fax = '$Fax'
WHERE NameID = '$NameID'

Use:

UPDATE $value
  SET $value = '$value'...etc.

Possible??

Thanks, Ruth

eoghain

Sure as long as you build everything corectly. I'm going to assume 1 table per form and that all fields in your form are named the same as the columns in the table, and finally that you have a hidden field called table in each form, and one more called ID.

   $sql = "UPDATE " . $_POST["table"]
          "   SET ";
   $where = " WHERE ";
   $sep = "";

   // $id is used to tell us what the ID field is named
   $id = $_POST["ID"];

   for (reset($_POST); $key = key($_POST); next($_POST)) {
      if ($key != "table" && $key != $id) {
         $sql .= $sep . $key . " = '" . $_POST[$key] . "'";
         $sep - ",\n";
      } else if ($key == $id) {
         $where .= $key . " = '" . $_POST[$key] . "'";
      }
   }

   $sql .= $where;
   echo $sql;  // Just to show you that the SQL was built correctly.

This should give you enough of an example to get something like this working for your pages.

ruthsimon

Yes, thank you. I could get this to work...

But next question.

Now that I see the code, is it cleaner, do you think to just go with the original two SQL statements? Would this code cause more confusion than efficiency?

Wondering.... Ruth

eoghain

If you have to do 2 queries and you will only ever be doing these 2 queries then there is no reason to abstract your code like this.

The only reason to do what I did was if you wanted to build a page to handle your updates no matter what the form infront was doing. For example if you were building a database administration tool, and you had a page that would display the values of a row of a table in the database depending on what table/row the user selected. And you were giving the user the ability to update that row on that screen. Now there is no way you, as the programmer, can know what data that table is going to be holding or what the field names are. So you'd have to build a single update script that would be told everything it needs to know from the form that is submitting to it. That's where the abstracted code comes in. Ofcourse it'd have to be more robust that what I've shown, but you get the idea.

In short abstracting in this way probably isn't what you are looking for and to do this would cause you to re-design more of your code to make everything work well together. In the long run doing this work would make your code more efficient and easier to maintain, but in the short term you are going to add a lot more work for yourself, and possibly some big headaches.