Pagination AND Column Sorting?

PHP
1

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

Hmm…I tried the jQuery tablesorter plugin and nothing happens at all. Does it not work with dynamically generated data?

3

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.

4

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.

5

Don’t you have the page number on the URL?

6

Here’s the general concept.


$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.

7

without seeing your code I have no idea where the bug is.

it sounds like you just need to do some basic [COLOR=#ff6600]debugging

[/COLOR]1) start at the top of the script as specified in your form’s action attribute and add

 
echo 'got here'; die();

  1. run your form to check if it gets to your php script

  2. 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.

  3. 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.

  1. 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.

8

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:

$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:


        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:


$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?

9

$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.

10

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.

11

Nevermind, I got it to work. I had to delete all previous PHP-related sorting code. Thanks to all for the generous assistance!