Member Avatar for Albert Pinto

Hi people,

Need some help in keeping query string values intact while moving across pages via pagination.

Thing is that I'm fetching records from mysql table and displaying it in tabular format. Its like...

I have a page "records.php"

In phpmyadmin there is "userdetails" table with columns as...... first_name, last_name, address, contact_number

In HTML:

First-Name | Last-Name | Address | Contact-Number

I'm trying to sort these elements.... I mean sort by first name or contact number etc...

So I've made <th> elements as links and I'm using querystring... on click the links are directing to same page records.php

so its like...

<th><a href="records.php?idfield=first_name">First Name</th>

<th><a href="records.php?idfield=last_name">Last Name</th>

and so on....

then I initialize on top a variable.

$colfield= $_GET['idfield'];

and then this variable $colfield, I'm using in mysql query...

like

$sql ="SELECT * FROM userdetails order by $colfield ASC";

So, here I'm passing idfield value as per the column name in the database table and then using it in order by clause in sql query.

This works fine... Issue starts when pagination comes into play.... Say I sort by first name, It gets sorted, but when I go to Page 2... the first_name field won't pass in query string... and the order by clause fails... and no records are displayed.

Is there any way to keep query string intact across pagination links....??

Thanks

  • 5 Contributors
  • 4 Replies
  • 1K Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Short, practical addition: preserve the full query string when emitting pagination links so the active sort/filter does not get dropped. Rather than hard-coding only the page and idfield values into each anchor, merge the current query parameters and replace only the page value. That follows 's suggestion and the URL-format idea from , while still using LIMIT/OFFSET for paging as mentioned.

Example (safe) pattern:

$allowed = array('first_name','last_name','address','contact_number');
$sort   = (isset($_GET['idfield']) && in_array($_GET['idfield'],$allowed)) ? $_GET['idfield'] : 'first_name';
$order  = (isset($_GET['order']) && strtolower($_GET['order']) === 'desc') ? 'DESC' : 'ASC';
$page   = max(1, (int)($_GET['page'] ?? 1));
$perPage = 20;
$offset = ($page - 1) * $perPage;

/* prepared statement for LIMIT/OFFSET; ORDER BY uses whitelisted values */
$stmt = $pdo->prepare("SELECT * FROM userdetails ORDER BY $sort $order LIMIT :limit OFFSET :offset");
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

/* build links without losing other params */
$params = $_GET;
$params['page'] = $i; // loop index
$link = 'records.php?' . http_build_query($params);

Cautions and tips: never interpolate unchecked GET values into ORDER BY — whitelist column names and allowed directions. Treat page and perPage as integers and bind LIMIT/OFFSET. Escape generated URLs with htmlspecialchars() when outputting. Preserve any extra filter/sort params (order, search terms, etc.) in the same merge so navigating pages keeps the same view.

Recommended Answers

All 4 Replies

Member Avatar for paulkd

You could append the $_GET['idfield'] to the next page link

e.g. (untested) <a href="records.php?page=<?php echo $nextPageNum; ?>&idfield=<?php echo $_GET['idfield']; ?>

Member Avatar for arunmagar

You can use limit in a query. And pass accordingly for each page.
I am not sure but for pagination pupose limit will help you.

Member Avatar for Webville312

I agree with paulkd on this one. Unless there is more you need to do ...

Member Avatar for Member #120589

You provide no code, so it's difficult to know for sure how you're paginating.

Set up a concrete url format for this, e.g.

users.php?page=int&sortorder=field

Alternatively, this could be done via ajax, where the params are passed from a js script to a php page and fresh data returned. Unless the url is important for your SEO, the this may be a better option.

Your page links (at the bottom of the table?) should be updated with the url parameters:

$activepage = 5; // from $_GET['page'] or provide a default - it would be more complicated than this in order to stop out-of-range values
$numpages = 30; // these would be calculated usually from your pagination script.
$so = 'firstname'; //again from $_GET['sortorder'] or provide a default - again need to stop invalid entry

$pages = '<ul class="pages">';
for($i=1;$i<=$numpages;$i++){
    $pages .= ($i == $activepage) ? "<li class='active'>$i</li>" : "<li><a href='users.php?page=$i&sortorder=$so'>$i</a></li>";
}
$pages .= "</ul>";

The issue is how are you choosing your sortorder? If via js, then you need to overwrite the url in the <li> tags otherwise, I'm assuming by submit dropdown, in which case you could php-ify that to show the current sortorder. The dropdown form action would need to be set to

"users.php?page=<?php echo $activepage;?>"

Loads of ways to skin a cat. Provide your code so we can see how best to help you.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.