foreach question

[rubenhan]

Hi, guys

Right now I have dynamic input fields that would be generated by javascripts.
So if the user generates four sets of data, I'll have four sets of text fields like this.

name[] age[] height[] nationality[]
name[] age[] height[] nationality[]
name[] age[] height[] nationality[]
name[] age[] height[] nationality[]

These are the name of each input field.

So, once they are submitted, I want to INSERT them to my Mysql database. I did some research online and I found out I have to use FOREACH function to handle this dynamic input data and so I came up with the following initial codes.

	$query = "INSERT INTO users (name, age, height, nationality) VALUES ";

foreach($_POST as $key => $value){
        $q_parts[]='('.$value.')';
}

$query .= implode(',',$q_parts);

Am I going into the right direction? With this code, would the values of the input fields be organized accordingly for the insertion?

[YAOMK]

Correct me if I'm wrong, but from your code I gather that $_POST['name'] is an array of names and so on, if that is the case then to access that array you do:

$count = count($_POST['name']);

$values = null;
for($i = 0, $i < $count, $i++ )
{
   $values .= "({$name[$i]},{$age[$i]},{$height[$i]},{$nationality[$i]}),";
}

//run sql query.....

Remember to use mysql_real_escape_string() on your $_POST data.

[rubenhan]

Hey YAOMK, thank you for your post.

I applied your codes in my PHP codes and it looks like this. Is this the correct way to apply them?

I'll make sure I sanitize my input later on.

   if (isset($_POST['nutritionsubmit'])) {
	$query = ""INSERT INTO users (name, age, height, nationality) VALUES "; 
        $name = array($_POST["name"]);
	$age = array($_POST["age"]);
	$height = array($_POST["height"]);
	$nationality = array($_POST["nationality"]);

$count = count($_POST["name"]);

for($i = 0, $i<$count, $i++)
{
   $values = "({$name[$i]},{$age[$i]},{$height[$i]},{$nationality[$i]}),"; 
   $query .= $values;
   $result = mysql_query($query,$connection);
} 
}

[bradgrafelman]

Not quite, for a few reasons:

• You have a parse error, first of all; get rid of the extra double quote at the beginning of the first $query variable declaration.

• $_POST['name'] and others are already arrays; using array() when defining variables for them simply nests the arrays inside another array; get rid of the array()'s in your variable declarations.

• The last iteration through the loop will have an extra comma after it; you might consider using [man]rtrim/man outside the loop on just the $values variable to get rid of this extraneous comma.

• In your code, the query will be executed on each iteration of the for() loop; this is very inefficient (not to mention that the code will cause undesired results; you append the values each time through the loop, so first it will insert the first set posted, next it will insert the first and second, next it will insert the first, second, and third, ... and so on).

To fix the last two problems I mentioned above, you might try moving the $query .= and $result = lines outside of the for loop, in addition to adding this immediately after the loop:

$values = rtrim($values, ',');

so that the extra comma is removed.

EDIT: Completely forgot; you also didn't use [man]mysql_real_escape_string/man as was mentioned above. As such, your code is vulnerable to SQL injection attacks; user-supplied data should never be placed directly inside a SQL query without properly escaping it first.

[YAOMK]

Thank you bradgrafelman. I should have been more thorough with my answer, but I was already falling asleep in my chair.

In case you are still unclear about how $_POST['name'] is an array. In your HTML code you are naming multiple input fields with the same name. This creates an array once it gets posted to PHP.

Also the correct structure for multiple inserts in MySQL is as follows:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

From this I'm sure you can grasp the concepts behind the prototyped code. Good luck.

[bradgrafelman]

YAOMK wrote:

In your HTML code you are naming multiple input fields with the same name. This creates an array once it gets posted to PHP.

Just to add more info... these fields must have the same name plus the brackets ( "[]" ) at the end.

Also, note that while numeric values shouldn't be enclosed in quotes (just as in PHP), strings must be quote-delimited. For example, the last part of the above example SQL could also be something like:

... VALUES('dog',1,'cat'), ('elephant', 2, 'mouse'), ...