Insert from multiple form fields 1

[mcquam]

I have a form with multiple text fields of the same name (ingredient[]). My problem is that I can't get the array into the correct format for the insert query to work.

	<form action="index.php" method="post">
	<fieldset><legend>bla bla:</legend>
	<p><b>Ingredients:</b><input type="text" name="ingredient_list[]"  /></p>
	<p><b>Ingredients:</b><input type="text" name="ingredient_list[]"  /></p>
	<p><b>Ingredients:</b><input type="text" name="ingredient_list[]"  /></p>
	</fieldset>
	
	<div align="center"><input type="submit" name="submit" value="Submit" /></div>

</form>

I am trying to keep it simple as I really should allow the user to add more fields as necessary but in the meantime I'm just going to use maybe 10 or 15 ingredient_list fields.

I presume I should be using a foreach loop something along these lines:

			foreach ($_POST[$ingredient_list] as $ingredient) {
			$query = 'INSERT INTO ingredient_assoc (ingredient_assoc_id, ingredient, recipe_id) VALUES ';
	$query .= "($ingredient), ";
						$result = @mysql_query ($query);}

 

[vacunita]

You are on the right track:

Just remove the $ from the ingredient_list index inside the $_POST variable, and surround the index with quotes.

foreach ($_POST[[red]'[/red]ingredient_list[red]'[/red]] as $ingredient) {
            $query = 'INSERT INTO ingredient_assoc (ingredient_assoc_id, ingredient, recipe_id) VALUES ';
    $query .= "($ingredient), ";
                        $result = @mysql_query ($query);

I would also not suppress errors (@) from the mysql_query call if you are still debugging.

You are also missing the other values you want to insert.
ingredient_assoc_id, and recipe_id, have no corresponding values. That will likely generate an error.


and finally, I'd at least clean the variables from the form before inserting them into the database.

Look into sql_injection for details:

http://en.wikipedia.org/wiki/SQL_injection

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[mcquam]

Thanks. I tried what you suggested but got similar results. I've tried to strip it down to the important bits and tried it again (with a test db of user data this time) and here is the code:

<?php
require_once ('mysql_connect.php');
				
?>
	<form action="test2.php" method="post">
	<fieldset><legend>bla bla:</legend>
	<p><b>Ingredients:</b><input type="text" name="user_name[]"  /></p>
	<p><b>Ingredients:</b><input type="text" name="user_name[]"  /></p>
	<p><b>Ingredients:</b><input type="text" name="user_name[]"  /></p>
	</fieldset>
	
	<div align="center"><input type="submit" name="submit" value="Submit" /></div>

</form>
	<?php

foreach ($_POST['user_name'] as $user) {
            $query = 'INSERT INTO users (user_name) VALUES ';
    $query .= "($user), ";
                        $result = mysql_query ($query);
}
	{echo '<pre' . print_r($_POST,1) . '<pre>';}

echo "$query <br />\n";

?>

and these are the results when i entered a1, a2 and a3:

Array ( [0] => a1 [1] => a2 [2] => a3 ) [submit] => Submit )
INSERT INTO users (user_name) VALUES (a3),

 

[vacunita]

That would be correct. Lets take it line by line just to clear any confusion:

foreach ($_POST['user_name'] as $user) {  [green]\\Loop through each of the users in the array[/green]
            $query = 'INSERT INTO users (user_name) VALUES ';
    $query .= "($user), "; [green]\\ create query for each user[/green]
                        $result = mysql_query ($query); [green]\\execute query for each user[/green]
}
    {echo '<pre' . print_r($_POST,1) . '<pre>';} [green]\\print the $_POST variable.[/green]

echo "$query <br />\n";

[green]\\Print the query string created in the for each loop. This will print the last iteration of the string, as it is overwritten each time the loop begins again. [/green]

I have a feeling you are expecting to have all 3 user_ids in the same insert statement, but that will neither happen nor would it work in the query.

The code you posted should in fact have inserted 3 rows, one for each user you entered in your form.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[mcquam]

Yes, I did think it would make one statement. I expected the query to result in comma separated values in brackets. However, no records were inserted when I look at my users database, not even the "a3" that it seems to have inserted.
I have managed to insert records before but I have overwritten the code but I'm pretty sure my db connection is ok.

 

[DonQuichote]

I suppose you have MySQL running on your development machine. For a development machine, it can be quite handy to enable the query-log. This is a file containing all the queries that are sent to the (development) server. With a program like BareTail (search for it), you can monitor that file live, so you can actualy see what is communicated to the database.

To enable the query-log, add a line in the form:

log=/path/to/the/log

