Shopify Question posted in
The Shopify help center can be found here.

datatable does not support if more than 60000 records are being fetched – Shopify

we want to store large number of data (more than 60K rows ) in datatable but the datatable fails to executes and output is not coming well. The datatable will not fails till 20000 records. Here is fhe following source code

 <div class="row">
                        <table id="customer_table"  class="display" cellspacing="0" width="100%">
                         <thead>    
                    <tr>
                        <th>#</th>
                        <th>Customer Name</th>
                        <th>Email</th>
                        <th>Group</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>


    <?php   for($p=1;$p<=200;$p++)

    {

    $ch = curl_init("https://$shop_name/admin/customers.json?limit=250&page=".$p."");
                    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET"); 
                    curl_setopt($ch,CURLOPT_RETURNTRANSFER,TRUE);
                    curl_setopt($ch, CURLOPT_POSTFIELDS, $params); 
                    curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json",
                                        "X-Shopify-Access-Token: $store_token"));
                     $result = curl_exec($ch);
                    $decoded_customer = json_decode($result, true); 
                    //var_dump($decoded_customer); 
                    ?>

    <?php for($i=0;$i<count($decoded_customer['customers']);$i++)

    {
    ?>
            <tr>
            <input type="hidden" value="<?php echo $decoded_customer['customers'][$i]['id']; ?>" >
            <td>#</td>
            <td><?php echo $decoded_customer['customers'][$i]['first_name']." ".$decoded_customer['customers'][$i]['last_name'];?></td>
            <td><?php echo $decoded_customer['customers'][$i]['email']; ?></td>
            <td>

      <select class="form-control" >
           <option value="" >
               No Selected
            </option>
                <?php 
                session_start();

                $shop_name = $_SESSION['shop'];
                include("dbconnect.php");

                $sql_select_tbl_shop = "SELECT * FROM `store_info_tbl` WHERE `store_name` = '$shop_name'";
                $select_tbl_shop = mysql_query($sql_select_tbl_shop);

                if(mysql_num_rows($select_tbl_shop)>0){

                $row_select_val=mysql_fetch_array($select_tbl_shop );
                $shop_id=$row_select_val['id']; 

                //select selected group name
                $customer_id=$decoded_customer['customers'][$i]['id'];


                $sqlselectgroup_customer="SELECT * FROM `tbl_group` WHERE `store_name`='$shop_id'";
                $resselectgroup_customer=mysql_query($sqlselectgroup_customer);
                if(mysql_num_rows($resselectgroup_customer))
                {
                  while($rowselectgroup_customer=mysql_fetch_assoc($resselectgroup_customer))
                  { 
                $sqlselectcustomergroup="SELECT * FROM `tbl_customer_detail` WHERE `customer_shop_id`='$customer_id'";
                $resselectcustomergroup=mysql_query($sqlselectcustomergroup);

                if(mysql_num_rows($resselectcustomergroup)>0){
                //$rowselectcustomer=mysql_num_rows($resselectcustomergroup);

                 //$rowid=mysql_fetch_array('group_name');
                 $rowid=mysql_fetch_array($resselectcustomergroup);
                 $customer_group=$rowid['group_name'];

                ?>

            <option value="<?php echo $rowselectgroup_customer['id']; ?>" <?php if($customer_group==$rowselectgroup_customer['id']) { ?>selected <?php } ?> >
            <?php echo $rowselectgroup_customer['group_name']; ?>
            </option>
              <?php } else{ ?>

           <option value="<?php echo $rowselectgroup_customer['id']; ?>" >
            <?php echo $rowselectgroup_customer['group_name']; ?>
            </option>

                <?php  } } } } ?>

            </select>
            </td>
            <td><button type="button" class="btn btn-primary save">Save</button></td>
            </tr>
            <?php } } ?>



    </tbody>

    </table>

    <script type="text/javascript" > 
               $(document).ready(function(){
                 $('#customer_table').DataTable();

               }); 
               $(document).on('click','.save',function(){
                   var shopify_customer_id=$(this).parent().parent().find('input').val();
                   var customer_name=$(this).parent().parent().find('td').eq(1).html();
                   var customer_email=$(this).parent().parent().find('td').eq(2).html();
                   var group_name=$(this).parent().parent().find('td').find('select').val();
                  //alert(shopify_customer_id+customer_name+customer_email+group_name);


                  $.ajax({
                        type: "post",
                        url: "ajaxcustomerdata.php",
                        data: {
                            shopify_customer_id:shopify_customer_id,
                            customer_name:customer_name,
                            customer_email:customer_email,
                            group_name:group_name
                        },
                        success: function (data){
                           alert(data);

                        },
                        error: function (xhr, ajaxOptions, thrownError){
                        }
                    });



                   });

            </script>
            <script>

            </script>



    </div>

If any one has any kind of suggestion please help me out to resolve this problem

Tags:

2

Answers


  1. 0

    Please read https://datatables.net/extensions/scroller/examples/initialisation/server-side_processing.html official post from data table. it is showing how to add thousands of record added in data table.

    Below is example of 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: 160421,
                    recordsFiltered: 160421
                } );
            }, 50 );
        },
        scrollY: 600,
        scroller: {
            loadingIndicator: true
        }
    } );
} );

    html for datatable :

    <table id="example" class="display nowrap" width="100%" cellspacing="0">
        <thead>
            <tr>
                <th>ID</th>
                <th>First name</th>
                <th>Last name</th>
                <th>ZIP / Post code</th>
                <th>Country</th>
            </tr>
        </thead>
    </table>
    Login or Signup to reply.
  2. 0

    Though you should not load so many records at once, it will make your server insecure and you may go out of resources if so many multiple request are made at once. You should try Ajax based Datatable.

    https://datatables.net/examples/ajax/objects.html

    Link above uses the AJAX call to load data so that you don’t have to query all 60,000 rows.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search