Webix Webix - 3 years ago 54
Ajax Question

Fill existing table with JSON data using ajax

I have been searching for a while for a way to populate a existing html table with delimited JSON.

What I have so far:
api.php that fetches the data from mysql database:

$id = $_GET['id'];
$query = 'SELECT id, username, level, email, active, last_login FROM cpusers LIMIT ' . $id . ', 10';
$result = mysqli_query($con, $query);
for(
$array = array();
$row = mysqli_fetch_assoc($result);
$array[] = $row
);
echo json_encode($array);


And my HTML:

<html>

<head>
<script language="javascript" type="text/javascript" src="http://code.jquery.com/jquery-2.1.1.js">

</script>
<script language="JavaScript" type="text/javascript">
$(function() {

var mydata = '';
var previd = parseInt($("#prev").attr('data-firstid'));
var nextid = parseInt($("#next").attr('data-lastid'));
var actualid = 0;

function getdata(value) {
$.ajax({
type: "GET",
async: false,
url: "api.php",
data: 'table=id='+value,
success: function(data){
mydata = jQuery.parseJSON(data)
}
});
}

if ( previd == -1 && nextid == -1 ) {
getdata(actualid)
pageMe(mydata);
if (mydata.length < 10) {
$("#next").hide();
} else {
nextid = actualid + 1;
$("#next").attr("data-lastid", nextid);
$("#next").show();
}
}

if ( previd == -1 ) {
$("#prev").hide();
} else {
$("#prev").show();
}

//register your click event
$("#prev").click(function() {
actualid = parseInt($(this).attr('data-firstid'));
previd = actualid - 1;
nextid = actualid + 1;
getdata(actualid);
pageMe(mydata);
$("#prev").attr("data-firstid", previd);
$("#next").attr("data-lastid", nextid);
if ( previd == -1 ) {
$("#prev").hide();
} else {
$("#prev").show();
}
});

$("#next").click(function() {
actualid = parseInt($(this).attr('data-lastid'));
previd = actualid - 1;
getdata(actualid);
pageMe(mydata);
$("#prev").attr("data-firstid", previd);
$("#prev").show();
if (mydata.length < 10) {
$("#next").hide();
} else {
nextid = actualid + 1;
$("#next").attr("data-lastid", nextid);
$("#next").show();
}
});
});

function pageMe(data) {
clearMyTable();
if (typeof data == 'undefined') return;
for (var i = 0; i < data.length; i++) {
var rowdata = data[i];
if (typeof rowdata == 'undefined') break;
$("#" + i + "0").text(rowdata.username);
$("#" + i + "1").text(rowdata.email);
$("#" + i + "2").text(rowdata.level);
$("#" + i + "3").text(rowdata.last_login);
$("#" + i + "4").text(rowdata.active);
}
}

function clearMyTable()
{
for (var i = 0; i < 10; i++) {
$("#" + i + "0").text("");
$("#" + i + "1").text("");
$("#" + i + "2").text("");
$("#" + i + "3").text("");
$("#" + i + "4").text("");

}
}
</script>
</head>

<body>
<table border="0" cellspacing="5" cellpadding="0">
<tr id="header">
<td align="center">Account</td>
<td align="center">Email Address</td>
<td align="center">Level</td>
<td align="center">Last Login</td>
<td align="center">Active</td>
</tr>
<tr id="Line0">
<td align="center" id="00">&nbsp;</td>
<td align="center" id="01">&nbsp;</td>
<td align="center" id="02">&nbsp;</td>
<td align="center" id="03">&nbsp;</td>
<td align="center" id="04">&nbsp;</td>
</tr>
<tr id="Line1">
<td align="center" id="10">&nbsp;</td>
<td align="center" id="11">&nbsp;</td>
<td align="center" id="12">&nbsp;</td>
<td align="center" id="13">&nbsp;</td>
<td align="center" id="14">&nbsp;</td>
</tr>
<tr id="Line2">
<td align="center" id="20">&nbsp;</td>
<td align="center" id="21">&nbsp;</td>
<td align="center" id="22">&nbsp;</td>
<td align="center" id="23">&nbsp;</td>
<td align="center" id="24">&nbsp;</td>
</tr>
<tr id="Line3">
<td align="center" id="30">&nbsp;</td>
<td align="center" id="31">&nbsp;</td>
<td align="center" id="32">&nbsp;</td>
<td align="center" id="33">&nbsp;</td>
<td align="center" id="34">&nbsp;</td>
</tr>
<tr id="Line4">
<td align="center" id="40">&nbsp;</td>
<td align="center" id="41">&nbsp;</td>
<td align="center" id="42">&nbsp;</td>
<td align="center" id="43">&nbsp;</td>
<td align="center" id="44">&nbsp;</td>
</tr>
<tr id="Line5">
<td align="center" id="50">&nbsp;</td>
<td align="center" id="51">&nbsp;</td>
<td align="center" id="52">&nbsp;</td>
<td align="center" id="53">&nbsp;</td>
<td align="center" id="54">&nbsp;</td>
</tr>
<tr id="Line6">
<td align="center" id="60">&nbsp;</td>
<td align="center" id="61">&nbsp;</td>
<td align="center" id="62">&nbsp;</td>
<td align="center" id="63">&nbsp;</td>
<td align="center" id="64">&nbsp;</td>
</tr>
<tr id="Line7">
<td align="center" id="70">&nbsp;</td>
<td align="center" id="71">&nbsp;</td>
<td align="center" id="72">&nbsp;</td>
<td align="center" id="73">&nbsp;</td>
<td align="center" id="74">&nbsp;</td>
</tr>
<tr id="Line8">
<td align="center" id="80">&nbsp;</td>
<td align="center" id="81">&nbsp;</td>
<td align="center" id="82">&nbsp;</td>
<td align="center" id="83">&nbsp;</td>
<td align="center" id="84">&nbsp;</td>
</tr>
<tr id="Line9">
<td align="center" id="90">&nbsp;</td>
<td align="center" id="91">&nbsp;</td>
<td align="center" id="92">&nbsp;</td>
<td align="center" id="93">&nbsp;</td>
<td align="center" id="94">&nbsp;</td>
</tr>
<tr id="Pagination">
<td align="center" colspan="2"><a id="prev" data-firstid="-1" href="#">Previous</a>

