Insert multiple arrays into mysql

Question

As an example, let's say I have three form text inputs all with the name item[]. Each of these inputs have a value consisting of two numbers separated by a comma.

<input type="text" name="item[]" value="8,6" />
<input type="text" name="item[]" value="6,1" />
<input type="text" name="item[]" value="9,4" />

The first number in the value field relates to an id in a mysql database table. The second is a value that will update another field.

I have used:

foreach($_POST['item'] as $items){
    echo $items. "<br/>";
}

This displays the values like so:

8,6
6,1
9,4

I would now like to take these values and insert into a table with data fetched with the first value (the id value).

What would be the best way to go about accomplishing this?

@sgt insert, sorry I said update but I meant insert

proPhet
– proPhet

2014-09-12 11:39:59 +00:00
Commented Sep 12, 2014 at 11:39 — proPhet
– proPhet, Commented Sep 12, 2014 at 11:39

lsouza · Accepted Answer · 2014-09-12 11:43:37Z

2

In this case, explode is the best way. Try using it along with list.

foreach($_POST['item'] as $item){
    list($id, $value) = explode(',', $item);
}

answered Sep 12, 2014 at 11:43

lsouza

2,4984 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bansi Over a year ago

This is easy to use with prepared statements.

Aleksandar Vasić · Accepted Answer · 2014-09-12 11:40:27Z

0

 foreach($_POST['item'] as $items){
      $item = explode(",",$items);
      $id = $item[0];
      $otherfield = $item[1];

 //YOUR SQL QUERY with $id and $otherfield
 }

answered Sep 12, 2014 at 11:40

Aleksandar Vasić

5936 silver badges17 bronze badges

Comments

Sougata Bose · Accepted Answer · 2014-09-12 11:41:53Z

0

try this -

$values = array();
foreach($_POST['item'] as $items){
    $values[] = "(".$items.")"; 
}

$sql = "insert into `tablename` (`field1`, `field2`) values ".implode(',', $values);

answered Sep 12, 2014 at 11:41

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

Comments

bansi · Accepted Answer · 2014-09-12 11:56:16Z

0

Here is an one-liner without foreach.

//Warning this is unsafe to trust posted data.
$query = "INSERT INTO yourtable (id, otherfield) VALUES " . implode(',', array_map(function($v){return "($v)"; },$_POST['item']));

Note: NEVER trust posted data. You should always sanitize and validate the data before updating to database. Also try to use prepared statements whenever possible

Reference: array_map

answered Sep 12, 2014 at 11:56

bansi

57.3k6 gold badges44 silver badges52 bronze badges

1 Comment

proPhet Over a year ago

I only use prepared statements when there is post or get data in the equation, thanks, I'll give it a bash

Kleskowy · Accepted Answer · 2014-09-12 12:18:07Z

Assuming that your id is the primary key of your table, I'd use an efficient way to add or update selected rows with specific ids. As mentioned before, you should use a combination of explode and list to extract what you need and build sql query.

//assuming you don't already have a db connection
//fill constants yourself with apropriate connection info
$dbConnection =  mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); 

foreach($_POST['item'] as $item){
   list($id, $value) = explode(',', $item);

   //prevent inserting empty values (assuming that 0 is not allowed)
   if (!empty($id) && !empty($value)) {

      //remember escaping - prevent from SQL injection!
      $id = mysqli_real_escape_string($id); 
      $value = mysqli_real_escape_string($value);

      $sql = "INSERT INTO table (id, value) VALUES ('{$id}', '{$value}')
         ON DUPLICATE KEY UPDATE value='{$value}'";
      mysqli_query($dbConnection, $sql);
   }
}

Collectives™ on Stack Overflow

Insert multiple arrays into mysql

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related