Mysql query for distinct multiple column for select dropdown list

[Senthilkumar]

Dear Team,

I have a data table in my PHP, which is data getting from databse. In this table, I have four drop-down lists on my tablehead for filtering the data. In a single query, I am updating all four drop-down lists using an Ajax request. All four drop-down lists are getting updated with repeated values. Please refer the below output image

I want the list to update distinct values instead of being repeated.

My script is 

<script type="text/javascript" language="javascript">
				$(document).ready(function () {

					dropdown_load_data();
				});

				

				function dropdown_load_data() {

					var Model = document.getElementById("Model").value;
					var CustomerName = document.getElementById("CustomerName").value;
					var ModelGroup = document.getElementById("ModelGroup").value;
					var ModelSubGroup = document.getElementById("ModelSubGroup").value;
					$.ajax({
						url: 'dropdownOrder.php',
						type: 'POST',
						dataType: 'JSON',
						data: { Model: Model, CustomerName: CustomerName, ModelGroup: ModelGroup, ModelSubGroup: ModelSubGroup },
						success: function (response) {
							var len = response.length
							
							for (var i = 0; i < len; i++) {
								var Model = response[i].Model;
								var CustomerName = response[i].CustomerName;
								var ModelGroup = response[i].ModelGroup;
								var ModelSubGroup = response[i].ModelSubGroup;
								
								$("#Model").append("<option>" + Model + "</option>");
								$("#CustomerName").append("<option>" + CustomerName + "</option>");
								$("#ModelGroup").append("<option>" + ModelGroup + "</option>");
								$("#ModelSubGroup").append("<option>" + ModelSubGroup + "</option>");
							}
							
						}
					})

				}


				$(document).on('change', '#Model', function () {					
					dropdown_load_data();
				});

				$(document).on('change', '#CustomerName', function () {					
					dropdown_load_data();
				});

				$(document).on('change', '#ModelGroup', function () {					
					dropdown_load_data();
				});

				$(document).on('change', '#ModelSubGroup', function () {
					dropdown_load_data();
				});
				
				});

</script>

dropdownOrder.php 

<?php

include("../connection.php");
$column = array("id", "Model", "MachineSlNo", "CustomerID", "CustomerName", "MachineStateCode", "MachineStateName", "MachineDistrictCode", "MachineDistrictName", "InstallationDate", "ModelGroup", "ModelSubGroup");

$where_columns = ['Model' => 'Model', 'CustomerName' => 'CustomerName', 'ModelGroup' => 'ModelGroup', 'ModelSubGroup' => 'ModelSubGroup'];

$where_terms = [];

foreach ($where_columns as $col => $input) {
	if ($_POST[$input] == !'') {
		$where_terms[] = "$col= '$_POST[$input]' ";
	}
}

$where = '';
if (!empty($where_terms)) {
	$where = 'WHERE ' . implode(' AND ', $where_terms);
} else {
	$where = 'WHERE 1 ';
}

$query = "SELECT * FROM machinemaster $where";
$result = mysqli_query($conn, $query);


while ($row = mysqli_fetch_array($result)) {
	$Model = $row['Model'];
	$CustomerName = $row['CustomerName'];
	$ModelGroup = $row['ModelGroup'];
	$ModelSubGroup = $row['ModelSubGroup'];

	$return_arr[] = array(
		"Model" => $Model,
		"CustomerName" => $CustomerName,
		"ModelGroup" => $ModelGroup,
		"ModelSubGroup" => $ModelSubGroup,
	);
}
echo json_encode($return_arr);

?>

My table is 

<table id="product_data" class="table table-striped table-bordered" style="width:auto;margin-left:1%;margin-right:1%;overflow-x:scroll;white-space:nowrap">
				<thead class="bg-light">
					<tr>
						<th>S.No</th>
						<th>
							<select name="category" id="Model" class="form-control form-select" style="width:auto">
								<option value="">Model</option>
							</select>
						</th>
						<th style="white-space:nowrap">Machine S.No</th>
						<th>Customer ID</th>
						<th>
							<select name="category" id="CustomerName" class="form-control form-select">
								<option value="">Customer Name</option>
							</select>
						</th>
						<th>State</th>
						<th>District</th>
						<th>Commissionning Date</th>
						<th>
							<select name="category" id="ModelGroup" class="form-control form-select" style="width:auto">
								<option value="">Model Group</option>
							</select>
						</th>
						<th>
							<select name="category" id="ModelSubGroup" class="form-control form-select" style="width:auto">
								<option value="">Model Sub Group</option>
							</select>
						</th>

					</tr>
				</thead>
</table>

 

Can anyone help with how to distinct the dropdown list?

Edited December 6, 2023 by Senthilkumar

[requinix]

Right now, you will end up with a situation where someone is able to pick a combination of 4 that does not exist in your database.

You can avoid that with either:

(a) Don't do all 4 at once. Do the first, then when the user makes a choice you update the next, then the third, then the fourth.
(b) Do all 4 at once, but you need to add some Javascript that will ensure the only options available for 2, 3, and 4 are the valid options according to the earlier choice(s).

What would you rather do?

[Senthilkumar]

i would like to do option b