Page 1 of 1

Inserting all $_POST variables via a MySQL query

Posted: Sat Apr 14, 2007 7:16 pm
by Astillas
Hello everyone, I would like to gather all of the post variables from a certain form, and then put all of these variables into the database (after security to disallow injection, of course). So, I have the following code:

Code: Select all

<? //I have omitted the security code below for brevity
foreach($_POST as $postname => $value) {
	$sql = mysql_query('INSERT INTO `table` (`'.$postname.'`) VALUES ("'.$value.'")');
	};
?>
Understandably, the result is that each field updated with value "$value" is on a new row! I would like for them to be in the same row. Any thoughts would be much appreciated.

Posted: Sat Apr 14, 2007 7:25 pm
by Gurzi
Obviously, you have a foreach , if your $_POST array have 10 values, you will run on foreach 10 times , so you will create 10 differents rows with $value.

Posted: Sat Apr 14, 2007 7:35 pm
by Astillas
Gurzi, I understand this -- however, would you happen to know how I could remedy this problem? Thanks!

Posted: Sun Apr 15, 2007 3:00 am
by Ollie Saunders
Why is it a problem? But to insert more than one value into a row you need to insert more than one column.

Code: Select all

INSERT INTO `table` (`a`, `b`) VALUES (1, 2)
here both 1, 2 exist on the same row in separate columns.

Posted: Sun Apr 15, 2007 4:12 am
by timvw
I guess the point is that the OP should build 1 query, instead of building a new query for each item...Since you probably need to append both the column name and the value per item, you'll probably have two subparts:

Code: Select all

$first = "INSERT INTO ... ( ";
$second = ") VALUES (";

foreach($input ... ) {
   $first .= // append column name;
  $second .= // append value;
}

$sql = $first . $second;

Posted: Sun Apr 15, 2007 4:17 am
by Benjamin
Just serialize the array, put it in a blob and be done with it.

Posted: Sun Apr 15, 2007 4:19 am
by Kieran Huggins

Code: Select all

function array2query($array){
	foreach($array as $k=>$v){
		$q['fields'][]=$k;
		$q['values'][]=mysql_real_escape_string($v);
	}
	return $q;
}

$q = array2query($_POST);

$result = MySQL_query("INSERT INTO `table` (`".implode("`,`",$q['fields'])."`) VALUES ('".implode("','",$q['values'])."')",$db) or die(MySQL_error());
Of course, you DID validate the POST data, didn't you??????

Posted: Sun Apr 15, 2007 4:22 am
by Benjamin

Code: Select all

mysql_query("INSERT INTO `table` (`" . implode("`, `", array_map('mysql_real_escape_string', array_keys($_POST))) . "`) VALUES ('" . implode("', '", array_map('mysql_real_escape_string', $_POST)) . "')");
:wink:

I was thinking something more along the lines of..

Code: Select all

mysql_query('INSERT INTO `table` (`form_data`) VALUES ("' . mysql_real_escape_string(serialize($_POST)) . '")');
because it sounds as if he may not know what is going to be posted, and the fields might not exist in the table, and an unexpected field won't cause the query to fail.

Posted: Sun Apr 15, 2007 4:10 pm
by Astillas
Wow, thanks so much for the help! I'll implement all of those suggestions immediately.

Thanks again!