How do I loop through a 3 dimensional array to insert data into the database?

Question

This is my 3 dimensional array.

My $_POST array. These are the values

$_POST = Array ( 
[9] => Array ( [student_id] => 9 [subject_id] => 6 [class_id] => 2 [mark_obtained] => 40 [mark_obtainable] => 100 ) 
[10] => Array ( [student_id] => 10 [subject_id] => 6 [class_id] => 2 [mark_obtained] => 52 [mark_obtainable] => 100 ) 
[11] => Array ( [student_id] => 11 [subject_id] => 6 [class_id] => 2 [mark_obtained] => 30 [mark_obtainable] => 100 ) 
[12] => Array ( [student_id] => 12 [subject_id] => 6 [class_id] => 2 [mark_obtained] => 68 [mark_obtainable] => 100 ) )

How do i loop through it and submit each of the array into the database?

My column names are the keys. "student_id, subject_id, class_id, mark_obtained, mark_obtainable".

I am trying this but i keep getting duplicate entries.`foreach ($_POST as $i => $values1) {

foreach ($values1 as $key => $value) {
    $columns = implode(", ",array_keys($values1));
    $values  = implode(", ", $values1);
    $columns = $columns
    $sql = "INSERT INTO `daily_report`($columns) VALUES ($values)";

    if ($conn->multi_query($sql) === TRUE) {
        echo "New records created successfully <br>";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

looks like a 2D array to me. Will each entry in the main array be a single row in the database? If so then you just need a single foreach loop, and each one will execute an INSERT statement. Should we assume that the array shown in the first snippet is actually the $_POST array? — ADyson
– ADyson, Commented May 6, 2018 at 19:37

Felix · Accepted Answer · 2018-05-07 16:43:31Z

1

You have to echo the sql variable inside the foreach loop like this.

foreach ($_POST as $data => $values) {

echo $sql = "INSERT INTO `daily_report` (student_id, subject_id, class_id,mark_obtained,mark_obtainable,daily_report.date)
VALUES (".$values['student_id'].", ".$values['subject_id'].", ".$values['class_id'].", ".$values['mark_obtained'].",".$values['mark_obtainable'].", now())";

// echo $sqlupdate1 = "UPDATE table SET commodity_quantity=$qty WHERE user='".$rows['user']."' ";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Concatenate the variables while echoing.

answered May 7, 2018 at 16:43

Felix

288 bronze badges

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

1 Comment

ADyson Over a year ago

This is correct and will work, but note that this is extremely vulnerable to SQL injection attacks. Really, parameterised queries and prepared statements should be used instead. See bobby-tables.com for an explanation of the risks, and also some examples of writing queries safely using PHP with mysqli or PDO

Valentin Emil Cudelcu · Accepted Answer · 2018-05-06 20:09:32Z

0

You don't need a 3 dimensional array. You can use a 2 dimensional array and by using foreach you can insert them one by one. Here is an example:

 $data_array = [
    [
        'student_id' => 9,
        'subject_id' => 6, 
        'class_id' => 2,
        'mark_obtained' => 40,
        'mark_obtainable' => 100    
    ],
        [
        'student_id' => 10,
        'subject_id' => 6, 
        'class_id' => 2,
        'mark_obtained' => 52,
        'mark_obtainable' => 100    
    ],
        [
        'student_id' => 11,
        'subject_id' => 6, 
        'class_id' => 2,
        'mark_obtained' => 30,
        'mark_obtainable' => 100    
    ],
        [
        'student_id' => 12,
        'subject_id' => 6, 
        'class_id' => 2,
        'mark_obtained' => 30,
        'mark_obtainable' => 100    
    ],
];

foreach($data_array as $data) {
$sql = "INSERT INTO `daily_report` (student_id, subject_id, class_id,mark_obtained,mark_obtainable)
VALUES ($data['student_id'], $data['subject_id'], $data['class_id'], $data['mark_obtained'],$data['mark_obtainable'])";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

edited May 6, 2018 at 20:09

answered May 6, 2018 at 19:56

Valentin Emil Cudelcu

6891 gold badge11 silver badges27 bronze badges

1 Comment

Strawberry Over a year ago

I'm not convinced that executing a query inside a loop is such a great idea. And see about prepared statements- there's some really clever examples of their use in conjunction with multi-row inserts.

Collectives™ on Stack Overflow

How do I loop through a 3 dimensional array to insert data into the database?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related