I have a table named fruits:
id fruit 1 Apple 1 Banana 2 Apple 2 Apple 2 Pear
I would like to be able to add several rows at once using PHP.
I tried having two sets of two textboxes (for id and fruit) but that only insert 1 row, using the last set of textboxes.
EDIT:
Then what do I do if I have a table like this:
id fruit taste 1 Apple Good 1 Banana Okay 2 Apple Good 2 Apple Bad 2 Pear Good
You need to name your text boxes properly using brackets [] to form an array, then loop through your POST'ed array to insert the data. Be certain you're escaping data before running your query by using mysql_real_escape_string or prepared queries.
EDIT: Updating examples because OP appended information to the question.
In the extended example you've provided, it looks like the taste only has a few choices. In that case, I would use a <select> element over a text box. Check out my examples for how, personally, I would do it.
Form
Notable changes: Using a simple loop in PHP, you can specify the number of fields you want to show and easily add other options to the taste select box. I'm placing an arbitrary number within the [] to be certain we link the fruit to the taste when we process the form.
<?php
$n = 5; //Number of fields to show
$tastes = array('Great', 'Good', 'Okay', 'Bad', 'Horrible');
for($i=0;$i<$n;$i++) {
echo '<p>';
echo '<input type="text" name="fruit['.$i.']" /> ';
echo '<select name="taste['.$i.']">';
foreach($tastes as $t) {
echo '<option value="'.htmlentities($t).'">'.$t.'</option>';
}
echo '</select>';
echo '</p>';
}
echo '<input type="submit" name="submit" value="Submit" />';
?>
Process Code
Notable changes: I'm checking to make sure the fruit has a value, otherwise it won't insert. I'm using that arbitrary value (which is the array key $k) to select the taste that matches the fruit.
<?php
if($_POST['submit']) {
foreach($_POST['fruit'] as $k => $v) {
if(!empty($v)) {
$query = "INSERT INTO fruits (fruit, taste) VALUE ('".mysql_real_escape_string($v)."', '".mysql_real_escape_string($_POST['taste'][$k])."')";
mysql_query($query);
}
}
}
?>
Single insert using implode function.
Your HTML
<input type="text" name="fruit[]" />
<input type="text" name="fruit[]" />
<input type="text" name="fruit[]" />
Your PHP
foreach($_POST['fruit'] as $fruit) $values[] = "('".mysql_real_escape_string($fruit)."')";
if(count($values) > 0) mysql_query("INSERT INTO fruits (fruitName) VALUES " . implode(',', $values));
MySQL supports multiple inserts in a single query:
INSERT INTO table (field1, field2) VALUES (values1a, values2a), (values1b, values2b), etc...
You can make an html array of elements for example
in html
<form action='save.php' method='post'/>
<input name='fruits[]' value='' />
<input name='fruits[]' value='' />
<input name='fruits[]' value='' />
</form>
Then in save.php
$fruits = '';
foreach($_POST['fruits'] as $fruit) {
$fruits .= "({$fruit}),"
}
$fruits = rtrim($fruits,',');
$sql = "INSERT INTO fruits(fruit) VALUES {$fruits}";
In your insert statement just add the values, like so:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
so for your example,
INSERT INTO fruits (fruit) VALUES ('Orange'),('Lemon'),('Lime');
should work.
