I am working on a PHP code with MYSQL. It involves selecting data from the database using a dropdown list with AJAX and displaying the results on the screen. I have three dropdown lists that are dependent on each other and each dropdown has its own table to select from:
- site_info
- owner_info
- company_info
Note: The third table only has 6 values.
The issue arises when the user selects an option from the first dropdown, causing the third dropdown to display results from the second table.
If anyone could assist me in resolving this problem, it would be greatly appreciated.
search_info_location.php
<td><select id="site_name" name = "site_name">
<option value="">Select Site</option>
<?php
$query_site_name =$wpdb->get_results("select DISTINCT
i.siteNAME,
i.ownerID,
i.companyID,
o.ownerNAME,
x.companyNAME
from site_info i
LEFT
JOIN owner_info o
on i.ownerID = o.ownerID
LEFT
JOIN company_info x
on i.companyID = x.companyID
");
foreach($query_site_name as $row)
{
echo "<option value = '".$row ->companyID.",".$row ->ownerID.",".$row ->siteNAME."'>".$row->siteNAME."</option>";
}
?>
</select></td>
<!--create dropdown list owner names-->
<td><select id="owner_name" name ="owner_name">
<option value="">Select Owner</option>
</select></td>
<!--create dropdown list Company names-->
<td><select id="Company_name" name ="Company_name">
<option value="">Select Company</option>
</select></td>
$sql = $wpdb->prepare("select i.siteID
, i.siteNAME
, i.equipmentTYPE
, c.latitude
, c.longitude
, c.height
, o.ownerNAME
, o.ownerCONTACT
, x.companyNAME
, y.subcontractorCOMPANY
, y.subcontractorNAME
, y.subcontractorCONTACT
from site_info i
LEFT
JOIN owner_info o
on i.ownerID = o.ownerID
LEFT
JOIN company_info x
on i.companyID = x.companyID
LEFT
JOIN subcontractor_info y
on i.subcontractorID = y.subcontractorID
LEFT JOIN site_coordinates2 c
on i.siteID=c.siteID
where
i.siteNAME = %s
AND
o.ownerNAME = %s
AND
x.companyNAME = %s
",$site_name,$owner_name,$company_name);
$query_submit =$wpdb->get_results($sql, OBJECT);
<script type="text/javascript">
// make Dropdownlist depend on each other
$(document).ready(function(){
// depend owner name on site name
$('#site_name').change(function(){
var arrayId = $(this).val().split(",");
if(arrayId != ""){
var ownerID = arrayId[0];
var companyID = arrayId[1];
$.ajax({
url:"<?php echo get_stylesheet_directory_uri(); ?>/dropdown_fetch_owner.php",
method:"POST",
data:{ownerID:ownerID,companyID:companyID},
dataType:"text",
success:function(data){
var Response = data.split("--");
$('#owner_name').html(Response[2]);
$('#Company_name').html(Response[4]);
}
});
}
});
});
</script>
dropdown_fetch.php
<?php
include_once($_SERVER['DOCUMENT_ROOT'].'/wordpress/wp-load.php');
global $wpdb,$Owner_name,$Company_name;
$sql =$wpdb->get_results("select ownerID, ownerNAME from owner_info where ownerID = '".$_POST['ownerID']."' ORDER BY ownerNAME");
$Owner_name = '--Owner--';
foreach($sql as $row){
$Owner_name.= "<option value ='".$row ->ownerID."'>".$row->ownerNAME."</option>";
}
echo $Owner_name;
$sql =$wpdb->get_results("select companyID, companyNAME from company_info where companyID = '".$_POST['companyID']."' ORDER BY companyNAME");
$Company_name = '--Company--';
var_dump($sql);
foreach($sql as $row){
$Company_name.= "<option value ='".$row ->companyID."'>".$row->companyNAME."</option>";
}
echo $Company_name;
exit();
?>
https://i.stack.imgur.com/pcG37.png
The maximum number of the companyID must be 6, however, it displays 166, which exists in the owner_info table.