CODEWITHSUNDEEP
phpmultidimensional-arraytransposeinsert-query
Rossitten
Rossitten

Reputation: 1166

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

Upvotes: -1

Views: 65

Answers (3)

mickmackusa
mickmackusa

Reputation: 48041

  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',
  ),
)

Upvotes: 0

Passionate Coder
Passionate Coder

Reputation: 7294

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

Upvotes: 0

Tim Hysniu
Tim Hysniu

Reputation: 1540

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.

Upvotes: 1

Related Questions