to the [mysqld] section of my.cnf or my.ini, and restart the mysql server.

Warning: this log can get large, so one in a while you will want to throw it away. On a live server, this can gradually take up a lot of space. Furthermore, GRANT statements are also logged if you do not prevent it. So the query-log is less useful on a live server.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[vacunita]

Perhaps adding error display to the query may give a few clues.

 $result = mysql_query ($query)[red]or die(mysql_query())[/red];

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[mcquam]

DQ - I will search for Baretail - that sounds very helpful. In the meantime vacunita I get the following:

Warning: Wrong parameter count for mysql_query() in C:\xampp\htdocs\test1\test2.php on line 20

 

[vacunita]

look like it can't find the connection to the DB.

Make sure your connection is available to it.

Can you show us what is inside mysql_connect.php?

You can of course be specific, and pass the connection variable to the mysql_query function.

Assuming your connection is made like so:

$conn=mysql_connect("host","user","password");

You can explcitely pass the $conn var to mysql_query.

$result = mysql_query ($query, [red]$conn[/red]) or die(mysql_error());

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[mcquam]

Ok I have simplified the connection so the whole file now looks like this:

<?php
// replace these with your credentials 
$server = "localhost"; 
$user = "root"; 
$password = ""; 
$database = "test"; 

// Connect to Server 
mysql_connect($server, $user, $pass) or die(mysql_error()); 

// Select Database 
mysql_select_db($database) or die(mysql_error()); 


//$conn=mysql_connect("localhost","root","");
//				mysql_select_db(test);
?>
	<form action="test2.php" method="post">
	<fieldset><legend>bla bla:</legend>
	<p><b>Ingredients:</b><input type="text" name="user_name[]"  /></p>
	<p><b>Ingredients:</b><input type="text" name="user_name[]"  /></p>
	<p><b>Ingredients:</b><input type="text" name="user_name[]"  /></p>
	</fieldset>
	
	<div align="center"><input type="submit" name="submit" value="Submit" /></div>

</form>
	<?php

foreach ($_POST['user_name'] as $user) {
	        $query = 'INSERT INTO users (user_name) VALUES ';
			$query .= "($user), ";
    $result = mysql_query ($query);
}
	{echo '<pre' . print_r($_POST,1) . '<pre>';}
echo "$query <br />\n";
echo "$conn <br />\n";
echo "$result <br />\n";
?>

and the result is this:
Array ( [0] => dzsfv [1] => zdfgv [2] => azsdgv ) [submit] => Submit )
INSERT INTO users (user_name) VALUES (azsdgv),

I know the connection works but no data has been inserted.

 

[jpadie]

just fyi, @OP, there is no problem with making a single query.

if (!empty($_POST['user_name'])){
 array_map('enquote', $_POST['user_name']); //cleanse the array
 $query = sprintf('Insert into tblUsers (user_name) values ( %s )', implode ('),(', $_POST['user_name']));
 mysql_query($query) or die (mysql_error());
}

function enquote($data){
 return "'". mysql_real_escape_string($data) . "'";
}

note that it is not good practice to manipulate a supeglobal such as POST and GET. it would be better to assign the $_POST['user_name'] array to another variable and work on that. consider the code about a proof of concept rather than 'good' coding.

 

[mcquam]

Many thanks jpadie, that does seem elegant. However I still have the same result.
I need to have this data populated so that I can move on and play with different options.

Am I missing something stupid?

 

[jpadie]

your code will not work because the values are not being enquoted before database submission. this should not happen with the code that i posted as the enquote function takes care of it.

your code can be easily fixed by changing the for next loop

foreach ($_POST['user_name'] as $user) {
            $query = 'INSERT INTO users (user_name) VALUES ';
            $query .= "('" . mysql_real_escape_string($user)) . "')";
            $result = mysql_query ($query);
}

 

[mcquam]

Oh joy!
Many thanks for your help jpadie.
I have tried both your suggestions and both work now but I will use your first.
Can you explain what you meant about manipulating the POST superglobal?

and thanks again, i have been stuck with this for ages.

 

[mcquam]

Also I don't really follow why tblUsers works in the query when the table name is users because I admit the first time I tried this I changed it.

 

[jpadie]

the _POST superglobal should remain a reflection of what the user actually submitted rather on something that you have changed.

so take a copy first and work on the copy.

if (!empty($_POST['user_name'])){
 $uN = $_POST['user_name'];
 array_map('enquote', $uN); //cleanse the array
 $query = sprintf('Insert into users (user_name) values ( %s )', implode ('),(', $uN));
 mysql_query($query) or die (mysql_error());
}