jQuery Pagination and MySQL $limit, $offset

[ripcurlksm]

I am trying to use the jQuery pagination plugin with my PHP/MySQL website – It appears I have to either use Ajax or use PHP to write the jQuery pagintion header so I can control the $offset and $limit, in order to control the pagination –

What is the best way to combine javascript, PHP and MySQL so that they can communicate with eachother? Here is my PHP page and SQL

<script type="text/javascript" src="include/jquery-latest.js"></script>
<script type="text/javascript" src="include/tablesorter/addons/pagination/jquery.pagination.js"></script>
<link rel="stylesheet" href="include/tablesorter/addons/pagination/pagination.css" type="text/css" media="print, projection, screen" />
<script type="text/javascript">

		function pageselectCallback(page_id, jq){
					$('#Searchresult').text("Showing search results "+((page_id*10)+1)+"-"+((page_id*10)+10));
                }
		
        $(document).ready(function(){
			// Create pagination element
            $("#Pagination").pagination(300, {
				num_edge_entries: 2,
				num_display_entries: 8,
                callback: pageselectCallback
            });
        });

        </script>

<div id="Pagination" class="pagination"></div>

		<br style="clear:both;" />
		<div id="Searchresult">
			Showing search results 1-10 ...
		</div>


<?php

	$limit = 10;
	$offset = 0;

	dbConnect();
	$sql = "SELECT * FROM emt_company LIMIT $offset, $limit";
	$result = mysql_query($sql) or die(mysql_error());
	$num = mysql_num_rows($result);
	

	while($row = mysql_fetch_assoc($result)){
		
		$name = $row['name'];
		echo "$name<br>";
	
	}


?>

[SoulScratch]

You could have a seperate standalone page for the PHP which grabs the query and call it using $.ajax - could use a GET method to send the limit and offset, and in the standalone PHP page use $_GET[‘offset’] and $_GET[‘limit’] to grab the query.

[ripcurlksm]

Could you give me a rough example of a php page and the calls so i can see the structure?

[SoulScratch]

Roughly the same as what your php snippet has, except using $_GET values to query the database, and just echo the output in there and then grab the html with $.ajax callback. I’m a little weary from replying to threads all day, but if someone else doesn’t reply I may make a little sample.

[ripcurlksm]

cool, i understand what you are saying up until the ajax callback – thanks for your time

[ripcurlksm]

Ok so maybe something like this:

a PHP page using $_GET to build the query and output HTML

<?php
	dbConnect();
	$sql = "SELECT * FROM emt_company LIMIT ".$_GET['offset'].", ".$_GET['limit']."";
	$result = mysql_query($sql) or die(mysql_error());
	$num = mysql_num_rows($result);
	

	while($row = mysql_fetch_assoc($result)){
		
		$name = $row['name'];
		echo "$name<br>";
	}
?>

The search results page with pagination:

<?php
include('include/scripts.inc.php');
include('conn/conn.inc.php');
?>
<script type="text/javascript" src="include/jquery-latest.js"></script>
<script type="text/javascript" src="include/tablesorter/addons/pagination/jquery.pagination.js"></script>
<link rel="stylesheet" href="include/tablesorter/addons/pagination/pagination.css" type="text/css" media="print, projection, screen" />
<script type="text/javascript">
            
		function pageselectCallback(page_id, jq){
					$('#Searchresult').text("Showing search results "+((page_id*50)+1)+"-"+((page_id*50)+10));
                }
                
		var $set = 50;
		
        $(document).ready(function(){
			// Create pagination element
            $("#Pagination").pagination($set, {
				num_edge_entries: 2,
				num_display_entries: 8,
                callback: pageselectCallback
            });
        });
      
	    $.ajax({
				  url: "test5-1.php?offset=0&limit=50",
				  cache: false,
				  success: function(html){
				    $("#results").append(html);
				  }
});
    
        </script>

<div id="Pagination" class="pagination"></div>

		<br style="clear:both;" />
		<div id="Searchresult">
			Showing search results 1-50 ...
		</div>


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

