insert multiple (Multi select menu) data into MySQL

Question

I have a set of data contains a (Multi select menu text) and 4 input fields

<select class="form-control m color_select_thread" multiple="multiple" required>
<option value="" disabled selected >Select Color</option>
<option value="" >white</option>
<option value="" >black</option>
<option value="" >yellow</option>
</select>

<input type="number" step=".01" class="form-control countPiece_thread" required>

<input type="text" class="form-control border border-warning total_thread" required disabled>

<input type="text" class="form-control border border-warning hadr_thread" required>

this set of data is repeated more than once according to the user's choice

I have push the variables normally to jQuery

let threads = [];
let colorSelect = [];
let countPiece = [];
let total = [];
let hadr = [];

$(".threads-value").each(function() {
  threads.push($(this).val());
});

$(".color_select_thread option:selected").each(function() {
  let text = $(this).text();
  //let tempVal = $(".color_select_thread").text();
  if (text.indexOf(text) >= 0 && colorSelect.indexOf(text) < 0) {
    colorSelect.push(text);
  } else if (text.indexOf(text) < 0 && colorSelect.indexOf(text) >= 0) {
    colorSelect.splice(colorSelect.indexOf(text), 1);
  }
  //  colorSelect.push($(this).text());
});

$(".total_thread").each(function() {
  total.push($(this).val());
});

$(".hadr_thread").each(function() {
  hadr.push($(this).val());
});

$.ajax({
  url: "requests/ordersAddAccessories.php",
  dataType: 'text',
  data: {
    "action": "add_threads",
    "order": <?php echo $order_id; ?>,
    "threads": threads,
    "colorSelect": colorSelect,
    "colorNumber": colorNumber,
    "countPiece": countPiece,
    "total": total,
    "hadr": hadr,
    "last": last
  },

And I wrote the back end code to enter the values ​​into the database

$order = $_POST['order'];
$colorSelect = $_POST['colorSelect'];
$threads = $_POST['threads'];
$hadr = $_POST['hadr'];
$total = $_POST['total'];
$last = $_POST['last'];

for ($i =0; $i < count($threads); $i++){
    foreach ($colorSelect AS $meriag){
        $result = $conn->query("INSERT INTO order_threads(order_id,
         thread_id,
         color_select,
         colorNumber_input,
         countPiece ,
         total,
        hadr,
        egmaly ) 
        VALUES
        (
        '$order',
        '$threads[$i]', 
        '$meriag',  // This is Multi Select menu data
        '$colorNumber[$i]',
        '$countPiece[$i]',
        '$total[$i]',
        '$hadr[$i]',
        '$last[$i]')");   
    }
}

$result2 = $conn->query("UPDATE orders SET thread=1 WHERE id='$order'");

if ($result2){
    echo 'done';
}

The code work well to all inputs expect the data of multi select menu

it is not inserted data successfully

How could i fix that ?

Answer 1

You need to make colorSelect a 2-dimensional array. The first dimension is for each select, the second dimension is the list of choices in that select.

let colorSelect = $(".color_select_thread").map(function() {
    return [$(this).find(":selected").map(function() {
        return $(this).text();
    }).get()];
}).get();

The extra array around the return value if the inner map() is needed to work around jQuery .map() automatically flattening nested arrays. See Is there a jQuery map utility that doesn't automically flatten?

Then your loop in PHP will look like this (I've also converted the code to use prepared statemts to prevent SQL injection).

$order = $_POST['order'];
$colorSelects = $_POST['colorSelect'];
$threads = $_POST['threads'];
$hadrs = $_POST['hadr'];
$totals = $_POST['total'];
$lasts = $_POST['last'];
$colorNumbers = $_POST['colorNumber'];
$countPieces = $_POST['countPiece'];

$stmt = $conn->prepare("INSERT INTO order_threads(order_id, thread_id, color_select, colorNumber_input, countPiece , total, hadr, egmaly ) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param("ssssssss", $order, $thread, $meriag, $colorNumber, $countPiece, $total, $hadr, $last);

for ($i =0; $i < count($threads); $i++){
    $thread = $threads[$i];
    $colorNumber = $colorNumbers[$i];
    $countPiece = $countPieces[$i];
    $total = $totals[$i];
    $hadr = $hadrs[$i];
    $last = $lasts[$i];
    foreach ($colorSelects[$i] AS $meriag) {
        if (!$stmt->execute()) {
            die("INSERT failed: " . $conn->error);
        }
    }
}

$stmt = $conn->prepare("UPDATE orders SET thread = 1 WHERE id = ?");
$stmt->bind_param("s", $order);
if (!$stmt->execute()) {
    die("UPDATE failed: " . $conn->error);
}
echo 'done';