How to do server-side processing

Jazzy 1012
Jazzy 1012 used Ask the Experts™
on
I have this datatable:
<table class="table table-striped" id="sales-order-table">
	<thead>
		<tr>
			<th> ID</th>
			<th>Name</th>
			<th> Last Name</th>
			<th>Email</th>
			<th>Order</th>
			<th>Date</th>
			<th>Status</th>
			<th>Option ID</th>
			<th>Meal Name</th>
			<th>Amount</th>
			<th>Quantity</th>
		</tr>
	</thead>
	<tbody>
<?php 
	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)
	{
		continue;
	}
	else{
	$row_to_get_info = mysqli_fetch_assoc($result_to_get_info);
	?>
	<tr>
		<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>

		</tr>
		<?php }
		}?>
	</tbody>
</table>
<script>
$(document).ready(function(){
    $('#sales-order-table').DataTable({
        "pageLength": 50,
        dom: 'Bfrtip',
        buttons: [
                  {
                	  extend: 'csv', text: 'Download CSV' }
        ]
    });
});
</script>

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

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

Author

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

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

https://datatables.net/reference/option/ajax

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial