Insert database rows from columns of data from associative array of indexed arrays

Question

I have an array called $works which looks like this:

[
    'type' => [1, 5],
    'description' => [2, 6],
    'hours' => [3, 7],
    'amount' => [4, 8]
]

I need to send all values to Database (MySQL) in two iterations. Something like:

INSERT INTO `works` (`type`, `description`, `hours`, `amount`) VALUES (works[type][0], works[decsription][0], works[hours][0], works[amount][0]);

INSERT INTO `works` (`type`, `description`, `hours`, `amount`) VALUES (works[type][1], works[description][1], works[hours][1], works[amount][1]);

Either use a for loop and then use a counter variable to access each value in the loop or transpose the array and then use a foreach loop — Rizier123, Jun 07 '16 at 02:46
Darren - I know it's not taht hard but somehow I have been struggling for good three hours trying different loop variations. Of course I tried $i++ — Rossitten, Jun 07 '16 at 03:17
@ Rizier123 - mate, thanks a bunch!!! freaking convenient solution in my case!!! "transpose the array and then use a foreach loop" — Rossitten, Jun 07 '16 at 03:26
good threaD on the array transposing: http://stackoverflow.com/questions/797251/transposing-multidimensional-arrays-in-php — Rossitten, Jun 07 '16 at 03:49

score 1 · Accepted Answer · edited Jun 07 '16 at 02:53

for($i=0; $i<count($works['type']); $i++) {
    $query = "INSERT INTO `works` (`type`, `decsripion`, `hours`, `amount`) VALUES ('{works[type][$i]}', '{works[description][$i]}', '{works[hours][$i]}', '{works[amount][$i]}')";
    mysql_query($query);
}

That is provided that you have already connected and selected a database. There are better ways of doing this but this depends on framework you are using. For example, you might want to escape these values before trying to insert them into database.

score 0 · Answer 2 · answered Jun 07 '16 at 03:54

Hi You can try this.

<?php
$works = array('type'=>array(1,5),'description'=>array(2,6),'hours'=>array(3,7),'amount'=>array(4,8));
    $insert_query = 'INSERT INTO works(type,description,hours,amount) values ';
    for($i=0; $i<count($works['type']); $i++) {
                $insert_query .= "('".$works['type'][$i]."','".$works['description'][$i]."','".$works['hours'][$i]."','".$works['amount'][$i]."')," ;

    }

    $query = rtrim($insert_query, ',');
    //echo $query;
    if(!mysqli_query($con,$query)){
        die('Error: ' . mysqli_error($con));
    }else{
        echo "record added";
    }

In this i have used bulk insert into you can echo query if you want to see the query. According to your question I have assumed that all array means type, description etc are of same length so i used $works['type'] to calculate the for loop steps

score 0 · Answer 3 · answered Apr 23 '22 at 09:16

Define a prepared statement before entering your loop.
Count the number of columns by accessing the type subarray and counting its elements.
Bind the integer values (accessed using the counter variable $i) to the prepared statement.
Execute the prepared statement (you can check the affected rows if you like)

Code: (Demo)

$works = [
    'type' => [1, 5],
    'description' => [2, 6],
    'hours' => [3, 7],
    'amount' => [4, 8]
];

if (!empty($works['type'])) {
    $sql = "INSERT INTO works (type, description, hours, amount)
            VALUES (?,?,?,?)";
    $stmt = $mysqli->prepare($sql);
    for ($i = 0, $count = count($works['type']); $i < $count; ++$i) {
        $stmt->bind_param(
            "iiii",
            $works['type'][$i],
            $works['description'][$i],
            $works['hours'][$i],
            $works['amount'][$i]
        );
        $stmt->execute();
    }
}
var_export($mysqli->query("SELECT * FROM works")->fetch_all(MYSQLI_ASSOC));

Output:

array (
  0 => 
  array (
    'id' => '1',
    'type' => '1',
    'description' => '2',
    'hours' => '3',
    'amount' => '4',
  ),
  1 => 
  array (
    'id' => '2',
    'type' => '5',
    'description' => '6',
    'hours' => '7',
    'amount' => '8',
  ),
)

Insert database rows from columns of data from associative array of indexed arrays

3 Answers3