The Objective
I am creating a dynamic statement by using AJAX to pass on a string to PHP. I am using Ajax to pass on this string by first creating an array in Javascript of a group of selected checkbox elements, then using array.join to create the statement. There are two separate groups of checkbox elements, one is based on staff location, the other is based on staff department. The final search can either involve one or both categories of checkboxes which will ultimately filter and narrow down the results based on this search criteria.
Here is the Javascript code below - executed once the user cliks on the search button. As you will see, its a loop which goes through the checkboxes to see which ones have been checked, then pushes them into an array, before creating the statement via join method.
document.getElementById("submitFilterSearch").addEventListener("click",()=>{
alert("working");
const filteredDepartments = document.querySelectorAll(".deptCB");
const filteredLocations = document.querySelectorAll(".locCB");
let departmentsArr = [];
let locationArr = [];
//push item values into the arrays to create dynamic SQL query search term, concat array once done and sent through ajax
filteredDepartments.forEach((checkbox, index)=>{
if (checkbox.checked) {
departmentsArr.push(checkbox.value);
}
})
filteredLocations.forEach((checkbox, index)=>{
if (checkbox.checked) {
locationArr.push(checkbox.value);
}
})
let depQuery = departmentsArr.map((value, index)=>{
if (index == 0){
return "d.id = "+value;
} else {
return "OR d.id = "+value;
}
})
let locQuery = locationArr.map((value, index)=>{
if (index == 0){
return "l.id = "+value;
} else {
return "OR l.id = "+value;
}
})
let depQueryStr = depQuery.join(" ");
let locQueryStr = locQuery.join(" ");
$.ajax({
url: "./libs/php/filteredSearch.php",
type: "POST",
dataType: "JSON",
data: {
depFilter: depQueryStr,
locFilter: locQueryStr
},
success: function(success){
console.log(success);
},
error: function(xhr, txts, err){
console.log(err)
}
})
})
Here is what the final joined arrays hold before sending off to PHP
d.id = 2 OR d.id = 3 OR d.id = 9
l.id = 1 OR l.id = 2 OR l.id = 3
Here is the PHP code for creating the dynamic statement
$queryString = 'SELECT p.id, p.lastName, p.firstName, p.jobTitle, p.email,
d.name as department,
l.name as location, l.id as locationID, d.id as departmentID
FROM personnel p
LEFT JOIN department d ON (d.id = p.departmentID)
LEFT JOIN location l ON (l.id = d.locationID)
WHERE ';
$depQuery = $_REQUEST["depFilter"];
$locQuery = $_REQUEST["locFilter"];
if ( isset($_REQUEST["depFilter"]) && isset($_REQUEST["locFilter"]) ){
global $queryString;
$queryString.=$depQuery." AND ".$locQuery;
} else if ( isset($_REQUEST["depFilter"]) && !isset($_REQUEST["locFilter"]) ){
global $queryString;
$queryString.=$depQuery;
} else {
global $queryString;
$queryString.=$locQuery;
}
$result = $conn->query($queryString);
if (!$result) {
$output['status']['code'] = "400";
$output['status']['name'] = "executed";
$output['status']['description'] = "query failed";
$output['data'] = [];
mysqli_close($conn);
echo json_encode($output);
exit;
}
$data = [];
while ($row = mysqli_fetch_assoc($result)) {
array_push($data, $row);
}
$output['status']['code'] = "200";
$output['status']['name'] = "ok";
$output['status']['description'] = "success";
$output['status']['returnedIn'] = (microtime(true) - $executionStartTime) / 1000 . " ms";
$output['data'] = $data;
mysqli_close($conn);
echo json_encode($data);
The Problem: When I select the checkboxes from BOTH categories, the statement works as expecting and brings back the results. However, when I choose only ONE of the categories from the checkboxes, and therefore on only one of the joined arrays is sent through as a value, the response I get through the status is "code: "400", name: "executed", description: "query failed""
Where am I going wrong? I presume the if statement within the PHP code.
Also, perhaps you can already tell from my code and approach, that I am new to using SQL with PHP. I know that SQL injections is also something I need to look into a bit more, so if anyone can give me advice on if this is a safe enough approach, that is also appreciated!