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

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/bootstrap@5.0.0/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-wEmeIV1mKuiNpC+IOBjI7aAzPcEZeedi5yW5f2yOq55WWLwNGmvvx4Um1vskeMj0" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0/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.

Answer

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.

Advertisement

Answer