Hi to Respected members!
I have a form with fields country, state, city and rate fields and two hidden fields zzz1 and zzz2. Currently form auto filling the value of rate, zzz1 and zzz2 fields on the basis of country city and state selection where as Consultant is selected by user from drop down list. all working fine till this point.
Now I am looking to make zzz1 and zzz2 dependent on Consultant field. (for example if I select consultant A, then autofill display zzz1 = 80 and zzz2 = 20 and if I select Consultant B, then autofill should display zzz1= 100 and zzz2 = 0 and so on as per Consultant selection) where as rate should remain independent of consultant selection. well I tried it via case statement in sql , where its working but dynamically it will be failed. I want to get an idea that how can i do at front end, may be controlling autofil with more than one key if its possible ???
Below is my code
HTML Form
<table class="table table-bordered">
<thead class="table-success" style="background-color: #3fbbc0;">
<tr>
<th width="15%"><center>Country</th>
<th width="15%"><center>State</th>
<th width="10%"><center>City</th>
<th width="10%"><center>Consultant</th>
<th width="10%"><center>Rate</th>
<button type="button" class="btn btn-sm btn-success" onclick="BtnAdd()">Add Item</button>
</th>
</tr>
</thead>
<tbody id="TBody">
<tr id="TRow" class="d-none">
<td><Select class="country form-control text-end" name="country[]" id = "country" >
<option value=""> Select Type</option>
<?php
include('db1.php');
$query = "select * from country";
// $query = mysqli_query($con, $qr);
$result = $con->query($query);
if ($result->num_rows > 0) {
while ($row = mysqli_fetch_assoc($result)) {
?>
<option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>
<?php
}
} ?> </select> </td>
<td><Select class="state form-control text-end" name="state[]" id = "state">
<option value="">select Service</option></select></td>
<td><Select class="city form-control text-end" name="city[]" id = "city" onchange="GetDetail(this.closest('tr'))">
<option value="">Select Machine</option></select></td>
<td><Select class="form-control text-end" name="docname[]" id="iii" required onfocus="Calc(this);">
<option value="">Select Consult</option>
<?php
include('db.php');
$sql = mysqli_query($con,"SELECT * FROM consultant");
while($row=mysqli_fetch_array($sql))
{
echo '<option value="'.$row['consultant_name'].'">'.$row['consultant_name'].'</option>';
} ?> </select></td>
<td><input type="text" class="price form-control text-end" name="price1[]" id="ddd" onfocus="Calc(this);" readonly style="background-color: #3fbbc0;"></td>
<input type="hidden" class="zzz1 form-control text-end" name="zzz1[]" id="zzz1" ><input type="hidden" class="zzz2 form-control text-end" name="zzz2[]" id="zzz2" >
<td class="NoPrint"><button type="button" class="btn btn-success" style="line-height: 1;" onclick="BtnDel(this)">x</button></td>
</tr> </tbody> </table>
Here is script code which handle autofill
<script>
$(document).ready(function () {
$(document).on("change", ".country", function(){
// Get the current row element
let row = $(this).closest("tr");
// Get the country id from the current select element
let country_id = $(this).val();
// Use AJAX to send a request to the server-side script
$.ajax({
method: "POST",
url: "response.php",
data: {
id: country_id
},
datatype: "html",
success: function(data) {
// Find the state select element in the same row and update its options
row.find(".state").html(data);
// Find the city select element in the same row and clear its options
row.find(".city").html('<option value="">Select Machine</option>');
row.find('.price').val('');
row.find('.zzz1').val('');
row.find('.zzz2').val('');
}
});
});
// When the user changes the value of the state select element
$(document).on("change", ".state", function(){
// Get the current row element
let row = $(this).closest("tr");
// Get the state id from the current select element
let state_id = $(this).val();
// Use AJAX to send a request to the server-side script
$.ajax({
method: "POST",
url: "response.php",
data: {
sid: state_id
},
datatype: "html",
success: function(data) {
// Find the city select element in the same row and update its options
row.find(".city").html(data);
row.find('.price').val('');
row.find('.zzz1').val('');
row.find('.zzz2').val('');
}
});
});
$(document).on("change", ".city", function(){
// Get the current row element
let row = $(this).closest("tr");
// Get the state id from the current select element
let city_id = $(this).val();
if (city_id.length == 0) {
row.find('.price').val('');
row.find('.zzz1').val('');
row.find('.zzz2').val('');
//return;
} else {
// Use AJAX to send a request to the server-side script
$.ajax({
url: 'response.php',
type: 'POST',
dataType: 'JSON',
data: {cid: city_id} ,
success: function(response) {
row.find('.price').val(response.price);
row.find('.zzz1').val(response.zzz1);
row.find('.zzz2').val(response.zzz2);
}
});
}
});
});
</script>
Script for dynamically adding new row in form
<script type="text/javascript">
function GetPrint()
{
/*For Print*/
window.print();
}
function BtnAdd()
{
/*Add Button*/
var v = $("#TRow").clone().appendTo("#TBody") ;
$(v).find("input").val('');
$(v).find("input").autocomplete({
source: 'backend-script.php'
});
$(v).removeClass("d-none");
$(v).find("th").first().html($('#TBody tr').length - 1);
}
Below is response.php file which get data of rate, zzz1 and zzz2 from database
<?php
include_once 'db1.php';
if (!empty($_POST["id"])) {
$id = $_POST['id'];
$query = "SELECT id, state FROM state WHERE country_id=$id";
$result = mysqli_query($con, $query);
if ($result->num_rows > 0) {
echo '<option value="">Select Service</option>';
while ($row = mysqli_fetch_assoc($result)) {
echo '<option value="' . $row['id'] . '">' . $row['state'] . '</option>';
}
}
}
if (!empty($_POST['sid'])) {
$id = $_POST['sid'];
$query1 = "SELECT id, city FROM city WHERE state_id=$id";
$result1 = mysqli_query($con, $query1);
if ($result1->num_rows > 0) {
echo '<option value="">Select Machine</option>';
while ($row = mysqli_fetch_assoc($result1)) {
echo '<option value="' . $row['id'] . '">' . $row['city'] . '</option>';
}
}
}
if(!empty($_POST['cid'])){
$city_id = $_POST['cid'];
$query_asset = "SELECT price, zzz1, zzz2 FROM assets WHERE city_id=$city_id";
$result_asset = mysqli_query($con, $query_asset);
if ($result_asset->num_rows > 0) {
$row_asset = mysqli_fetch_assoc($result_asset);
$response = array('price' => $row_asset['price'], 'zzz1' => $row_asset['zzz1'], 'zzz2' => $row_asset['zzz2']);
}
echo json_encode($response);
exit;
}
?>
Thanks for the support which respected member provided me during my educational project completion! Currently I am trying to improve my form more and more.