How to bulk insert dropdown select data into mysql

The code below is for inserting data into mysql tables. I am only able to insert data successfully into two of the three.

A headsup: Table name – wp_form_inputs which stores the form_id and the ids of the dropdown selects Then this one stores the options of the selects above

Where I am inserting data into:

wp_appointments - Successful
wp_appointment_customers - Successful
wp_appointment_custom_data - Not Successful

The 3rd table wp_appointment_custom_data which won’t insert. How saved data looks like.

CODE

global $wpdb;
$msg = '';
$action = isset($_GET['action']) ? trim($_GET['action']) : "";
$id = isset($_GET['id']) ? intval($_GET['id']) : "";

$row_details = $wpdb->get_row(
        $wpdb->prepare(
                "SELECT * from wp_appointments WHERE id = %d", $id
        ), ARRAY_A
);
if (isset($_POST['save-multiple-data'])) {
if (empty($action)) {
    $action = isset($_GET['action']) ? trim($_GET['action']) : "";
    $id = isset($_GET['id']) ? intval($_GET['id']) : "";

        for ($i = 0; $i < count($_POST["location"]); $i++) {
//Table 1 - Success
  $wpdb->insert("wp_appointments", array(
      "location_id" => $_POST["location"][$i],
      ...
  ));
//Table 2 - Success
  $wpdb->insert("wp_appointment_customers", array(
      "appointment_id" => $wpdb->insert_id,
      "customer_id" => '1',
      ...
  ));
$fields = [
      0 => 'shifttype[]',
      1 => 'shiftstype[]',
      2 => 'facilitytype[]',
];

$insertId = $wpdb->insert_id;
foreach ($fields as $formInputId => $inputValueKey) {
  $wpdb->insert("wp_appointment_custom_data", array(
      "appointment_id" => $insertId,
      "customer_id" => '1',

      "form_input_id" => $formInputId,
      "input_value" => $_POST[$inputValueKey][$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];
    }

    }
}

FORM

<div class="col-md-3">
    <div class="form-group mb-2">
        <label>Location</label> <br>
        <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>
    </div>
</div>
<div class="col-md-3">
    <div class="form-group mb-2">
        <label>Shift Sub Type</label> <br>
        <select name="shifttype[<?php echo $form_input_id;?>]" value="<?php echo isset($row_details['input_value']) ? $row_details['input_value'] : ""; ?>">
            <?php
            $shiftype_results = $wpdb->get_results ( "SELECT id, form_input_id, title FROM wp_form_input_choices WHERE form_input_id = '1'");
            foreach($shiftype_results as $shiftype_result) {
            $shiftype_result_id=$shiftype_result->id;
            $shiftype_result_title=$shiftype_result->title;

            echo '<option value="' . esc_attr( $shiftype_result_id ) . '">' . esc_html( $shiftype_result_title) . '</option>';
            }
            ?>
        </select>
    </div>
</div>
<div class="col-md-3">
    <div class="form-group mb-2">
        <label>Shift Type</label> <br>
        <select name="shiftstype[<?php echo $form_input_id;?>]" value="<?php echo isset($row_details['input_value']) ? $row_details['input_value'] : ""; ?>">
            <?php
            $shiftstype_results = $wpdb->get_results ( "SELECT id, form_input_id, title FROM wp_form_input_choices WHERE form_input_id = '2'");
            foreach($shiftstype_results as $shiftstype_result) {
            $shiftstype_result_id=$shiftstype_result->id;
            $shiftstype_result_title=$shiftstype_result->title;

            echo '<option value="' . esc_attr( $shiftstype_result_id ) . '">' . esc_html( $shiftstype_result_title) . '</option>';
            }
            ?>
        </select>
    </div>
</div>
<div class="col-md-3">
    <div class="form-group mb-2">
        <label>Facility Type</label> <br>
        <select name="facilitytype[<?php echo $form_input_id;?>]" value="<?php echo isset($row_details['input_value']) ? $row_details['input_value'] : ""; ?>">
            <?php
            $facilitytype_results = $wpdb->get_results ( "SELECT id, form_input_id, title FROM wp_form_input_choices WHERE form_input_id = '3'");
            foreach($facilitytype_results as $facilitytype_result) {
            $facilitytype_result_id=$facilitytype_result->id;
            $facilitytype_result_title=$facilitytype_result->title;

            echo '<option value="' . esc_attr( $facilitytype_result_id ) . '">' . esc_html( $facilitytype_result_title) . '</option>';
            }
            ?>
        </select>
    </div>
</div>

Unable find what I’m doing wrong. JS code duplicates the form for bulk insert. I didn’t see the need to add it.

Answer

An array assignment with same key will override previous assignment

  $wpdb->insert("wp_appointment_custom_data", array(
      "appointment_id" => $wpdb->insert_id,
      "customer_id" => '1',

      "form_input_id" => '1', // overridden by 3rd form_input_id
      "form_input_id" => '2', // overridden by 3rd form_input_id
      "form_input_id" => '3',
      "input_value" => $_POST["shifttype"][$i],    // overridden by 3rd input_value
      "input_value" => $_POST["shiftstype"][$i],   // overridden by 3rd input_value
      "input_value" => $_POST["facilitytype"][$i],

  ));

you have to insert three times respective to input types

$fields = [
      1 => 'shifttype',
      2 => 'shiftstype',
      3 => 'facilitytype',
];

$insertId = $wpdb->insert_id;
foreach ($fields as $formInputId => $inputValueKey) {
  $wpdb->insert("wp_appointment_custom_data", array(
      "appointment_id" => $insertId,
      "customer_id" => '1',

      "form_input_id" => $formInputId,
      "input_value" => $_POST[$inputValueKey][$i],
  ));
}

Advertisement

Answer