CODEWITHSUNDEEP
phpmysqlrows
Brian
Brian

Reputation: 365

PHP POST multiple rows into MySQL

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

Upvotes: 1

Views: 4417

Answers (5)

Michael Irigoyen
Michael Irigoyen

Reputation: 22947

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);
    }
  }
}
?>

Upvotes: 3

awestover89
awestover89

Reputation: 1763

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.

http://dev.mysql.com/doc/refman/5.5/en/insert.html

Upvotes: 0

Damon Skelhorn
Damon Skelhorn

Reputation: 1511

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));

Upvotes: 1

Lylo
Lylo

Reputation: 1261

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}";

Upvotes: 0

Marc B
Marc B

Reputation: 360562

MySQL supports multiple inserts in a single query:

INSERT INTO table (field1, field2) VALUES (values1a, values2a), (values1b, values2b), etc...

Upvotes: 0

Related Questions