How to browse every 10 records using MYSQL and jquery ajax calls

Question

Hi i want to browse every 10 records on a click. The AJAX call should fetch next 10 records each from SQL QUERY when clicked on next or previous. At the initial page i have a SQL QUERY which fetches 10 records

SELECT VENUENAME FROM VENUES LIMIT 10

<a style="float:right" id="next-nav" href="#">Next</a>


$("#next-nav").click(function(){
 $("#hidNavigation").val(parseInt($("#hidNavigation").val()) + 1);
 $.ajax({
  type: "POST",
  url: "loaddynamic.cfm?template=mapresults&startrow=" +  $("#hidNavigation").val(),
  cache: false,
  dataType: "json",
  success: function(data){
   //show venues

  }  
 });
});

The query i am using inside AJAX page is

SELECT VENUENAME FROM VENUES LIMIT startrow, 10

I want records fetched to be unique and also the make the links(prev/next) non-clickable when it is at the start/end of the last record. Please help me. I am using MYSQL DB and jquery.

Answer 1

Links cannot be made non-clickable. What you can do is hide them and replace them with static text, or you can catch clicks which would get you out of bounds in your click handler function. For the 'previous' link, you can do that by verifying that the value of #hidNavigation is larger than 0. To find out whether you're at the end of the list, you'd first have to query the number of available venues like so:

SELECT COUNT(*) FROM VENUS;

Once you have that information on the client, disable the 'next' link if (in pseudocode) (#hidNavigation+1)*10 >= number of venues.

Answer 2

Your query should look like so:

SELECT VENUENAME FROM VENUES ORDER BY VENUENAME LIMIT numrows OFFSET startrow

Make sure that you parameterize your query so startrow is passed in as a placeholder. Also, I added the ORDER BY clause and just threw VENUENAME in... you might want ID instead, but you'll almost certainly want some sort of ordering.

To make the links non-clickable, you'll need to get a count of rows so you can determine the total number of pages.

SELECT COUNT(*) FROM VENUES

To deal with the UI element, I'd use the jQuery pager plugin rather than try to roll your own: http://github.com/jonpauldavies/jquery-pager-plugin/tree/master