I have 4 select drop-downs each to filter by a particular field and want to filter the table as soon as a user selects an option in either select.
Note that this table gets its data from a server-side json file.
I am trying to use ajax to call the same PHP file that gets the table data and stores it in the json file then place conditionals and alter the query slightly by adding a WHERE clause for the select data:
/* drop-down menu variables */
$dd_fname = $_POST["first_name"];
$dd_lname = $_POST['Last_name'];
$dd_category = $_POST['category'];
$dd_group = $_POST["group"];
if (isset($_POST['first_name'])) { //filter. THIS IS WHAT IS GETTING IGNORED
$stmt = "SELECT first_name, Last_name, category, group\n"
. "\n"
. "FROM users
. "\n"
. "WHERE first_name =$dd_fname";
$result = mysqli_query($mysqli, $stmt);
$num_rows = mysqli_num_rows($result);
}
else { //no filter. initial data. THIS ALWAYS EXECUTES NO MATTER WHAT
$stmt = "SELECT first_name, Last_name, category, group\n"
. "\n"
. "FROM users";
$result = mysqli_query($mysqli, $stmt);
$num_rows = mysqli_num_rows($result);
}
/*
CODE TO ADD TO JSON FILE AND ECHO TO TABLE IS AFTER THIS
*/
So on the initial onload the table is displayed. But when the user selects an option to filter I want ajax to update the drop down variables in the php file (as onload they are empty) to trigger the sql query that has the isset and overwriting the json with filtered results. My issue is that this isset block gets ignored and the initial unfiltered data is always being returned.
/*AJAX Code runs onclick for now*/
function filter_drop_down() {
//get all 4 select tag data
var first_name = document.getElementById('first_name').value;
var last_name = document.getElementById('last_name').value;
var category = document.getElementById('category').value;
var group = document.getElementById('group').value;
if ((first_name || last_name || category || group) === '') {
alert("Select at least one option to use the filter");
return false;
}
//send the filter variables to the server
var xhr;
if (window.XMLHttpRequest) { // Mozilla, Safari, ...
xhr = new XMLHttpRequest();
} else if (window.ActiveXObject) { // IE 8 and older
xhr = new ActiveXObject("Microsoft.XMLHTTP");
}
var data = "dd_fname=" + first_name + "&dd_lname=" + last_name + "&dd_categoryr=" + category + "&dd_group=" + group;
xhr.open("POST", "fileThatGetsDataForTable.php", true);
/************************************************/
xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
xhr.send(data);
}
UPDATE: I am having a problem with getting the table to display the data of the new query via AJAX:
if (!empty($_POST['first_name'])) { //filter. ***FIXED***
$stmt = "SELECT first_name, Last_name, category, group\n"
. "\n"
. "FROM users
. "\n"
. "WHERE first_name =$dd_fname";
$result = mysqli_query($mysqli, $stmt);
$num_rows = mysqli_num_rows($result);
}
And upon looking at the xhr return, the json file has not been updated
UPDATE: Some progress. I have done some debugging and now I get a (500 internal Server Error) just when the php file is about to write the filtered JSON data. This error is coming from my AJAX; this line in particular:
xhr.send(data);
What could be causing this? The AJAX has already successfully sent the select dropdown variables (and I did see these while debugging the php).
UPDATE: After researching it appears that the 500 error resulted from the way I had set up my mysqli_fetch_assoc function.
while ($row = mysqli_fetch_assoc($result)) {
$cart[$i] = array(
"First Name" => htmlspecialchars($row['first_name']),
"Last Name" => htmlspecialchars($row['last_name']),
"Category" => htmlspecialchars($row['category']),
"Group" => htmlspecialchars($row['group']),
);
$i = $i + 1; //add next row
}
//encoding the PHP array
$json_server_pagination_data = array(
"total" => intval($num_rows), // total rows in data
"rows" => $cart, //array data
);
echo json_encode($json_server_pagination_data);
} else {
//do nothing . no data in sql query
}As per usual, one problem leads to another. I now have access to the filtered data (in both The HTML page VIA PHP and VIA JAVACRIPT) but I do not know how to overwrite the initial data first loaded to the table onload. I thought writing another echo json_encode($json_server_pagination_data) would overwrite it but this does not seem to be the case.
