How to bulk insert to database using foreach insert query in mysql

Question

I have an HTML form which I can duplicate to help add multiple rows at a time. This code works but I'm running into a problem that I can't quite fix. The code is supposed to add a row for each data inserted into the database. The problem is chat it is repeating the same row. This is not a duplicate issue. Say, I need three locations, I can add them say, Location A, Location B, Location C.

PROBLEM - Instead of adding the three locations, the query adds Location C three times.The reason why it's adding three times is because I added an option to duplicate the form for bulk inserting data. The issue is that it's adding the same entry meaning it loops correctly (3 times) but it doesn't enter the other two.

if (isset($_POST['save-multiple-data'])) {
if (empty($action)) {
    $action = isset($_GET['action']) ? trim($_GET['action']) : "";
    $id = isset($_GET['id']) ? intval($_GET['id']) : "";

    $shiftdata = array('location' => $_POST['location_id'], 'service' => $_POST['service_id']);

    foreach ($shiftdata as $index => $shifts) {
        $wpdb->insert("wp_appointments", array(
            "location_id" => $_POST['location'],
            "service_id" => $_POST['service']
        ));

        echo $shifts[$index];

        if ($wpdb->insert_id > 0) {
            $msg = "Shifts Added Successfully";
        } else {
            $msg = "Failed to Add Shifts";
        }

        }
    }
}

<div class="col-md-4">
    <div class="form-group mb-2">
        <p>
            <label>Location</label>
                <select name="location" value="<?php echo isset($row_details['location_id']) ? $row_details['location_id'] : ""; ?>">
                        <?php
                        $loc_results = $wpdb->get_results ( "SELECT id, name FROM wp_locations");
                        foreach($loc_results as $locat) {
                        $locat_id=$locat->id;
                        $locat_name=$locat->name;
                        echo '<option value="' . esc_attr( $locat_id ) . '">' . esc_html( $locat_name) . '</option>';
                        }
                        ?>
                </select>
        </p>
    </div>
</div>                                

<div class="col-md-4">
    <div class="form-group mb-2">
        <p>
            <label>Service</label>
                <select name="service" value="<?php echo isset($row_details['service_id']) ? $row_details['service_id'] : ""; ?>">
                        <?php
                        $shift_results = $wpdb->get_results ( "SELECT id, name FROM wp_services");
                            foreach($shift_results as $shift) {
                            $shift_id=$shift->id;
                            $shift_name=$shift->name;
                            echo '<option value="' . esc_attr( $shift_id ) . '">' . esc_html( $shift_name) . '</option>';
                        }
                        ?>
            </select>
        </p>
    </div>
</div>
<script src="https://code.jquery.com/jquery-1.9.1.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.js"></script>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-wEmeIV1mKuiNpC+IOBjI7aAzPcEZeedi5yW5f2yOq55WWLwNGmvvx4Um1vskeMj0" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-p34f1UUtsS3wqzfto5wAAmdvj+osOnFyQFpp4Ua3gs/ZVWx6oOypYoCJhGGScy+8" crossorigin="anonymous"></script>

Js code only helps us with duplicating the form so I didn't include it here.

ADyson · Accepted Answer · 2021-05-12 12:43:38Z

1

A couple of problems:

Your name attributes aren't set up for being able to submit multiple values into the same parameter. You need array syntax in all the names you're going to duplicate.

e.g.

<select name="location[]"

and

<select name="service[]"

foreach ($shiftdata makes no sense because that will loop through the "location" and "service" entries of that associative array, not through the lists within each entry. The $shiftdata array is redundant really, you can simply remove it from the code.

Assuming there will always be an identical number of locations entries as service entries then this will make more sense:

for ($i = 0; $i < count($_POST["location"]; $i++) {
  $wpdb->insert("wp_appointments", array(
      "location_id" => $_POST["location"][$i],
      "service_id" => $_POST["service"][$i]
  ));

  if ($wpdb->insert_id > 0) {
        $msg = "Shift added successfully";
    } else {
        $msg = "Failed to add shift for location ".$_POST["location"][$i]." and service ".$_POST["service"][$i];
    }
}

N.B. Another unrelated potential issue - you include jQuery twice in your page (version 1.9.0 and version 3.6.0). This is unnecessary, inefficient and could cause clashes, crashes or other unexpected behaviour. You should only need one version - in general, keeping the most recent version would be sensible, unless you have a specific reason for requiring the older one.

answered May 12, 2021 at 12:43

ADyson

62.9k16 gold badges88 silver badges100 bronze badges

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

1 Comment

Danstan Ongubo Over a year ago

Perfect solution! Regarding the jQuery appearing twice, for some strange reason, when I remove either of them, the duplicate form button becomes unclickable. Something to sort from my end, maybe a theme/plugin issue. Nice fix.

Collectives™ on Stack Overflow

How to bulk insert to database using foreach insert query in mysql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related