</td>
<td align="center" colspan="3"><a id="next" data-lastid="-1" href="#">Next</a>

</td>
</tr>
</table>
</body>

</html>


And I can't get this to work.
When the page loads, the table should be populated with mysql data starting from the id "0". And then use the links "Previous" and "Next" to paginate. When reaching the end of the mysql data, the table should be filled with the data and leave blank the rest of the cells.
I need to have all the time the 12 rows table.

Any help/suggestions appreciated.
Note: all the examples I found I saw the ajax building the html table, and none for a table already created. I don't want to create the table dynamically because it will break all my visuals.

Answer Source

What about using the free jqGrid for your purpose? It was working perfectly for me in a similar situation and it has great support here in stack overflow. Check their demo with php server side like yours: http://www.trirand.com/blog/jqgrid/jqgrid.html

BTW they have commercially supported server side component too but the javascript version is completely free.

declaimer: no connection with the company just happy user of the free component.

EDIT: I see several issues with your js code:

  1. $('.previouspage').click(function() is not properly ended - } must be }); line 27 in your original html.
  2. $('.previouspage').click(function() registers onClick event for elements that will be searched for by class - you don't have that class. You can test this by just adding class="previouspage" to your element, and it will be activated on clicking .. BTW when do you plan to load your first page?
  3. your Line0 tr should have elements with id 10,11,12... not 11,12,13...

Here is my working sample with array of data (I can't use your server side at the moment).

<html>

<head>
    <script language="javascript" type="text/javascript" src="http://code.jquery.com/jquery-2.1.1.js">
        
    </script>
    <script language="JavaScript" type="text/javascript">
        var mydata = [{
            id: "0",
            username: "test0",
            level: "1",
            email: "e@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "1",
            username: "test1",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "2",
            username: "test2",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "3",
            username: "test3",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "4",
            username: "test4",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "5",
            username: "test5",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "6",
            username: "test6",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "7",
            username: "test7",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "8",
            username: "test8",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "9",
            username: "test9",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "10",
            username: "test10",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "11",
            username: "test11",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }, {
            id: "12",
            username: "test12",
            level: "2",
            email: "eccc@e.com",
            active: "true",
            last_login: "2007-10-01"
        }];

        $(function() {

			var firstid = $("#prev").attr('data-firstid');
			var lastid = $("#next").attr('data-lastid');
		
            if ( firstid == -1 && lastid == -1 )
            {
                pageMe(mydata);

            }
			
			if ( firstid < 1  )
            {
                
				$("#prev").hide();

            }
			else {
			  $("#prev").show();
			}


            //register your click event
            $("#prev").click(function() {
			
                var id = parseInt($(this).attr('data-firstid'));
									
				var start = Math.max(0, id-11);
				var end = Math.max(10, id-1);
				var pagedata = mydata.slice(start, end);
				                    
                pageMe(pagedata);   

//for ajax you should have something like:
//  $.ajax({
//            type: "GET",
//            url: "api.php",
//            data: 'id='+start, 
//            success: pageMe  //NOTE no passing arguments!
//  });            

            });

            $("#next").click(function() {
			
                var id = parseInt($(this).attr('data-lastid'));
				
				if (id < -1) id = -1;
				
                var pagedata = mydata.slice(id+1, id+11);
                pageMe(pagedata);
				
				$("#prev").show();

            });


        });

        function pageMe(data) {

            clearMyTable();

			if (typeof data == 'undefined') return;
			
			if (data.length < 10) {
			  $("#next").hide();
			}
			else {
			  $("#next").show();
			}
			
            for (var i = 0; i < data.length; i++) {
			
					
                //that is single row for the table - the server will limit rows to ten
                var rowdata = data[i];

                if (typeof rowdata == 'undefined') break;
				
				

                if (i==0)
                {
                    //setting the prev id
                    $("#prev").attr("data-firstid", parseInt(rowdata.id));
                }

                $("#" + i + "0").text(rowdata.username);
                $("#" + i + "1").text(rowdata.email);
                $("#" + i + "2").text(rowdata.level);
                $("#" + i + "3").text(rowdata.last_login);
                $("#" + i + "4").text(rowdata.active);

                if (i == (data.length - 1) || i == 9 )
                {
                    //setting the prev id
                    $("#next").attr("data-lastid", parseInt(rowdata.id));
					break;
                }

            }

        }

        function clearMyTable()
        {
           for (var i = 0; i < 10; i++) {
                $("#prev").attr("data-firstid", -1);
				$("#next").attr("data-lastid", -1);
                $("#" + i + "0").text("");
                $("#" + i + "1").text("");
                $("#" + i + "2").text("");
                $("#" + i + "3").text("");
                $("#" + i + "4").text("");
                
            } 
        }
    </script>
</head>

<body>
    <table border="0" cellspacing="5" cellpadding="0">
        <tr id="header">
            <td align="center">Account</td>
            <td align="center">Email Address</td>
            <td align="center">Level</td>
            <td align="center">Last Login</td>
            <td align="center">Active</td>
        </tr>
        <tr id="Line0">
            <td align="center" id="00">&nbsp;</td>
            <td align="center" id="01">&nbsp;</td>
            <td align="center" id="02">&nbsp;</td>
            <td align="center" id="03">&nbsp;</td>
            <td align="center" id="04">&nbsp;</td>
        </tr>
        <tr id="Line1">
            <td align="center" id="10">&nbsp;</td>
            <td align="center" id="11">&nbsp;</td>
            <td align="center" id="12">&nbsp;</td>
            <td align="center" id="13">&nbsp;</td>
            <td align="center" id="14">&nbsp;</td>
        </tr>
        <tr id="Line2">
            <td align="center" id="20">&nbsp;</td>
            <td align="center" id="21">&nbsp;</td>
            <td align="center" id="22">&nbsp;</td>
            <td align="center" id="23">&nbsp;</td>
            <td align="center" id="24">&nbsp;</td>
        </tr>
        <tr id="Line3">
            <td align="center" id="30">&nbsp;</td>
            <td align="center" id="31">&nbsp;</td>
            <td align="center" id="32">&nbsp;</td>
            <td align="center" id="33">&nbsp;</td>
            <td align="center" id="34">&nbsp;</td>
        </tr>
        <tr id="Line4">
            <td align="center" id="40">&nbsp;</td>
            <td align="center" id="41">&nbsp;</td>
            <td align="center" id="42">&nbsp;</td>
            <td align="center" id="43">&nbsp;</td>
            <td align="center" id="44">&nbsp;</td>
        </tr>
        <tr id="Line5">
            <td align="center" id="50">&nbsp;</td>
            <td align="center" id="51">&nbsp;</td>
            <td align="center" id="52">&nbsp;</td>
            <td align="center" id="53">&nbsp;</td>
            <td align="center" id="54">&nbsp;</td>
        </tr>
        <tr id="Line6">
            <td align="center" id="60">&nbsp;</td>
            <td align="center" id="61">&nbsp;</td>
            <td align="center" id="62">&nbsp;</td>
            <td align="center" id="63">&nbsp;</td>
            <td align="center" id="64">&nbsp;</td>
        </tr>
        <tr id="Line7">
            <td align="center" id="70">&nbsp;</td>
            <td align="center" id="71">&nbsp;</td>
            <td align="center" id="72">&nbsp;</td>
            <td align="center" id="73">&nbsp;</td>
            <td align="center" id="74">&nbsp;</td>
        </tr>
        <tr id="Line8">
            <td align="center" id="80">&nbsp;</td>
            <td align="center" id="81">&nbsp;</td>
            <td align="center" id="82">&nbsp;</td>
            <td align="center" id="83">&nbsp;</td>
            <td align="center" id="84">&nbsp;</td>
        </tr>
        <tr id="Line9">
            <td align="center" id="90">&nbsp;</td>
            <td align="center" id="91">&nbsp;</td>
            <td align="center" id="92">&nbsp;</td>
            <td align="center" id="93">&nbsp;</td>
            <td align="center" id="94">&nbsp;</td>
        </tr>
        <tr id="Pagination">
            <td align="center" colspan="2"><a id="prev" data-firstid="-1" href="#">Previous</a>

            </td>
            <td align="center" colspan="3"><a id="next" data-lastid="-1" href="#">Next</a>

            </td>
        </tr>
    </table>
</body>

</html>    

EDIT 3: OK now we are looking my code above. You need to remove the array manipulation with calls to your ajax. You need to figure out how to call the ajax for the next :) Hope this helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download