Sam
Sam

Reputation: 37

Ajax paginate sql query

I am working on a website that needs to paginate sql database results. The idea is like this, a user can click on a submit button with each US state's code to see all of the comments left by users from that state. For example clicking NY should display all people's comments from NY and clicking TX should display all of TX and so on. Displaying the results in one big list works but I want to paginate those results now. I am currently using this code located in its own paginate.php file which needs to be re-updated each time a new state is shown:

<?php

...

$rs_result = mysql_query($sql); 
$row = mysql_fetch_row($rs_result); 
$total_records = $row[0]; 
$total_pages = ceil($total_records / 10); 

for ($i=1; $i<=$total_pages; $i++) { 

echo "<a href='?page=".$i."'>".$i."</a> ";

}; 
?>

The code itself doesn't exactly work because it makes the page refresh. Doing that loses the memory of which state was previously clicked on. How can I change this to work with Ajax so I don't need to refresh the page. If you need more information let me know.

Upvotes: 0

Views: 723

Answers (2)

Jared Drake
Jared Drake

Reputation: 1002

What you would do is make a AJAX call using the same thing you have in your href attribute. After the response take the data and place it into an html element on your page. Check out jQuery's load method.


Oh ya, just to keep our little movement going. You should try to stop using mysql_query() and start working with mysqli instead. mysql is deprecated and it is no longer being supported. :/

Upvotes: 0

Mihai Iorga
Mihai Iorga

Reputation: 39724

You need to wrap the results in a DIV that has an assigned ID and change the URL's to point a javascript function (I would recommend jQuery). The function has to get the data that will be displayed in that div.

eg:

<div id="results">
</div> 

<?php
    for ($i=1; $i<=$total_pages; $i++) { 
        echo "<a href='?page=".$i."' onclick="getPage('".$i."'); return false;">".$i."</a> ";
    }; 
?>

// and script

// the script asks result.php for page and returns html result.
<script type="text/javascript">
    function getPage(page){
        $("#results").load("result.php", { page: page } );
    }
</script>

Upvotes: 1

Related Questions