[SoulScratch]

Hm, learned quite a bit implementing this.

Here’s the initial page that calls the ajax:

<?php
    error_reporting( E_ALL );
    $conn = mysql_connect( 'localhost', 'root', 'mypass');
    if ( is_resource( $conn ) ) {
	if ( !mysql_select_db('foo', $conn) ) {
	    echo '<p>Can not select db.</p>';
	}
	echo '<p>Yay</p>';
	$sql = 'SELECT COUNT(*) from news;';
	$res = mysql_query($sql);
	if ( is_resource( $res ) ) {
	    $total = mysql_result( $res, 0 );
	    echo 'yay';
	}
	//$sql = 'CREATE DATABASE IF NOT EXISTS foo; USE foo; CREATE TABLE news ( news_id INT AUTO_INCREMENT, story TEXT, primary key ( news_id ) );';
	/*
	for ( $i=0; $i<30; ++$i ) {
	    $sql.= 'INSERT INTO news(story) VALUES ("<h2>' . $i . '</h2><p>Lorem ipsum dolor sit amet.</p>");';
	}
	$queries = explode( ';', $sql );
	foreach ( $queries as $value ) {
	    $query = mysql_query( $value );
	    if ( !$query ) {
		echo mysql_error();
	    }
	}
	*/
    }
?>
<!doctype html>
<html>
<head>
    <script type="text/javascript" src="http://www.d-scribe.de/webtools/jquery-pagination/lib/jquery/jquery-compressed.js"></script>
    <script type="text/javascript" src="http://www.d-scribe.de/webtools/jquery-pagination/lib/jquery_pagination/jquery.pagination.js"></script>
    <script type="text/javascript">
	function pageselectCallback(page_id, jq){
	    var first = (page_id*10)+1, second = (page_id*10)+5;
	    $('#Searchresult').text("Showing search results " + first + '-' + second);
	    $.ajax({
		type:'GET',
		url:'ajax.php',
		data:'offset=' + first + '&limit=5',
		success:function(msg) {
		    $('#ajaxContent').html(msg);
		}
	    });
	}
	$(document).ready(function(){
	    $("#Pagination").pagination( <?php echo $total;?>, { 
		num_edge_entries: 2,
		num_display_entries: 8,
		callback: pageselectCallback 
	    });

	    pageselectCallback(0);
	});
    </script>
    <title>database</title>
</head>
<body>
    <div id="Pagination"></div>
    <div id="Searchresult"></div>
    <div id="ajaxContent"></div>
</body>
</html>

Here’s the ajax.php page:

<?php
    $offset = $_GET['offset'];
    $limit = $_GET['limit'];
    $conn = mysql_connect( 'localhost', 'root', 'mypass');
    if ( is_resource( $conn ) ) {
	if ( !mysql_select_db('foo', $conn) ) {
	    echo '<p>Can not select db.</p>';
	}
	$result = mysql_query('SELECT * from NEWS LIMIT ' . $offset . ',' . $limit);
	if ( is_resource( $result ) ) {
	    while ( $row = mysql_fetch_assoc( $result ) ) {
		echo $row['story'];
	    }
	}
    }
?>

Hope this helps out, you had the right idea. I really should be going to sleep now, as this took the last of my brain cells remaining.

[ripcurlksm]

Excellent. Wonderful. Thank You!

[SoulScratch]

@ripcurlksm

Excellent. Wonderful. Thank You!

Sure, lol I remember contemplating whether or not I should’ve done that at like 12 last night

[ripcurlksm]

Yea I read it last night after you posted it – I continued graphic design/layout on the project and waited until this morning to give it a look.

If it means anything, I slept better last night because you slept worse Thanks

EDIT: Also I see you inserted PHP tags inside your javascript… funny, I tried this, but it caused the script to break and I assumed it wasn’t possible-- expecting client-side scripting to be able to be dynamic with server-side scripting… i suppose that is the reason for Ajax. Am I correct?

