CODEWITHSUNDEEP
javascriptphpjquerymysqlajax
wuno
wuno

Reputation: 9865

Page Scrolling Effect With Ajax How Data Is Loaded Into The Page

I am loading data from a mysql table into a html table. I am using AJAX php Jquery to accomplish this. I need to make sure that the way I built this will work regardless of how much data is in the table.

Right now I am working with a table that is 5000 rows long but there will eventually be 88000 rows in this table.

I know if I load all the data on page load this could end up bogging down the server and the load time of the page.

My question is the way my logic is now will it load all the results into the $results and only query the needed amount of rows because it is paginated. Or even though it is paginated is my webpage taking every row in the whole database for load time.

if the whole table is being loaded how can I change the query to only load the data when needed. It loads on page scroll.

Also I need to write a search function. Since the data is paginated would I search the data in $results or query the table with separate search functions? Which way would provide less load times which would cause a bad experience for my user?

The AjAX

<script type="text/javascript">
jQuery(document).ready(function($) {
var busy = true;
var limit = 5;
var offset = 0;
var assetPath = "<?php echo $assetPath ?>"

function displayRecords(lim, off) {
  jQuery.ajax({
          type: "GET",
          async: false,
          url: assetPath,
          data: "limit=" + lim + "&offset=" + off,
          cache: false,
          beforeSend: function() {
            $("#loader_message").html("").hide();
            $('#loader_image').show();
          },
          success: function(html) {
            $("#productResults").append(html);
            $('#loader_image').hide();
            if (html == "") {
             $("#loader_message").html('<button data-atr="nodata" class="btn btn-default" type="button">No more records.</button>').show()
            } else {
             $("#loader_message").html('Loading... Please wait <img src="http://www.wuno.com/monstroid/wp-content/uploads/2016/02/LoaderIcon.gif" alt="Loading">').show();
            }
            window.busy = false;

          }
        });
}

(function($) {
$(document).ready(function() {
if (busy == true) {
  displayRecords(limit, offset);
  busy = false;
}
});
})( jQuery );



(function($) {
$(document).ready(function() {
$(window).scroll(function() {
          // make sure u give the container id of the data to be loaded in.
          if ($(window).scrollTop() + $(window).height() > $("#productResults").height() && !busy) {
            offset = limit + offset;
         displayRecords(limit, offset);

          }
});
});
})( jQuery );
});
</script>

This is how I am querying the database

$limit = (intval($_GET['limit']) != 0 ) ? $_GET['limit'] : 5;
$offset = (intval($_GET['offset']) != 0 ) ? $_GET['offset'] : 0;

$sql = "SELECT * FROM wuno_inventory WHERE 1 ORDER BY id ASC LIMIT $limit OFFSET $offset";
try {
  $stmt = $DB_con->prepare($sql);
  $stmt->execute();
  $results = $stmt->fetchAll();
} catch (Exception $ex) {
  echo $ex->getMessage();
}
if (count($results) > 0) {
  foreach ($results as $res) {
    echo '<tr class="invent">';  
    echo '<td>' . $res['wuno_product'] . '</td>';  
    echo '<td>' . $res['wuno_alternates'] . '</td>';  
    echo '<td>' . $res['wuno_description'] . '</td>';  
    echo '<td>' . $res['wuno_onhand'] . '</td>';  
    echo '<td>' . $res['wuno_condition'] . '</td>';  
    echo '</tr>';   
  }
}

Upvotes: 2

Views: 2842

Answers (3)

hherger
hherger

Reputation: 1680

Here is a reduced example of the JSON / AJAX mechanism that I have tested:

HTML (file test.html)

<html>

<head>
<!-- THE NEXT LINE MUST BE MODIFIED -->
<script src="../jquery-1.4.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
var limit = 5;
var offset = 0;
// THE NEXT LINE MUST BE MODIFIED
var assetPath = "http://www.example.com/stackoverflow/test.php"

function displayRecords(lim, off) {
    jQuery.ajax({
        type: "GET",
        url: assetPath,
        dataType: "json",                   // We expect to receive a json object
        data: "limit=" + lim + "&offset=" + off,
        async: true,
        cache: false,
        beforeSend: function() {
            $("#content").html("");
        },
        success: function(json) {
            limit = json.lim;                       // corr to $output['lim']
            offset = json.offs;                     // corr to $output['offs']
            $("#content").html(json.html);
            window.busy = false;
        }
    }); 
}
</script>
</head>

<body>
    <div id="content"></div>
    <div onclick="displayRecords(limit,offset); return false;" style="cursor:pointer">Click to Call</div>
</body>

</html>

PHP (file test.php)

<?php
$limit = (intval($_REQUEST['limit']) != 0 ) ? $_REQUEST['limit'] : 5;
$offset = (intval($_REQUEST['offset']) != 0 ) ? $_REQUEST['offset'] : 0;

// Prepare the $output structure (will become a json object string)
$output = array(
'lim'=>$limit,
'offs'=>$offset+$limit,
'html'=>''
);

$output['html'] .= '<span style="color:red;">limit='.$output['lim'].', offset='.$output['offs'].'</span>';

// Now encode $output as a json object (string) and send it to the client
header('Content-type: application/json; charset=utf-8');
echo json_encode($output, JSON_HEX_TAG|JSON_HEX_AMP|JSON_HEX_APOS|JSON_HEX_QUOT|JSON_FORCE_OBJECT);

Result

1st time
1st time

2nd time
2nd time

enter image description here
nth time

Upvotes: 0

hherger
hherger

Reputation: 1680

