Inserting all $_POST variables via a MySQL query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Astillas
Forum Newbie
Posts: 3
Joined: Sat Apr 14, 2007 7:04 pm

Inserting all $_POST variables via a MySQL query

Post 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.
Gurzi
Forum Commoner
Posts: 27
Joined: Wed Aug 02, 2006 4:04 pm

Post 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.
Astillas
Forum Newbie
Posts: 3
Joined: Sat Apr 14, 2007 7:04 pm

Post by Astillas »

Gurzi, I understand this -- however, would you happen to know how I could remedy this problem? Thanks!
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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;
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Just serialize the array, put it in a blob and be done with it.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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??????
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
Astillas
Forum Newbie
Posts: 3
Joined: Sat Apr 14, 2007 7:04 pm

Post by Astillas »

Wow, thanks so much for the help! I'll implement all of those suggestions immediately.

Thanks again!
Post Reply