Insert array data to each table column

Question

Here is an array from the print_r($_POST)

Array
(
    [january] => Array
        (
            [0] => 10
            [1] => 20
            [2] => 5
        )

    [february] => Array
        (
            [0] => 9
            [1] => 8
            [2] => 10
        )

    [march] => Array
        (
            [0] => 2
            [1] => 5
            [2] => 6
        )

)

Loop for $_POST.

$data = array();
foreach ($_POST as $key => $value) {
   $data[] = $value;
}

Statement

INSERT INTO table (january,february,march) VALUES (".implode(", ", $data).")

With this current array how do I make a correct statement? I want to store the data something like this

id january february march
--------------------------
1  10      9        2
2  20      8        5
3  5       10       6

Try to write a query manually at first. So a complete query that inserts 3 rows with hardcoded values. — zerkms
– zerkms, Commented Nov 14, 2012 at 0:11

David Müller · Accepted Answer · 2012-11-14 00:18:52Z

3

Do it like this:

$month = array("january" => array(10, 20, 5), "february" => array(1,2,3), "march" => array(3,4,5));

$datasets = array();

foreach ($month as $monthname => $monthdata)
{
    foreach ($monthdata as $i => $data)
    {
        $datasets[$i][] = intval($data);
    }
}

$values = array();

foreach ($datasets as $dataset)
{
    $values[] = "(" . implode(", ", $dataset) . ")";
}


echo "INSERT INTO table (january,february,march) VALUES " . implode(", ", $values);

I have put an intval in the code to apply some "basic security" because you should never insert users data unfiltered in your database. Inform yourself about prepared statements and mysql escaping, please ;).

answered Nov 14, 2012 at 0:18

David Müller

5,3612 gold badges32 silver badges34 bronze badges

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

6 Comments

tadman Over a year ago

intval is not a proper substitute for escaping. This would be a billion times safer with placeholders instead of actual data.

David Müller Over a year ago

What security vulnerability could you possibly have after intval'ing the input?

tadman Over a year ago

intval creates a false sense of security. It's not a general purpose solution to a serious problem. Basically it's a red flag for bad escaping practices.

David Müller Over a year ago

I still don't get it. It fixes all sql injection vulnerabilities and if you want an int anyway, what's the big deal? I mean yeah, you could use filter_var which is able to do some range checking whatsoever, but as a basic approach, I don't see the problem.

tadman Over a year ago

What do you do about string data, then? intval isn't going to help you there. It also mangles decimal values and can't handle booleans or dates. It's so narrow a solution as to be useless in general practice.

|

Landon · Accepted Answer · 2012-11-14 00:21:30Z

-1

This is what I would do:

$rows=sizeof($_POST['january']);#find how many rows we want to add
for($i=0;$i<$rows;$i++){
  $columns=array();
  $values=array();
  foreach ($_POST as $key => $value) {
    $columns[]=$key;
    $values[]=$value;
  }
  $sql="INSERT INTO table (".implode(",",$columns).") VALUES (".implode(",",$values).")"
  mysql_query($sql)
}

answered Nov 14, 2012 at 0:21

Landon

4,1183 gold badges33 silver badges43 bronze badges

3 Comments

tadman Over a year ago

If that's your idea of how to do it, you really need to read up on proper SQL escaping because this is absolutely terrifying. Do not do this. Ever. Stop using mysql_query for the sake of humanity.

Landon Over a year ago

I was just using the same format that the OP was using. The question was more about the php side of things than mysql. You shouldn't downvote for that.

tadman Over a year ago

If someone puts this into production, it would create a gaping security hole. Since this promotes recklessly bad practices, it's not a good answer. Adding escaping might be annoying, but it makes the answer correct and safe.

Collectives™ on Stack Overflow

Insert array data to each table column

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related