CODEWITHSUNDEEP
phpsqlpagination
Isuru
Isuru

Reputation: 3958

Pagination with PHP

I am trying to run the pagination code I created, but I can't figure out how to get all items from table separated to pages(pagination).

Following is the code.

public function getCatItemsByPage($cat, $page) {
    $query = "SELECT 
                id
              FROM 
                categories
              WHERE
                name = '$cat'";


    $result = mysql_query($query) or die(mysql_error());

    $id = mysql_result($result, 0, "id");


 if($page == 1){
    $limit_start = 0;
    $limit_stop = 52;
 }else if($page == 2){
    $limit_start = ($page - 1) * 52;
    $limit_stop = $limit_start + 52; 
 }else{
    $limit_start = $page * 52;
    $limit_stop = $limit_start + 52; 
 }

    $output_videos_query = "SELECT 
               *
              FROM 
                videos
              WHERE
                cate_id=$id
              ORDER BY
                created_at
              DESC LIMIT 
                $limit_start, $limit_stop";

    $output_videos_result = mysql_query($output_videos_query) or die(mysql_error());

    return $output_videos_result;

}

I pass category($cat) and page number($page). And the problem is how to calculate $limit_start and $limit_stop by page. I spent last hour trying to figure out how to get videos, but some pages don't get some videos or some pages get lots of videos.

As I am new to PHP any idea on this, really appreciated.

Upvotes: 0

Views: 848

Answers (2)

Glitch Desire
Glitch Desire

Reputation: 15023

You can replace this:

if($page == 1){
    $limit_start = 0;
    $limit_stop = 52;
}else if($page == 2){
    $limit_start = ($page - 1) * 52;
    $limit_stop = $limit_start + 52; 
}else{
    $limit_start = $page * 52;
    $limit_stop = $limit_start + 52; 
}

With a much simpler:

$limit_start = ($page - 1) * 52;
$limit_stop = 52;

Upvotes: 1

bwoebi
bwoebi

Reputation: 23777

if($page == 1){
    $limit_start = 0;
    $limit_stop = 52;
}else if($page == 2){
    $limit_start = ($page - 1) * 52;
    $limit_stop = $limit_start + 52; 
}else{
    $limit_start = $page * 52;
    $limit_stop = $limit_start + 52; 
}

What should this condition do? Simply do every time:

$limit_start = ($page - 1) * 52; // 0 when 1, 52 when 2, 104 when 3 etc.
$limit_stop = 52;

And it should work.

$limit_stop has to be a constant value as MySQL's LIMIT expects a length, not a position.

Upvotes: 2

Related Questions