DataTable is not populated after AJAX call using onchange event
Hello Experts!
Please what is wrong with my code? No return data in table but when I checked header (developer tool), I got something (attached).
I think the problem is from select option values and how they are being passed through AJAX. I said this because when I used hard-coded value (1 or 0) in my PHP script and request AJAX call when page loads NOT onchange, the table populated correctly.
The HTML select:
![header.png]()
Please what is wrong with my code? No return data in table but when I checked header (developer tool), I got something (attached).
I think the problem is from select option values and how they are being passed through AJAX. I said this because when I used hard-coded value (1 or 0) in my PHP script and request AJAX call when page loads NOT onchange, the table populated correctly.
The HTML select:
<select name="user_status" id="user_status">
<option value="">--User Status--</option>
<option value="1">Active Users</option>
<option value="0">Inactive Users</option>
</select>
var myTable =
$('#lockUsersTable').DataTable({
"pageLength": 25,
select: {style: 'multi'},
columns: [
{ data: 'count' },
{ data: 'initials' },
{ data: 'username' },
{ data: 'user_type' },
{ data: 'status', orderable: false },
{ data: 'lock', orderable: false }
]
});
$('#user_status').on('change', function() {
$('#tableResponse').html('<i class="ace-icon fa fa-spinner fa-spin blue bigger-115"></i> Please wait...');
$.ajax({
method : 'post',
url : '../../lockUsers_Data',
data : { userStatus : $('#user_status').val() },
dataType : 'json',
}).done(function(data) {
myTable.clear().draw();
myTable.rows.add(data).draw();
var statusVal = $('#user_status').val();
if ( myTable.data().any() ) {
if ( statusVal == 1 ){
$('#tableResponse').html("Active Users");
}else if(statusVal == 0){
$('#tableResponse').html("Inactive Users");
}else{
$('#tableResponse').html("Select Users' Status")
}
}
else{
if ( statusVal == 1 ){
$('#tableResponse').html("No active users found");
}else if(statusVal == 0){
$('#tableResponse').html("No inactive users found");
}else{
$('#tableResponse').html("Select Users' Status");
}
}
});
});
<?php
include('../../db_connection.php');
//$user_status = 1;
$stmt = $conn->prepare( "SELECT UserId, Initials, Username, Status, User_Type FROM table WHERE Status = ? ORDER BY User_Type ASC" );
$stmt->bind_param( "i", $_POST['userStatus'] );
$stmt->execute();
$users = $stmt->get_result();
$data = array();
$count = 1;
while ( $user = $users->fetch_object() ):
if ( $user->Status == 1 ) {
$status = "<span class='badge badge-sm badge-success'>Active</span> ";
} else{
$status = "<span class='badge badge-sm badge-danger'>Inactive</span> ";
}
$lock = "<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[".$user->UserId."]' value=".$user->UserId." ><span class='lbl'></span></label></div>";
$data[] = array(
'count' => $count++,
'user_id' => $user->UserId,
'initials' => $user->Initials,
'username' => $user->Username,
'user_type' => $user->User_Type,
'status' => $status,
'lock' => $lock
);
endwhile;
$response = array(
'data' => $data,
);
die( json_encode($response) );
Hi AbdulRasheed,
Have you tried to parse the coming value to an integer using `intval` like :
Since your bind_param wait for the corresponding variable to have an integer type and the .val() in the JS part return always a string that may the part that creates the problem.
Have you tried to parse the coming value to an integer using `intval` like :
$stmt->bind_param( "i", intval($_POST['userStatus']) );
Since your bind_param wait for the corresponding variable to have an integer type and the .val() in the JS part return always a string that may the part that creates the problem.
Thanks for the response. I tried it that way but got:
Strict standards: Only variables should be passed by reference in C:\..\..\lockUsers_Data.php on line 6
$user_status = intval($_POST['userStatus']);
$stmt->bind_param( "i", $user_status );
Ok then we need to debug it, first try to show the value of the received post like :
To make sure you're getting the correct value.
Make sure you don't have two elements with the same id in your document and try to get the value using this before the ajax request and show it like :
echo $_POST['userStatus'];
To make sure you're getting the correct value.
Make sure you don't have two elements with the same id in your document and try to get the value using this before the ajax request and show it like :
var user_status = $(this).val();
alert(user_status);
$.ajax({
method : 'post',
url : '../../lockUsers_Data',
data : { userStatus : user_status },
dataType : 'json',
...
echo $_POST['userStatus'];I can't see anything. I added it on top of the PHP script this way:
<?php
include('../../db_connection.php');
echo $_POST['userStatus'];
You should check the value in the returned data under the network tab in your console when you change the value of the select, try to add some special characters to identify the value like for example :
And try also the JS debugging part in my previous comment, it will help us to identify the source of the problem.
echo "===================".$_POST['userStatus']."===================";
And try also the JS debugging part in my previous comment, it will help us to identify the source of the problem.
this
var user_status = $(this).val();
alert(user_status);
Good to hear this, that means you got the correct value in the PHP side, so we need just to debug this part, the JS looks fine too.
We could start by replacaing $stmt->execute() with :
We could start by replacaing $stmt->execute() with :
if ($stmt->execute()) {
echo '=========it worked=========';
} else {
echo '========='.$stmt->error.'=========';
}
Looks like all goes right until now, what about the returned result, try to show how many rows are returned by your query by adding the following line just after $users = $stmt->get_result(); :
echo '========='.$users->num_rows.'==========';
Do you get this as a response when you're changing the select value from the list? if yes then try to comment all the echo's we have added and try again to see if it will work.
Do you get this as a response when you're changing the select value from the list?YES.
Now, I didn't get anything other than:
data: [{count: 1, user_id: 49, initials: "HARDSOFT APP", username: "admin", user_type: "staff",…},…]
0: {count: 1, user_id: 49, initials: "HARDSOFT APP", username: "admin", user_type: "staff",…}
1: {count: 2, user_id: 52, initials: "DEMO FORM MASTER", username: "fm", user_type: "staff",…}
2: {count: 3, user_id: 53, initials: "DEMO STAFF", username: "staff", user_type: "staff",…}
3: {count: 4, user_id: 60, initials: "ABDULRASHEED G. O.", username: "orpee", user_type: "staff",…}
4: {count: 5, user_id: 81, initials: "SUNDAY STAFF", username: "sunday", user_type: "staff",…}
5: {count: 6, user_id: 95, initials: "MONDAY STAFF", username: "monday", user_type: "staff",…}
6: {count: 7, user_id: 126, initials: "JOHN DOE", username: "doe", user_type: "staff",…}
7: {count: 8, user_id: 129, initials: "GOODNESS", username: "ness", user_type: "staff",…}
8: {count: 9, user_id: 134, initials: "RABI ABDULRASHEED", username: "rabi", user_type: "staff",…}
9: {count: 10, user_id: 135, initials: "OWOLABI ALAMU", username: "alamu", user_type: "staff",…}
10: {count: 11, user_id: 62, initials: "DEMO STUDENT 1", username: "0001", user_type: "student",…}
11: {count: 12, user_id: 70, initials: "ADEBISI ADEKUNLE GOLD", username: "GSS/2222", user_type: "student",…}
12: {count: 13, user_id: 71, initials: "RABIAT ALAMU", username: "GSS/0011", user_type: "student",…}
13: {count: 14, user_id: 73, initials: "DEMO 4", username: "4", user_type: "student",…}
14: {count: 15, user_id: 78, initials: "DHDHDH", username: "GSS/00016", user_type: "student",…}
15: {count: 16, user_id: 136, initials: "OWOLABI ALAMU", username: "Gss/0009", user_type: "student",…}
16: {count: 17, user_id: 138, initials: "BEST TEACHER", username: "3214", user_type: "student",…}
In addition, if I don't use onchange event to load the table, it loads well even with the same PHP script. I just want to be able to load the table based on the value of the select.
Trivial, I'm using the same method (onchange) in various parts of this project.
Trivial, I'm using the same method (onchange) in various parts of this project.
OK, I see, what is the main difference between the data you get when you change the value from the select list and the data you get when you hardcode the 1 value?
OK, I see, what is the main difference between the data you get when you change the value from the select list and the data you get when you hardcode the 1 value?Here, same
data: [{count: 1, user_id: 49, initials: "HARDSOFT APP", username: "admin", user_type: "staff",…},…]
0: {count: 1, user_id: 49, initials: "HARDSOFT APP", username: "admin", user_type: "staff",…}
1: {count: 2, user_id: 52, initials: "DEMO FORM MASTER", username: "fm", user_type: "staff",…}
2: {count: 3, user_id: 53, initials: "DEMO STAFF", username: "staff", user_type: "staff",…}
3: {count: 4, user_id: 60, initials: "ABDULRASHEED G. O.", username: "orpee", user_type: "staff",…}
4: {count: 5, user_id: 81, initials: "SUNDAY STAFF", username: "sunday", user_type: "staff",…}
5: {count: 6, user_id: 95, initials: "MONDAY STAFF", username: "monday", user_type: "staff",…}
6: {count: 7, user_id: 126, initials: "JOHN DOE", username: "doe", user_type: "staff",…}
7: {count: 8, user_id: 129, initials: "GOODNESS", username: "ness", user_type: "staff",…}
8: {count: 9, user_id: 134, initials: "RABI ABDULRASHEED", username: "rabi", user_type: "staff",…}
9: {count: 10, user_id: 135, initials: "OWOLABI ALAMU", username: "alamu", user_type: "staff",…}
10: {count: 11, user_id: 62, initials: "DEMO STUDENT 1", username: "0001", user_type: "student",…}
11: {count: 12, user_id: 70, initials: "ADEBISI ADEKUNLE GOLD", username: "GSS/2222", user_type: "student",…}
12: {count: 13, user_id: 71, initials: "RABIAT ALAMU", username: "GSS/0011", user_type: "student",…}
13: {count: 14, user_id: 73, initials: "DEMO 4", username: "4", user_type: "student",…}
14: {count: 15, user_id: 78, initials: "DHDHDH", username: "GSS/00016", user_type: "student",…}
15: {count: 16, user_id: 136, initials: "OWOLABI ALAMU", username: "Gss/0009", user_type: "student",…}
16: {count: 17, user_id: 138, initials: "BEST TEACHER", username: "3214", user_type: "student",…}
when I used hard-coded value (1 or 0) in my PHP script and request AJAX call when page loads NOT onchange
Could you please show me how you fire the request call when the page load?
here sir,
var myTable =
$('#lockUsersTable').DataTable({
"pageLength": 25,
ajax : '../../lockUsers_Data',
select: {style: 'multi'},
columns: [
{ data: 'count' },
{ data: 'initials' },
{ data: 'username' },
{ data: 'user_type' },
{ data: 'status', orderable: false },
{ data: 'lock', orderable: false }
]
});
In the first statement, you're calling it with HTTP get method and without passing parameters, try it with the POST and the parameter in load like :
var myTable = $('#lockUsersTable').DataTable({
"pageLength": 25,
"ajax": {
"url": '../../lockUsers_Data',
"type": "POST",
"data": { userStatus : 1 }
},
select: {style: 'multi'},
columns: [
{ data: 'count' },
{ data: 'initials' },
{ data: 'username' },
{ data: 'user_type' },
{ data: 'status', orderable: false },
{ data: 'lock', orderable: false }
]
});
Ok backing to the onchange now try to separate the methods calls inside the success callback like :
Then change the select value and check if there's anything in the console or in the table.
}).done(function(data) {
var myTable = $('#lockUsersTable').DataTable();
myTable.clear();
myTable.rows.add(data);
myTable.draw();
});
Then change the select value and check if there's anything in the console or in the table.
You may need to try this too :
}).done(function(data) {
var myTable = $('#lockUsersTable').DataTable({
"pageLength": 25,
select: {style: 'multi'},
columns: [
{ data: 'count' },
{ data: 'initials' },
{ data: 'username' },
{ data: 'user_type' },
]
});
myTable.clear();
myTable.rows.add([
{count: 1, user_id: 49, initials: "HARDSOFT APP", username: "admin", user_type: "staff"},
{count: 2, user_id: 52, initials: "DEMO FORM MASTER", username: "fm", user_type: "staff"},
{count: 3, user_id: 53, initials: "DEMO STAFF", username: "staff", user_type: "staff"},
{count: 4, user_id: 60, initials: "ABDULRASHEED G. O.", username: "orpee", user_type: "staff"},
{count: 5, user_id: 81, initials: "SUNDAY STAFF", username: "sunday", user_type: "staff"},
{count: 6, user_id: 95, initials: "MONDAY STAFF", username: "monday", user_type: "staff"},
{count: 7, user_id: 126, initials: "JOHN DOE", username: "doe", user_type: "staff"},
]);
myTable.draw();
});
Sorry, the POST method here:
var myTable = $('#lockUsersTable').DataTable({
"pageLength": 25,
"ajax": {
"url": '../../lockUsers_Data',
"type": "POST",
"data": { userStatus : 1 }
},
select: {style: 'multi'},
columns: [
{ data: 'count' },
{ data: 'initials' },
{ data: 'username' },
{ data: 'user_type' },
{ data: 'status', orderable: false },
{ data: 'lock', orderable: false }
]
});
this didn't help:
}).done(function(data) {
var myTable = $('#lockUsersTable').DataTable();
myTable.clear();
myTable.rows.add(data);
myTable.draw();
});
this displayed in the table:
}).done(function(data) {
var myTable = $('#lockUsersTable').DataTable({
"pageLength": 25,
select: {style: 'multi'},
columns: [
{ data: 'count' },
{ data: 'initials' },
{ data: 'username' },
{ data: 'user_type' },
]
});
myTable.clear();
myTable.rows.add([
{count: 1, user_id: 49, initials: "HARDSOFT APP", username: "admin", user_type: "staff"},
{count: 2, user_id: 52, initials: "DEMO FORM MASTER", username: "fm", user_type: "staff"},
{count: 3, user_id: 53, initials: "DEMO STAFF", username: "staff", user_type: "staff"},
{count: 4, user_id: 60, initials: "ABDULRASHEED G. O.", username: "orpee", user_type: "staff"},
{count: 5, user_id: 81, initials: "SUNDAY STAFF", username: "sunday", user_type: "staff"},
{count: 6, user_id: 95, initials: "MONDAY STAFF", username: "monday", user_type: "staff"},
{count: 7, user_id: 126, initials: "JOHN DOE", username: "doe", user_type: "staff"},
]);
myTable.draw();
});
Sir, out of curiosity, I tried it this way, it worked though but it won't give me the flexibility of styling the columns such as adding if statement for Active and Inactive (badge-success and badge-danger respectively):
The PHP Script now:
I'm using onchange with this method.
Thank you so much.
The PHP Script now:
<?php
sleep(1);
require_once '../../connection.php';
$stmt = $conn->prepare( "SELECT UserId, Initials, Username, Status, User_Type FROM tableWHERE Status = ? ORDER BY User_Type ASC" );
$stmt->bind_param( "i", $_POST['userStatus'] );
$stmt->execute();
$students = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
die( json_encode($students) );
?>
var myTable =
$('#lockUsersTable').DataTable({
"pageLength": 25,
//ajax : '../data/lockUsers_Data',
select: {style: 'multi'},
columns: [
{ data: 'Initials' },
{ data: 'Username' },
{ data: 'User_Type' },
{
"orderable": false,
render: function ( data, type, row ) {
return '<div class="center"><label class="pos-rel"><input type="checkbox" class="ace" name="userSelect[' + row.UserId + ']" value="' + row.UserId + '"><span class="lbl"></span></label></div>';
}
}
]
});
I'm using onchange with this method.
Thank you so much.
We don't have to change the way, I think the main problem will be in the data format, but I'm not sure why the datatable doesn't return an error instead just doesn't show the passed data!!
Have you tried to keep the PHP script as it is in the originally posted code the in the callback :
Have you tried to keep the PHP script as it is in the originally posted code the in the callback :
}).done(function(data) {
var myTable = $('#lockUsersTable').DataTable({
"pageLength": 25,
select: {style: 'multi'},
columns: [
{ data: 'count' },
{ data: 'initials' },
{ data: 'username' },
{ data: 'user_type' },
]
});
myTable.clear();
myTable.rows.add(data);
myTable.draw();
});
Have you tried the code from my last comment AbdulRasheed?
The last working code :
Contains the data returned from your PHP script except the two columns status,lock, if you could give me the full data returned from your script with all columns, I'll try to identify why it's not filling the table with this data and from where the problem comes.
The last working code :
myTable.rows.add([
{count: 1, user_id: 49, initials: "HARDSOFT APP", username: "admin", user_type: "staff"},
{count: 2, user_id: 52, initials: "DEMO FORM MASTER", username: "fm", user_type: "staff"},
{count: 3, user_id: 53, initials: "DEMO STAFF", username: "staff", user_type: "staff"},
{count: 4, user_id: 60, initials: "ABDULRASHEED G. O.", username: "orpee", user_type: "staff"},
{count: 5, user_id: 81, initials: "SUNDAY STAFF", username: "sunday", user_type: "staff"},
{count: 6, user_id: 95, initials: "MONDAY STAFF", username: "monday", user_type: "staff"},
{count: 7, user_id: 126, initials: "JOHN DOE", username: "doe", user_type: "staff"},
]);
Contains the data returned from your PHP script except the two columns status,lock, if you could give me the full data returned from your script with all columns, I'll try to identify why it's not filling the table with this data and from where the problem comes.
Have you tried the code from my last comment AbdulRasheed?Yes, I have tried it. Same thing
Here:
{"data":[{"count":1,"user_id":49,"initials":"HARDSOFT APP","username":"admin","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[49]' value=49 ><span class='lbl'><\/span><\/label><\/div>"},{"count":2,"user_id":52,"initials":"DEMO FORM MASTER","username":"fm","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[52]' value=52 ><span class='lbl'><\/span><\/label><\/div>"},{"count":3,"user_id":53,"initials":"DEMO STAFF","username":"staff","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[53]' value=53 ><span class='lbl'><\/span><\/label><\/div>"},{"count":4,"user_id":60,"initials":"ABDULRASHEED G. O.","username":"orpee","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[60]' value=60 ><span class='lbl'><\/span><\/label><\/div>"},{"count":5,"user_id":81,"initials":"SUNDAY STAFF","username":"sunday","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[81]' value=81 ><span class='lbl'><\/span><\/label><\/div>"},{"count":6,"user_id":95,"initials":"MONDAY STAFF","username":"monday","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[95]' value=95 ><span class='lbl'><\/span><\/label><\/div>"},{"count":7,"user_id":126,"initials":"JOHN DOE","username":"doe","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[126]' value=126 ><span class='lbl'><\/span><\/label><\/div>"},{"count":8,"user_id":129,"initials":"GOODNESS","username":"ness","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[129]' value=129 ><span class='lbl'><\/span><\/label><\/div>"},{"count":9,"user_id":134,"initials":"RABI ABDULRASHEED","username":"rabi","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[134]' value=134 ><span class='lbl'><\/span><\/label><\/div>"},{"count":10,"user_id":135,"initials":"OWOLABI ALAMU","username":"alamu","user_type":"staff","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[135]' value=135 ><span class='lbl'><\/span><\/label><\/div>"},{"count":11,"user_id":62,"initials":"DEMO STUDENT 1","username":"0001","user_type":"student","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[62]' value=62 ><span class='lbl'><\/span><\/label><\/div>"},{"count":12,"user_id":70,"initials":"ADEBISI ADEKUNLE GOLD","username":"GSS\/2222","user_type":"student","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[70]' value=70 ><span class='lbl'><\/span><\/label><\/div>"},{"count":13,"user_id":71,"initials":"RABIAT ALAMU","username":"GSS\/0011","user_type":"student","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[71]' value=71 ><span class='lbl'><\/span><\/label><\/div>"},{"count":14,"user_id":73,"initials":"DEMO 4","username":"4","user_type":"student","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[73]' value=73 ><span class='lbl'><\/span><\/label><\/div>"},{"count":15,"user_id":78,"initials":"DHDHDH","username":"GSS\/00016","user_type":"student","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[78]' value=78 ><span class='lbl'><\/span><\/label><\/div>"},{"count":16,"user_id":136,"initials":"OWOLABI ALAMU","username":"Gss\/0009","user_type":"student","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[136]' value=136 ><span class='lbl'><\/span><\/label><\/div>"},{"count":17,"user_id":138,"initials":"BEST TEACHER","username":"3214","user_type":"student","status":"<span class='badge badge-sm badge-success'>Active<\/span> ","lock":"<div class='center'><label class='pos-rel'><input type='checkbox' class='ace' name='userSelect[138]' value=138 ><span class='lbl'><\/span><\/label><\/div>"}]}
Zakaria, your efforts are quite commendable. That did it. Working perfectly now. I'm so sorry for your trouble in order to help me. I pray you always get help and support. Thank you so much