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

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]);
phpmultidimensional-arraytransposeinsert-query


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

  1. Define a prepared statement before entering your loop.
  2. Count the number of columns by accessing the type subarray and counting its elements.
  3. Bind the integer values (accessed using the counter variable $i) to the prepared statement.
  4. 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',
  ),
)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Darren
Solution 2 Passionate Coder
Solution 3 mickmackusa

Related Questions