Best php method for inserting/updating FORM post

[acctman]

Hi I have a FORM of about 20 different form post data to insert and update into a table. whats the best way to go about doing this other than typing each name and value into a long mysql insert. Right now my FORM names are set like this name=add[status], name=add[ethnicity] etc.

 

Also since some of the entries will be update and not new insert. How should I write the mysql query, I know there is a code that Inserts and if data is already there it Updates. How would I do something like that.

[F1Fan]

Since you already have the inputs in array form, use a foreach loop like this:

 

<?php
foreach ($_POST['add'] as $col=>$val){
  // $col is your column name, and $val is your entered value.
}
?>

[F1Fan]

Sorry, I didn't address your second question. In MySQL, there's a way to do this:

 

INSERT INTO table (column) VALUES (values) ON DUPLICATE KEY UPDATE table SET column = value

 

That's not the right syntax, but you get the idea.

[acctman]

Sorry, I didn't address your second question. In MySQL, there's a way to do this:

 

INSERT INTO table (column) VALUES (values) ON DUPLICATE KEY UPDATE table SET column = value

 

That's not the right syntax, but you get the idea.

 

do you know where i can get the right syntax at. I've never used this method before so this is all new to me

[F1Fan]

It's the same syntax as an INSERT and an UPDATE. Write out your INSERT, write out your UPDATE, and just add "ON DUPLICATE KEY" in between them.

[acctman]

does this look right? (db fields are m_POST-FIELD-NAME)

 

foreach ($_POST['add'] as $col=>$value){

  // $col is your column name, and $val is your entered value.
  mysql_query("INSERT INTO r_members m_".$col." VALUES ($value) WHERE m_user = "testuser") ON DUPLICATE KEY UPDATE r_mmembers SET m_".$col." = $value WHERE m_user = "testuser";
}

[F1Fan]

Close.

<?php
foreach ($_POST['add'] as $col=>$value){0
  mysql_query("INSERT INTO r_members (m_$col) VALUES ('$value') WHERE m_user = 'testuser' ON DUPLICATE KEY UPDATE r_members SET m_$col = '$value' WHERE m_user = 'testuser'");
}
?>

[CroNiX]

You mentioned that you are getting all of this data from a user-inputted form, yet I don't where you 1) validate the info or 2) escape the info for proper sql insertion.  If you don't do these things, especially #2, people can do some serious damage or get unintended info from your database.  Its really a bad idea to put user inputted data straight into the database.  Also, if you don't validate the data, like making sure something that is being inserted into a INT column type is really an integer, then you can get errors.  You should check if its an INT, and if not redirect back to the form with an error message that the value is the wrong type.

[acctman]

You mentioned that you are getting all of this data from a user-inputted form, yet I don't where you 1) validate the info or 2) escape the info for proper sql insertion.  If you don't do these things, especially #2, people can do some serious damage or get unintended info from your database.  Its really a bad idea to put user inputted data straight into the database.  Also, if you don't validate the data, like making sure something that is being inserted into a INT column type is really an integer, then you can get errors.  You should check if its an INT, and if not redirect back to the form with an error message that the value is the wrong type.

 

how would I escape the info

[acctman]

i've added a escape string. as for the validation for form field entries, that's being down on the form and before the sql query insert. how does this look?

 

foreach ($_POST['add'] as $col => $value) $_POST['add'][$col] = strip_tags(stripslashes($value),allowed_html); {
  
mysql_query("INSERT INTO r_members (m_$col) VALUES ('$value') WHERE m_user = 'testuser' 
ON DUPLICATE KEY UPDATE r_members SET m_$col = '".sql_escape_string(stripslashes($value))."' WHERE m_user = 'testuser'");

}

[acctman]

is that the proper way to escape in sql

[kenrbnsn]

You can't use the "where" clause on an Insert. It doesn't make any sense.

 

Ken

[acctman]

You can't use the "where" clause on an Insert. It doesn't make any sense.

 

Ken

 

How would I update a users by insert first time data into fields and updating fields for specific user? Do I just use update only even if a field is empty and its the first time putting data into it?

[CroNiX]

By using UPDATE instead of INSERT of course

[acctman]

I think I understand it now, use INSERT when only created new "rows" and UPDATE to make changes to a row. I was confused with inserting data into fields for the first time and updating fields.

 

how do this look now... anything else I need to change or add?

 

foreach ($_POST['add'] as $col => $value) $_POST['add'][$col] = strip_tags(stripslashes($value),allowed_html); {
  mysql_query("UPDATE r_members SET m_".$col."='".sql_escape_string(stripslashes($value))."' WHERE m_user=testuser");
}