SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    jQuery Pagination and MySQL $limit, $offset

    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

    HTML Code:
    <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>";
    	
    	}
    
    
    ?>

  2. #2
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Cross browser css bugs

    Dan Schulz you will be missed

  3. #3
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you give me a rough example of a php page and the calls so i can see the structure?

  4. #4
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Cross browser css bugs

    Dan Schulz you will be missed

  5. #5
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool, i understand what you are saying up until the ajax callback -- thanks for your time

  6. #6
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok so maybe something like this:

    a PHP page using $_GET to build the query and output HTML
    Code PHP:
    <?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:
    Code:
    <?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>

  7. #7
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hm, learned quite a bit implementing this.

    Here's the initial page that calls the ajax:

    Code:
    <?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:
    Code:
    <?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.
    Cross browser css bugs

    Dan Schulz you will be missed

  8. #8
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent. Wonderful. Thank You!

  9. #9
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ripcurlksm View Post
    Excellent. Wonderful. Thank You!
    Sure, lol I remember contemplating whether or not I should've done that at like 12 last night :P
    Cross browser css bugs

    Dan Schulz you will be missed

  10. #10
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  11. #11
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Cross browser css bugs

    Dan Schulz you will be missed

  12. #12
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  13. #13
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That, or just replicate the html for every invocation, to the bottom ( second ) pagination element.
    Cross browser css bugs

    Dan Schulz you will be missed

  14. #14
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    whatever.php#
    But with the base url implemented on the page, it looks links to this:
    Code:
    http://localhost/project/#
    Can I modify the javascript? How can I fix this?

  15. #15
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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?

    Thanks

  16. #16
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,702
    Mentioned
    101 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Keyser_Soze View Post
    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.

    Code html4strict:
    <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>
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  17. #17
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by pmw57 View Post
    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.

    Code html4strict:
    <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

  18. #18
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,702
    Mentioned
    101 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Keyser_Soze View Post
    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.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •