We help IT Professionals succeed at work.

How to do server-side processing

Jazzy 1012
Jazzy 1012 asked
I have this datatable:
<table class="table table-striped" id="sales-order-table">
			<th> ID</th>
			<th> Last Name</th>
			<th>Option ID</th>
			<th>Meal Name</th>
	while($row_to_get_meal = mysqli_fetch_assoc($result_to_get_meal)){

	$order_id = $row_to_get_meal['order_id'];
	$query_to_get_info = "select orders.id, first_name,last_name, orders.user_id, email, charge_date, orders.status from orders left join users on orders.user_id = users.id where orders.id = '$order_id'";
	$result_to_get_info = mysqli_query($conn,$query_to_get_info);
	if(mysqli_num_rows($result_to_get_info) == 0)
	$row_to_get_info = mysqli_fetch_assoc($result_to_get_info);
		<td><?php echo $row_to_get_info['user']; ?></td>
	    <td><?php echo $row_to_get_info['first_name']; ?></td>
	    <td><?php echo $row_to_get_info['last_name']; ?></td>
	    <td><?php echo $row_to_get_info['email']; ?></td>
	    <td><?php echo $row_to_get_info['id']; ?></td>
		<td><?php echo $row_to_get_info['date']; ?></td>
		<td><?php echo $row_to_get_info['status'];?></td>
		<td><?php echo $row_to_get_meal['meal_option_id'];?></td>
		<td><?php echo $row_to_get_meal['display_name'];?></td>
		<td><?php echo $row_to_get_meal['amount'];?></td>
		<td><?php echo $row_to_get_meal['count'];?></td>

		<?php }
        "pageLength": 50,
        dom: 'Bfrtip',
        buttons: [
                	  extend: 'csv', text: 'Download CSV' }

Open in new window

It contains over a thousand columns, how can I make it server-processing?
I know that I have to use this:
$(document).ready(function() {
    $('#example').DataTable( {
        serverSide: true,
        ordering: false,
        searching: false,
        ajax: function ( data, callback, settings ) {
            var out = [];
            for ( var i=data.start, ien=data.start+data.length ; i<ien ; i++ ) {
                out.push( [ i+'-1', i+'-2', i+'-3', i+'-4', i+'-5' ] );
            setTimeout( function () {
                callback( {
                    draw: data.draw,
                    data: out,
                    recordsTotal: 5000000,
                    recordsFiltered: 5000000
                } );
            }, 50 );
        scrollY: 200,
        scroller: {
            loadingIndicator: true
    } );
} );

Open in new window

But where should I put the query
Watch Question

ste5anSenior Developer

It contains over a thousand columns, how can I make it server-processing?
I guess you mean rows.. thus: no users needs thousands of rows in the UI.

Can you rephrase your question, please?


No its a report for me to look at all the information, in the database this query is around 2,000 rows so when I run it on the browser it takes time to load the page, I want it to load page by page, I already have datatables but it doesnot load page by page
David FavorFractional CTO
Distinguished Expert 2019

You'll likely hit several problems.

1) You're using <table> to display your data, which I'd say is correct, as it's table data.

2) Without seeing your CSS, it's unclear how fast your <table> will render. If you set hard coded widths or screen percentages, your table will display fairly quickly, because the table can be rendered one row at a time.

Without this type of CSS, your table can only render after all the data is downloaded.

3) Your speed is also dependent on your database tuning + storage engine + maybe /tmp location (disk or tmpfs/memory).

4) Your speed is also dependent on your connection speed, if this will be viewed on mobile devices on slow networks.

Many things to consider.

If you have a large development budget, hire someone to audit your hosting + code to ensure every layer is tuned optimally.

If this is a personal project... start by tuning your hosting, till you can sustain very fast page renders with an empty table, then move onto optimizing your data transfer.
Senior Developer
The AJAX call in the DataTable function must point to an end-point where the data processing (querying) happens. Thus you need to create such.


Simples sample taken from there:

$('#example').dataTable( {
  "ajax": {
    "url": "data.json",
    "type": "POST"
} );

Open in new window

Where data.json is a file on your server. Just point the URL to a newly to create PHP page which only returns the data as JSON.