How to do server-side processing

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
LVL 1
Jazzy 1012Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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?
Jazzy 1012Author 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 FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
ste5anSenior DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.