Limit number of results using column value as a count

Question

I have two queries, one which will get a list of IDs from the main table, and another which will get all records in another table that relate to that ID.

I'd like to split it into pages of results, but my google searches are only coming up with people who want to have a certain number of results per column value, not who want to limit the overall count by it.

The main table has a column that contains the number of records, so actually reading the other table isn't needed. The limit would be used as a minimum value, so if there are still records left in the current group after the limit, it'll continue displaying them. That'd be somehow calculated as part of the offset so it can start in the correct place.

Here's an example of what I mean:

Table 1:

ID | Records
1  |    2
2  |    3
3  |    28
4  |    7
...

Table 2 (contents don't need to be known for this question):

ID | GroupID | Value
1  |    1    |   x
2  |    1    |   x
3  |    2    |   x
4  |    2    |   x
5  |    2    |   x
6  |    3    |   x
   ...

If the limit was given as 3 for example, both 1 and 2 should display on the first page, since just 1 by itself is under the limit. The next page will then start on 3, and that'll take up the entire page.

I could just manually count up using PHP until I reach the limit, though it might end up going slow if there were a lot of pages (I've no idea if mysql would be any better in that regard though). Here's a quick example of how that'd work to get the offset:

$page = 2;
$limit = 40;

$count = 0;
$current_page = 1;

$query = 'SELECT ID, Records FROM table1';
$stmt = $conn->prepare($query);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $count += $row['Records'];
    if($count > $limit){
        $current_page ++;
        $count = 0;
        if($current_page == $page){
            $start_id = $row['ID'];
            break;
        }
    }
}

Answer 1

Here's the result I ended up with, I ended up basing it off Rajdeeps answer but tweaked so that it'll allow sorting.

$query = 'SELECT ID, Records FROM table1 ORDER BY something';
$stmt = $conn->prepare($query);
$stmt->execute();
$id_array = array(); // For storing each ID
$current_ids = array(); // For storing the current ID pages
$initial_ids = array(); // For storing the first page, in case the page is too high

$count = 0;
$current_page = 1;
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $already_reset = false;

    // Move the session to the next page if it's more over the limit than under
    // For example, if a limit of 10 has 6 records already, and the next ID is 9 records, then start a new page
    if($id_array and $count + $row['Records'] / 2 > $max_links){
        array_push($id_array, $last_id);
        $current_page ++;
        $count = 0;
        $already_reset = true; // Set a marker so that if this ID is larger than the limit then don't start a 2nd new page
    }

    // Backup the first results in case page is too high and needs to be reset to 1
    if($current_page == 1){
        array_push($initial_ids, $row['ID']);
    }

    $count += $row['Records'];

    // The values that appear here are the results for the selected page
    // They may not be in sequence so store the individual IDs
    if($_GET['page'] == $current_page){
        array_push($current_ids, $row['ID']);
    }

    // Start a new page if over the limit
    if($count > $max_links and !$already_reset){
        $current_page ++;
        $count = 0;
        array_push($id_array, $row['ID']);
    }
    $last_id = $row['ID'];
}
array_push($id_array, $last_id);
$total_pages = count($id_array);

// If page is invalid revert to default
if(!$current_ids){
    $current_ids = $initial_ids;
    $_GET['page'] = 1;
}

$_GET['page'] = max(1, min($total_pages, intval($_GET['page'])));

$current_ids is an array of the IDs for the current page, and $total_pages is pretty self explanatory.

Answer 2

If you only need to create the "Next" button, you could try this way:

select t1.ID, t1.Records
from table1 t1
left join table1 t2
    on  t2.ID < t1.ID
    and t2.ID > :last_selected_id_1
where t1.ID > :last_selected_id_2
group by t1.ID, t1.Records
having coalesce(sum(t2.Records), 0) < :limit
order by t1.ID

:last_selected_id_x is the last ID from the current page or 0 for the first page.

http://rextester.com/FJRUV28068

You can use MySQL session variables to create page links:

select page, min(ID) as min_id, max(ID) as max_id
from (
    select ID
         , @page := case when @sum = 0 then @page + 1 else @page end as page
         , @sum  := case when (@sum + Records) >= :limit
             then 0 
             else @sum + Records
         end as sum
    from table1
    cross join (select @sum := 0, @page := 0) initvars
    order by ID
) sub
group by page

The result would look like:

page | min_id | max_id
   1 |      1 |      2
   2 |      3 |      3 
   3 |      4 |      4 

http://rextester.com/LQVJWP18655

Note that it is officially (as of documentation) not recomended to use the session variables like that (read and write in one statement). Feature versions may break your code.

Answer 3

Updated:

A "custom" Pagination is what you're looking for. So if you're planning to hop from page to page, you can't use hardcoded $page and $current_page values. Those values should be generated dynamically once you're on a particular page. In fact, you should have the ID column value in the query part of the URL so that the pagination links could satisfy your business logic.

Assuming the fact that your ID column value starts from 1, your code should be like this:

$id = isset($_GET['id']) && is_numeric($_GET['id']) ? $_GET['id'] : 1;
$limit = 40;

// Display query results based on particular ID number
$query = 'SELECT ID, Records FROM table1 WHERE ID >= ' . $id;
$stmt = $conn->prepare($query);
$stmt->execute();
$rowCount = $stmt->rowCount();
if($rowCount){
    $total = 0;
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        if($total <= $limit){
            $total += $row['Records'];
            // Display $row details here
        }else{
            // Get the next $id value
            $id = $row['ID'];
            break;
        }
    }
}

// Display relevant ID links
$query = 'SELECT * FROM table1';
$stmt = $conn->prepare($query);
$stmt->execute();

$idArr = array();
$total = 0;
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    if($total == 0){
        $idArr[] = $row['ID'];
    }
    if($total <= $limit){
        $total += $row['Records'];
    }else{
        $total = 0;
    }
}

foreach($idArr as $idValue){
    if($idValue == $id){
        echo '<a>'.$idValue.'</a> ';
    }else{
        echo '<a href="?id='.$idValue.'">'.$idValue.'</a> ';
    }
}

---

Sidenote: If the ID column value of your table doesn't start from 1, then use a separate query to get the first ID value and update the following statement,

$id = isset($_GET['id']) && is_numeric($_GET['id']) ? $_GET['id'] : <FIRST_ID_VALUE>;