I am having several issues with a web based dynamic form that I am building.
My most frustrating problem at the moment, is that I can get a manufacturer database to populate the first, #bodyman dropdown, but I then need that first dropdown to populate a second, #bodytype dropdown based on the #bodyman dropdown selection, and then a third#bodycolor dropdown based on the selection from the #bodytype dropdown.
At the moment I'm trying to work through it step by step and I just cannot get it to return the products from the database. I am using a combination of PHP, AJAX, and Javascript to run it, but this may not be the best way. The final result is meant to be on an intranet site.
The "include "config.php" code is included in the header and seems to be functioning properly because the manufacturer dropdown is showing the correct options. Watching the variables, it looks like bodyman_id is returning a correct value for the manufacturer input. I just get the feeling it's not working because of some mistake on my end and can't seem to figure out why it's not working properly. I have the database currently set up so that the manufacturer IDs are numbers and then the paver type has an ID column that lists the corresponding manufacturer number to the paver. If there is an easier or better way to dynamically call info in this way, I'm open to it, this is just a way that I know has worked before.
This is the source code I currently have:
<?php
$sql = "SELECT * FROM hardscapemfr where Pavers IS NOT NULL AND Pavers<>'';";
$stmt = $con->prepare($sql);
$stmt->execute();
$result=$stmt->get_result();
?>
<label>Manufacturer</label>
<select id="bodyman" onChange="getPavers(this.value);">
<option value="0">N/A</option>
<?php
while ($row = $result->fetch_assoc()){
$id=$row['ID'];
$name=$row['Pavers'];
echo "<option value=".$id.">".$name."</option>";
}?>;
</select>
</div>
<div class="col-2">
<label>Paver Type</label>
<select id="bodytype" onChange="getColor(this.value);">
<option value="0">Select Type</option>
</select>
</div>
<div class="col-2">
<label>Color</label>
<select id="bodycolor" style="width: auto">
<option value="0">Select Color</option>
</select>
</div>
<div class="col-2">
<label>Orientation</label>
<select id="bodyorientation" style="width: auto">
<option value="N/A">N/A</option>
<option value="Running Bond">Running Bond</option>
<option value="Herringbone">Herringbone</option>
<option value="Circular">Circular</option>
<option value="Basket Weave">Basket Weave</option>
</select>
</div>
Javascript
function getPavers (bodyman_id){
var paverel = document.getElementById('bodytype');
var colorel = document.getElementById('bodycolor');
paverel.innerHTML="";
colorel.innerHTML="";
var paveropt = document.createElement('option');
paveropt.value = 0;
paveropt.innerHTML = '--Select Paver--';
paverel.appendChild(paveropt);
var coloropt = document.createElement('option');
coloropt.value = 0;
coloropt.innerHTML = '--Select Color--';
colorel.appendChild(coloropt);
var xhttp = new XMLHttpRequest();
xhttp.open("$_POST","ajaxfile.php", true);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.onreadystatechange = function(){
if (this.readyState == 4 && this.status == 200) {
try {
var response = JSON.parse(this.responseText);
}
catch (error) {
console.log("Error parsing JSON:", error, data);
}
var len = 0;
if(response != null) {
len = response.stringValue;
}
if(len > 0){
for(var i=0; i<len; i++){
var id = response[i].id;
var name = response[i].name;
var opt = document.createElement('option');
opt.value = "id";
opt.innerHTML = "name";
paverel.appendChild(opt);
}
}
}
};
var data = {request: 'getPavers',bodyman_id: bodyman_id};
xhttp.send(JSON.stringify(data));
}
AJAX file
<?php
include 'config.php';
$postData = json_decode(file_get_contents("php://input"));
$request = "";
if(isset($postData->request)){
$request = $postData->request;
}
//Get Paver Type
if($request == 'getPavers'){
$bodyman_id = 0;
$result = array();$data=array();
if(isset($postData->ID)){
$id=$postData->ID;
$sql = "SELECT * from belgard where Pavers IS NOT NULL AND Pavers<>'';";
$stmt = $con->prepare($sql);
$stmt->bind_param();
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()){
$id = $row['ID'];
$name = $row['Pavers'];
$data[] = array(
"ID" => $id,
"Pavers" => $name
);
}
}
echo json_encode($data);
die;
}
I've got a couple of questions and one suggestion:
If you look at the Network tab in your browsers developer tools, can you what response the request to ajaxfile.php is returning? Is it an error or maybe a successful response but with no data? That may tell you that the problem lies there.
As far as this part of ajaxfile.php:
$bodyman_id = 0;
$result = array();$data=array();
if(isset($postData->ID)){
$id=$postData->ID;
It looks like you're checking if the post data from the request has a property named ID but you're JS is sending bodyman_id in the request body. Additionally, your SQL query in ajaxfile.php doesn't seem to be using the ID information, and I'd assume you'd have something like WHERE ID = bodyman_id or something along those lines. (and I know it's for an intranet, but be VERY careful about inserting user supplied strings in your SQL queries. Gives someone the opportunity to perform an SQL injection attack that could drop the table or something).
As far as your JS request, you could use the fetch API instead of XMLHttpRequest. I think it brings a cleaner format for making a requests, like so:
async function getPavers(bodyman_id) {
// ... Here are all of your variable declarations.
const getPaverData = await fetch('ajaxfile.php', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({
request: 'getPavers',
bodyman_id: bodyman_id
})
})
const paverData = await getPaverData.json()
paverData.forEach((paverData, index) => {
const opt = document.createElement('option');
opt.value = paverData.id;
opt.innerHTML = paverData.name;
paverel.appendChild(opt);
})
}
DISCLAIMER: I'm pretty sure that's fine, I didn't try running it.
You'll notice it's using async/await to make sure it doesn't run a synchronously, but that isn't required if you want it to run async (though I won't suggest that without testing it so there isn't any behavior I can't diagnose).
Lastly, you have just the file name in the request but it will be treated like a relative link (https://yourdomain.com/current/url/ajaxfile.php) and if the file isn't located there then the request will fail. You may want to change it to something more precise (/uri/of/file_location/ajaxfile.php), but if it's hitting the file correctly then it isn't strictly necessary.