Link to home
Start Free TrialLog in
Avatar of Opeyemi AbdulRasheed
Opeyemi AbdulRasheed

asked on

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:
<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>

Open in new window

AJAX:
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");
            }               
        }
    });
            
});

Open in new window

The PHP script:
<?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) );

Open in new window

User generated image
Avatar of Zakaria Acharki
Zakaria Acharki
Flag of Morocco image

Hi AbdulRasheed,

Have you tried to parse the coming value to an integer using `intval` like :

$stmt->bind_param( "i", intval($_POST['userStatus']) );

Open in new window


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.
Avatar of Opeyemi AbdulRasheed
Opeyemi AbdulRasheed

ASKER

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

Open in new window

So, I stored the value in a variable this way:
$user_status = intval($_POST['userStatus']);

Open in new window

and pass it this way:
$stmt->bind_param( "i", $user_status );

Open in new window

Yet, no luck. Same as before
Ok then we need to debug it, first try to show the value of the received post like :

echo $_POST['userStatus'];

Open in new window


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',
        ...

Open in new window

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'];

Open in new window

Oh, got:
Undefined index: userStatus in...

Open in new window

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 :

echo "===================".$_POST['userStatus']."===================";

Open in new window


And try also the JS debugging part in my previous comment, it will help us to identify the source of the problem.
Okay, i got
===================1===================

Open in new window

this
var user_status  = $(this).val();
alert(user_status);

Open in new window

returned 1 and 0 for selected option. The values seem correct
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 :

if ($stmt->execute()) { 
   echo '=========it worked=========';
} else {
   echo '========='.$stmt->error.'=========';
}

Open in new window

I got this now:
===================1============================it worked========={,…}

Open in new window

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.'==========';

Open in new window

here:
===================1============================it worked==================17==========

Open in new window

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",…}

Open in new window

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.
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",…}

Open in new window

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 }
                ]
            });

Open in new window

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 }
    ]
});

Open in new window

Nothing in the table. Not even in the console.
Ok backing to the onchange now try to separate the methods calls inside the success callback like :

}).done(function(data) {
    var myTable = $('#lockUsersTable').DataTable();

    myTable.clear();
    myTable.rows.add(data);
    myTable.draw();
});

Open in new window


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();
});

Open in new window

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 }
		    ]
		});

Open in new window

Actually populated the table. I'll try the call back you asked me to try now...
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:
<?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) );

?>

Open in new window

And AJAX:
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>';
	}
}
]
});

Open in new window

I don't even know how to do auto-numbering column using this method.
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 :

}).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();
});

Open in new window

Yes, the PHP script is still the same now.
Have you tried the code from my last comment AbdulRasheed?

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"},
]);

Open in new window


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>"}]}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Zakaria Acharki
Zakaria Acharki
Flag of Morocco image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Glad and happy to help with pleasure brother, thank you.