[SoulScratch]

This would break if you tried to do it in an external .js file which was sent as application/javascript, or whatever the Content-Type http header for JS is, unless you made some .php page that was sent as js, you can’t do it like that.

However, if you do it in a page which is parsed as php, such as .php then it’s fine, because it’s just outputting text, the same as typing bare HTML.

If you tried to do this in an .html page which wasn’t properly configured by the server to be parsed as php, then it probably wouldn’t work either and break on the ‘<?’.

AJAX is just a buzzword used to mean a variety of techniques which prevent the page from fully reloading, and can include using javascript to insert an iframe to give an “ajax” effect, the proper “ajax” way really is to create a new XMLHttpRequest object in a modern browser and open up a new http request.

Let me know if you’re still confused.

[ripcurlksm]

Gotcha –

To have pagination at the top and bottom of the search results, could I duplicate and rename the pagination div and double-up on the javascript call to the new div id?

[SoulScratch]

That, or just replicate the html for every invocation, to the bottom ( second ) pagination element.

[ripcurlksm]

ehh, Im integrating this into my page and the pagination is showing up, but the links are broken because I am using a <base url=“”> tag.

Normally the pagination links (previous, 1,2,3, next) to this:

whatever.php#

But with the base url implemented on the page, it looks links to this:

http://localhost/project/#

Can I modify the javascript? How can I fix this?

[Keyser_Soze]

Hi,

I used table sorter and pagination plugin “pager” but i have the same problem… i don’t know how to insert limit and offset

It seems that we don’t use the same pagination script

here is the pagination script i’m using:

http://tablesorter.com/docs/example-pager.html

I want to keep tablesorter
http://tablesorter.com

Can any one help me? :sick:

Thanks

[Paul_Wilkins]

@Keyser_Soze

i don’t know how to insert limit and offset

Look at the HTML source code of the page to learn how.

The page uses the HTML form element with a class of “pagesize” to determine the limit. The offset is handled in the “pagedisplay” class.

<div id="pager" class="pager">
	<form>
		<img src="../addons/pager/icons/first.png" class="first">
		<img src="../addons/pager/icons/prev.png" class="prev">
		<input type="text" class="pagedisplay">
		<img src="../addons/pager/icons/next.png" class="next">
		<img src="../addons/pager/icons/last.png" class="last">
		<select class="pagesize">
			<option selected="selected" value="10">10</option>
			<option value="20">20</option>
			<option value="30">30</option>
			<option value="40">40</option>
		</select>
	</form>
</div>

[Keyser_Soze]

@pmw57

Look at the HTML source code of the page to learn how.

The page uses the HTML form element with a class of “pagesize” to determine the limit. The offset is handled in the “pagedisplay” class.

<div id="pager" class="pager">
	<form>
		<img src="../addons/pager/icons/first.png" class="first">
		<img src="../addons/pager/icons/prev.png" class="prev">
		<input type="text" class="pagedisplay">
		<img src="../addons/pager/icons/next.png" class="next">
		<img src="../addons/pager/icons/last.png" class="last">
		<select class="pagesize">
			<option selected="selected" value="10">10</option>
			<option value="20">20</option>
			<option value="30">30</option>
			<option value="40">40</option>
		</select>
	</form>
</div>

Thank you so much, I apreciate your help

I guess i was misunderstood … you answered my last topic about the pagination and you told me to use DataTables … but, i’m still trying to find a way how to add LIMIT to tablesorter and its plugin pager

I did the pagination and I used the script, you gave, at the bottom of my table and it works… the only problem is to limit the [SELECT] results for big number of lines on the sql table (so the program don’t load thousands of lines!)

Merci

[Paul_Wilkins]

@Keyser_Soze

I did the pagination and I used the script, you gave, at the bottom of my table and it works… the only problem is to limit the [SELECT] results for big number of lines on the sql table (so the program don’t load thousands of lines!)

Tablesorter and the paginator cannot work in the way you require.
They are designed to only sort and paginate the complete dataset that is on the page.