SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pagination AND Column Sorting?

    I have database records that are called from the server and displayed in a table. I have a limit of 10 records per page and a pagination system in place.

    How can I include sorting by column heading while ensuring that I stay on the same page?

    Is it best to use Javascript for this?

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    without seeing your code it's a bit hard to see exactly what you have done, but in general you should be able to send to the server the clicked column name which is then included in your sql query to retrieve the page's records as an "order by" the clicked column.

  3. #3
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm doing exactly that, but when the clicked column name is sent back to the page, the sql query resets everything back to page 1.

  4. #4
    SitePoint Addict
    Join Date
    Aug 2007
    Location
    GR
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't you have the page number on the URL?

  5. #5
    SitePoint Guru
    Join Date
    Oct 2006
    Location
    Queensland, Australia
    Posts
    852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the general concept.

    PHP Code:
    $columns = array('name''description''price');

    echo 
    '<thead>';
    foreach(
    $columns as $col)
    {
        if(
    $_GET['orderby'] == $col && $_GET['dir'] == 'asc') {
            
    $sortDir 'desc';
        } else {
            
    $sortDir 'asc';
        }
        echo 
    "<th><a href='?page={$_GET['page']}&orderby=$col&dir=$sortDir'>".ucwords($col).(($sortDir == 'asc') ? ' ^' ' v')."</a></th>";
    }
    echo 
    '</thead>'
    That bit of code will echo out a table heading, with each column being a sortable link. By default, columns will be sorted in ascending order. If however, the column is currently being 'sorted by', it will be set to the opposite of its current sort direction. I've also included a little glyph to indicate the sort direction, though you'd probably want to replace these with an image or something a little nicer such as the ▼ and ▲ glyphs. Note also, that the page number is included is included by simply using $_GET['page']. If $_GET['page'] is not set, then this will just be a blank value which should default to page #1 in your code.

    And no, it is best not to use JavaScript, especially for collections of records which span multiple pages, as the javascript-based sorting will only sort the contents of the current page obviously, creating a rather confusing and misleading scenario for the user who may think that all the records have been sorted.

  6. #6
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bopjo1 View Post
    I'm doing exactly that, but when the clicked column name is sent back to the page, the sql query resets everything back to page 1.
    without seeing your code I have no idea where the bug is.

    it sounds like you just need to do some basic debugging

    1) start at the top of the script as specified in your form's action attribute and add

    Code:
     
    echo 'got here'; die();
    2) run your form to check if it gets to your php script

    3) then move the above echo/die down, line by line if you have to, and add appropriate echo statements to display values of variables and then run the form again each time you move the echos.

    4) as part of 3) insert the echos in each part of conditional blocks (IF blocks) to check your code logic is correct

    keep doing this until your echos show something is not right. then back track your code to fix the error.

    5) keep repeating 3) and 4) until you get to the end of your script and it works ok.

    if you have a debugger, then debugguing will be easier as you can set break points and check values of variables which is essentially what the above steps are doing.

  7. #7
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies everyone. I'm having a tough time figuring out how to pass my page number through the URL. I'm not sure what variable contains that value. (I got the pagination script from a book). At the top of my document I have the script that sets up the values thusly:

    Code:
    $display = 3;
    if (isset($_GET['p']) && is_numeric($_GET['p'])) {
    	$pages = $_GET['p'];
    } else {
    	$q = "SELECT COUNT(id) FROM links_clicked";
    	$r = mysql_query($q, $db);	
    	$row = mysql_fetch_array($r, MYSQL_NUM);	
    	$records = $row[0];
    	$_SESSION['records'] = $records;
    	
    	if ($records > $display) {
    		$pages = ceil($records/$display);
    	} else {
    		$pages = 1;
    	}
    }
    
    if (isset($_GET['s']) && is_numeric($_GET['s'])) {
    	$start = $_GET['s'];
    } else {
    	$start = 0;
    }

    Then lower down are the links that control the pagination itself:

    Code:
            if ($pages > 1) {
    	     $current_page = ($start/$display) + 1;
    	}
    	if ($current_page != 1) {
    		echo '<li><a href="clicks.php?s=' . ($start - $display) . '&p=' . $pages . '">&laquo; prev</a></li>';
    	}
    	for ($i = 1; $i <= $pages; $i++) {
    	if ($i != $current_page) {
    	     echo '<li><a href="clicks.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '">' . $i . '</a></li>';
    	} else {
    	     echo '<li class="current">' . $i . '</li>';
    	}
    	} // end of for loop
    	if ($current_page != $pages) {
    		echo '<li><a href="clicks.php?s=' . ($start + $display) . '&p=' . $pages . '">next &raquo;</a></li>';
    	}
    My sql query is:

    Code:
    $sql = "SELECT first_name, last_name, email, page, link_name, DATE_FORMAT(date_clicked, '%m/%d/%Y') AS Date
              FROM links_clicked
    	  ORDER BY $order[$o]
    	  LIMIT $start, $display";
    Is $start the variable that contains the current page?

  8. #8
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    $start is the record (row) number (values starting from 0). Essentially you multiply by 10 (records per page) to get the page number.

    One thing to bear in mind, if you sort the results by a different column then the whole order of the results will likely change - i.e. the 1st record when sorted by one column may be the 21st record when sorted by another column. Therefore starting again at page 1 is not unexpected.

    But if you just want to be able to sort the records currently displayed (i.e. not retrieve any new records) then this jQuery example may be of use.
    Ian Anderson
    www.siteguru.co.uk

  9. #9
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, all I need to do is sort the currently displayed records. I've trying to do this with PHP and it's making my brain explode! I thought jQuery might be the answer. Thanks siteguru for the link to that plugin. I'll give it a try.

  10. #10
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm...I tried the jQuery tablesorter plugin and nothing happens at all. Does it not work with dynamically generated data?

  11. #11
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind, I got it to work. I had to delete all previous PHP-related sorting code. Thanks to all for the generous assistance!


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
  •