Paging logic for multiple tables in PHP

Question

I want to prepare a listing page in PHP with multiple mysql tables. So that can be N numner of tables and the records in each table can be anything and I want to implement paging for that. Here is the information which is avaialbe to me before writing the paging logic:

define('RECORDS_PER_PAGE_SPLIT', 15);
$htmlTableList = array(
    '_split_1' => 25,
    '_split_2' => 4,
    '_split_3' => 60
);

$currentPage = 1;
if (isset($_REQUEST['page'])) {
    $currentPage = $_REQUEST['page'];
}
if ($currentPage == '' || $currentPage == '0') {
    $currentPage = 1;
}
$offset = ($currentPage - 1) * RECORDS_PER_PAGE_SPLIT;

I want a PHP function (which will contain the logic of multiple-tables-paging) which will return me following array:

//For page=1 in request:
Array{
    0 => "SELECT * FROM _split_1 LIMIT 0, 15"
}

//For page=2 in request:
Array{
    0 => "SELECT * FROM _split_1 LIMIT 15, 25",
    1 => "SELECT * FROM _split_2 LIMIT 0, 4",
    2 => "SELECT * FROM _split_3 LIMIT 0, 1",
}

//For page=3 in request:
Array{
    0 => "SELECT * FROM _split_3 LIMIT 1, 16"
}

//For page=4 in request:
Array{
    0 => "SELECT * FROM _split_3 LIMIT 16, 31"
}

Please provide the function if someone has already written this type of paging logic. Note: This is just in example, but in my live application number of records in each table will be in lacs. And number of tables are also not fixed. It can be 1 to 5 anything.

Please help.

Answer 1

To elaborate on my answer above... what you want to do is UNION all the result sets together, then you can let MySQL do all the hard work for you.

So your code would be something like this:

define('RECORDS_PER_PAGE_SPLIT', 15);

$currentPage = !empty($_REQUEST['page']) ? (int) $_REQUEST['page'] : 1;

$offset = ($currentPage - 1) * RECORDS_PER_PAGE_SPLIT;

$query = "
  SELECT SQL_CALC_FOUND_ROWS *
  FROM (
    SELECT *
    FROM _split_1
    UNION ALL
    SELECT *
    FROM _split_2
    UNION ALL
    SELECT *
    FROM _split_3
  ) u
  LIMIT $offset, ".RECORDS_PER_PAGE_SPLIT."
";

// This contains results for the page
$result = mysqli_query($conn, $query);

// If you want the total number of results to display or for other calulations
$query = "
  SELECT FOUND_ROWS()
";
$foundRows = mysqli_query($conn, $query);

If you're not bothered about knowing the total number of results, you can scrap the second query and remove the SQL_CALC_FOUND_ROWS from the first query.

Here is more information about SQL_CALC_FOUND_ROWS/FOUND_ROWS()

Note also that if you are going to apply a LIMIT you should also apply an ORDER BY to guarantee sensible results.

Answer 2

If you create a UNION query which lists all records of split_1, _2 and _3 in one table and in the desired sequence you won't need any complicated paging logic. Like in (example for page 4)

select * from _split_1 
union select * from _split_2
union select * from _split_3
limit 3 * 15, 15