By transferring a JSON object from the server the limit, offset and html (and maybe others lime status_messsage, etc.) values could be transferred to the client at the same time.

On the client side I meant something like this:

var limit = 5;
var offset = 0;
var assetPath = "<?php echo $assetPath ?>"

function displayRecords(lim, off) {
  jQuery.ajax({
      type: "GET",
      async: false,
      url: assetPath,
      dataType: "json",                         // We expect to receive a json object
      data: "limit=" + lim + "&offset=" + off,
      cache: false,
      beforeSend: function() {
        $("#loader_message").html("").hide();
        $('#loader_image').show();
      },
      success: function(json) {
        limit = json.lim;                       // corr to $output['lim']
        offset = json.offs;                     // corr to $output['offs']
        $("#productResults").append(json.html); // corr to $output['html']
        $('#loader_image').hide();
        if (json.html == "") {
         $("#loader_message").html('<button data-atr="nodata" class="btn btn-default" type="button">No more records.</button>').show()
        } else {
         $("#loader_message").html('Loading... Please wait <img src="http://www.wuno.com/monstroid/wp-content/uploads/2016/02/LoaderIcon.gif" alt="Loading">').show();
        }
        window.busy = false;

      }
    });
}

...and on the server side:

$limit = (intval($_REQUEST['limit']) != 0 ) ? $_REQUEST['limit'] : 5;
$offset = (intval($_REQUEST['offset']) != 0 ) ? $_REQUEST['offset'] : 0;

$sql = "SELECT * FROM wuno_inventory WHERE 1 ORDER BY id ASC LIMIT $limit OFFSET $offset";
// Make sure to handle invalid offset values properly, 
// as they are not validated from the last request.

// Prepare the $output structure (will become a json object string)
$output = array(
  'lim'=>$limit,
  'offs'=>$offset+$limit,
  'html'=>''
);
try {
  $stmt = $DB_con->prepare($sql);
  $stmt->execute();
  $results = $stmt->fetchAll();
} catch (Exception $ex) {
  $output['html'] .= $ex->getMessage();
}
if (count($results) > 0) {
  foreach ($results as $res) {
    $output['html'] .= '<tr class="invent">';  
    $output['html'] .= '<td>' . $res['wuno_product'] . '</td>';  
    $output['html'] .= '<td>' . $res['wuno_alternates'] . '</td>';  
    $output['html'] .= '<td>' . $res['wuno_description'] . '</td>';  
    $output['html'] .= '<td>' . $res['wuno_onhand'] . '</td>';  
    $output['html'] .= '<td>' . $res['wuno_condition'] . '</td>';  
    $output['html'] .= '</tr>';   
  }
}
// Now encode $output as a json object (string) and send it to the client
header('Content-type: application/json; charset=utf-8');
echo json_encode($output, JSON_HEX_TAG|JSON_HEX_AMP|JSON_HEX_APOS|JSON_HEX_QUOT|JSON_FORCE_OBJECT);

Upvotes: 1

Cesar Bielich
Cesar Bielich

Reputation: 4945

I do this exact thing you want on my site http://www.flixnetforme.com/ where as you can see as you scroll to the bottom of the page the next set of records is loaded. I have over 150,000 records but it only loads 36 at a time when the user scrolls to the bottom of the page.

The way you want to do this is is by loading your first initial records through ajax and not hardcode them into the page.

index.php

$(document).ready(function(){
    var last_id;
    if (last_id === undefined) {
        genres = $("#genres").val();
        $.ajax({
            type: "POST",
            url: "includes/getmorefirst.php?",
            data: "genres="+ genres,
            success: function(data) {
                $( ".append" ).append(data);
            }
        });
    };
};

<html><div class="append"></div></html>

In this example when the user gets to the page and is loaded it calls getmorefirst.php and return the first records.

  • getmorefirst.php is a file that will load the first set amount of records you want to show when the user gets to the page. In my case I load 36 records at a time when a user scrolls to the bottom of my page.

getmorefirst.php

$sql = "SELECT * FROM table ORDER BY ID ASC LIMIT 36"
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
    $last_id = $row['ID'];
    echo '<div>'.$row['column'].'</div>';
    echo '<div style="display:none" class="last_id" id="'.$last_id.'"';
}

The last_id div is important so that ajax will know the last record sent and which one to pick up after when it loads the next set of 36 records.

.append is the div where I am appending the data from ajax when the user its the bottom.

.last_id is your key to knowing how to load the next set of records. In whatever order you send the records back its important that ajax knows the last ID of the record loaded so it knows where to start loading the next time ajax calls for more records. In my case when the users scrolls to the bottom of the page.

when user scrolls to bottom of index.php

if($(window).scrollTop() === $(document).height() - $(window).height()) {
        last_id = $(".last_id:last").attr("id");
        $.ajax({
            type: "POST",
            url: "includes/getmore.php?",
            data: "last_id="+ last_id,
            success: function(data) {
                $( ".append" ).append(data);
            }
        });
        return false;
    };
};

last_id = $(".last_id:last").attr("id"); will get the last ID sent.

data: "last_id="+ last_id, will send the last id to getmore.php so it knows the last id of the record sent.

getmore.php

$sql = "SELECT * FROM table WHERE ID > '$last_id' ORDER BY ID ASC LIMIT 36"
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
    $last_id = $row['ID'];
    echo '<div>'.$row['column'].'</div>';
    echo '<div style="display:none" class="last_id" id="'.$last_id.'"';
}

As you can see getmore.php will return the next 36 records but AFTER the last_id sent.

Hope this make sense and gives you a start.

Upvotes: 1

Related Questions