CODEWITHSUNDEEP
phpmysql
Syirul Jo
Syirul Jo

Reputation: 51

Display specific no. of rows per pages?

I want to display specific no. of records per page? The code below is for pagination. I am trying to display 2 records per page. It succeed on the first page to display two records. The problem start to happen on the next page.Currently I have 10 records. It will display like this

Page 1 = 2 records.

    +---+---------+------------------+-------+
    |No.| File ID | File Desc.       |Status |
    +---+---------+------------------+-------+
    |1  | 400/4   | 1 - Test 1 400/4 |       |
    +---+---------+------------------+-------+
    |2  | 400/1   | 5 - Test 5 400/1 |       |
    +---+---------+------------------+-------+

Page 2 = 4 records (2 records + 2 records from page 3)

    +---+---------+------------------+-------+
    |No.| File ID | File Desc.       |Status |
    +---+---------+------------------+-------+
    |1  | 400/3   | 2 - Test 2 400/3 |       |
    +---+---------+------------------+-------+
    |2  | 400/3   | 1 - Test 1 400/3 |       |
    +---+---------+------------------+-------+
    |3  | 400/2   | 3 - Test 3 400/2 |       |
    +---+---------+------------------+-------+
    |4  | 400/2   | 2 - Test 2 400/2 |       |
    +---+---------+------------------+-------+

Page 3 = 6 records (2 records + 2 records from page 4 + 2 records from page 5)

    +---+---------+------------------+-------+
    |No.| File ID | File Desc.       |Status |
    +---+---------+------------------+-------+
    |1  | 400/2   | 3 - Test 3 400/2 |       |
    +---+---------+------------------+-------+
    |2  | 400/2   | 2 - Test 2 400/2 |       |
    +---+---------+------------------+-------+
    |3  | 400/2   | 1 - Test 1 400/2 |       |
    +---+---------+------------------+-------+
    |4  | 400/1   | 3 - Test 3 400/1 |       |
    +---+---------+------------------+-------+
    |5  | 400/1   | 2 - Test 2 400/1 |       |
    +---+---------+------------------+-------+
    |6  | 400/1   | 1 - Test 1 400/1 |       |
    +---+---------+------------------+-------+

Page 4 = 4 records (2 records + 2 records from page 5).

    +---+---------+------------------+-------+
    |No.| File ID | File Desc.       |Status |
    +---+---------+------------------+-------+
    |1  | 400/2   | 1 - Test 1 400/2 |       |
    +---+---------+------------------+-------+
    |2  | 400/1   | 3 - Test 3 400/1 |       |
    +---+---------+------------------+-------+
    |3  | 400/1   | 2 - Test 2 400/1 |       |
    +---+---------+------------------+-------+
    |4  | 400/1   | 1 - Test 1 400/1 |       |
    +---+---------+------------------+-------+

Page 5 = 2 records.

    +---+---------+------------------+-------+
    |No.| File ID | File Desc.       |Status |
    +---+---------+------------------+-------+
    |1  | 400/1   | 2 - Test 2 400/1 |       |
    +---+---------+------------------+-------+
    |2  | 400/1   | 1 - Test 1 400/1 |       |
    +---+---------+------------------+-------+

$num_rows = mysqli_num_rows($query);

    $per_page = 2;   // Per Page
    $page  = 1;

    if(isset($_GET["Page"]))
    {
        $page = $_GET["Page"];
    }

    $prev_page = $page-1;
    $next_page = $page+1;

    $row_start = (($per_page*$page)-$per_page);
    if($num_rows<=$per_page)
    {
        $num_pages =1;
    }
    else if(($num_rows % $per_page)==0)
    {
        $num_pages =($num_rows/$per_page) ;
    }
    else
    {
        $num_pages =($num_rows/$per_page)+1;
        $num_pages = (int)$num_pages;
    }
    $row_end = $per_page * $page;
    if($row_end > $num_rows)
    {
        $row_end = $num_rows;
    }

Below is the pagination

Total <?php echo $num_rows;?> Record : <?php echo $num_pages;?> Page :
<?php
if($prev_page)
{
    echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$prev_page&txtKeyword=$strKeyword&txtKeyword2=$strKeyword2'><< Back</a> ";

}

for($i=1; $i<=$num_pages; $i++){
    if($i != $page)
    {
        echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i&txtKeyword=$strKeyword&txtKeyword2=$strKeyword2'>$i</a> ]";

    }
    else
    {
        echo "<b> $i </b>";
    }
}
if($page!=$num_pages)
{
    echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$next_page&txtKeyword=$strKeyword&txtKeyword2=$strKeyword2'>Next>></a> ";

}
$dbConn = null;
?>

Upvotes: 0

Views: 1274

Answers (2)

Andreas
Andreas

Reputation: 23958

Can't you save the return from the database in session and slice out the part you need?

if(!isset($_SESSION['result'])){
    $_SESSION['result'] = // SQL query
}else{
    $page = $_GET['page'];
    $records = array_slice($_SESSION['result'], $page, 2);
 }
 //Output the records

This is the rough idea.
Use GET to pass the page number and save the results from the query in session, that way you don't call the database each time you switch page.

Upvotes: 0

vrooom
vrooom

Reputation: 51

The problem is that you are not updating your start or offset in your sql query.If offset is correct it would only fetch certain set of records which you require. example of sql query with offset : The SQL query below says "return only 2 records, start on record 1 (OFFSET 0)":

$page=0;
$per_page = 2;
$offset=$page*$per_page; //0

$sql = "SELECT * FROM Orders LIMIT 2 OFFSET 0";

When you click next page the start/offset should be updated 

$page=1;
$per_page = 2;
$offset=$page*$per_page; //2

$sql = "SELECT * FROM Orders LIMIT 2 OFFSET 2";

on clicking 3rd page code would be updated to

$page=2;
$per_page = 2;
$offset=$page*$per_page; //4

$sql = "SELECT * FROM Orders LIMIT 2 OFFSET 4";

and so on...

so sql statement should look like :

$sql = "SELECT * FROM Orders LIMIT $per_page OFFSET $offset";

or

You could also use a shorter syntax to achieve the same result:

$sql = "SELECT * FROM Orders LIMIT $offset, $per_page"

Upvotes: 1

Related Questions