how to get specific rows page number in pagination

Question

I have a question on MySQL paging. A user's record is displayed on a table with many other user's record. and the table is sorted/paged. Now I need to display the page that containing the user's row directly after the user login. How can I achieve this?

create table t_users (id int auto_increment primary key, username varchar(100)); insert t_users(username) values ('jim'),('bob'),('john'),('tim'),('tom'), ('mary'),('elise'),('karl'),('karla'),('bob'), ('jack'),('jacky'),('jon'),('tobias'),('peter');

I searched the google but not found answer so please help

Answer 1

There are two steps for this:

1. Determine the row's position in your sorted table.

Copied and tweaked from: https://stackoverflow.com/a/7057822/2391142

Use this SQL...

SELECT z.rank FROM (
SELECT id, @rownum := @rownum + 1 AS rank
FROM t_users, (SELECT @rownum := 0) r
ORDER BY id ASC
) as z WHERE id=1;

...replacing the ORDER BY id ASC with whatever your actual sort order is. And replacing the number 1 in WHERE id=1 with the provided number in that index.php?u=id url.

2. Determine the page number based on the row's position.

Use this PHP to determine the needed page number...

$rows_per_page = 50;
$user_row_position = [result you got from step 1];
$page = ceil($user_row_position / $rows_per_page);

...replacing the 50 with whatever your real rows-per-page limit is, and putting the real SQL result in $users_row_position.

And voila. You'll have the destination page number in the $page variable and hopefully you can take it from there.

EDIT

After further discussion in the comments, use this bit of PHP:

$page = 0;
$limit = 10;

// If a user ID is specified, then lookup the page number it's on.
if (isset($_GET['u'])) {
    // Check the given ID is valid to avoid SQL injection risks.
    if (is_numeric($_GET['u'])) {
        // Lookup the user's position in the list.
        $query = mysqli_fetch_array(mysqli_query($link, "SELECT z.rank FROM (SELECT id, @rownum := @rownum + 1 AS rank FROM sites, (SELECT @rownum := 0) r WHERE online='0') as z WHERE id=" . $_GET['u']));
        $position = $query[0];
        if (is_numeric($position)) {
            // Convert the result to a number before doing math on it.
            $position = (int) $position;
            $page = ceil($position / $limit);
        }
    }
}

// If a page number is specified, and wasn't already set by looking a user, then lookup the real starting row.
if ($page == 0 && isset($_GET['page'])) {
    // Check your given page number is valid too.
    if (is_numeric($_GET['page'])) {
        $page = (int) $_GET['page'];
    }
}

// Notice that if anything fails in the above checks, we just pretend it never
// happened and keep using the default page and start number of 0. 

// Determine the starting row based off the page number.
$start = ($page - 1) * $limit;

// Get the list of sites for the provided page only.
$query = mysqli_query($link, "SELECT * FROM sites WHERE online='0' LIMIT " . $start . ", " + $limit);
while ($row = mysqli_fetch_array($query)) {
    // Stuff to render your rows goes here.
    // You can use $row['fieldname'] to extract fields for this row.
}