Split MySQL select data into multiple pages by using LIMIT

Our goal is to split the selected MySQL data into multiple pages and display current page and the data show on specify page.

Introduce to LIMIT clause

Let’s say the limit of data on single page is 10 rows, and current page is page 2. It means I need to show the data begin from 20th to 30th. So what I need to do is to set the offset value and length of data selected.

    $offset = 0;
    $length_of_data = 10;
    $query = "SELECT * FROM mytable LIMIT {$offset} ,{$length_of_data}";

The parameter of LIMIT clause is LIMIT offset, length

Optimize the query

To prevent sql injection, we need to modify the code above into bottom.

    $length_of_data = 10;
    $query = "SELECT * FROM mytable LIMIT :offset ,{$length_of_data}";
    $page = intval(trim($_GET["page"]));
    $offset = $page * $length_of_data;
 
    try{               
        $stmt = $db->prepare($query);
        $stmt->bindParam(':offset',$offset,PDO::PARAM_INT);
        $stmt->execute();
    }catch(PDOException $ex){        
        die ($ex);
    }

Caution : According to stackoverflow, seem like there is a bug when using execute(array(“:offset”=>0)) or bindParam(“:offset”,trim($offset),PDO::PARAM_INT) without intval function. So it is better using what I given on above. I hope it works for your guys.

You can test it by add ?page=0 to your end of the url. It will show you the first 10 rows data without offset.

Display current page and all available pages

It is the last things we need to do. First get the total rows number from table. Then show the current page with special view and other pages with normal view.

 
    $query = "SELECT * FROM mytable";
    try{
        $stmt = $db->prepare($query);
        $stmt->execute();
    }catch(PDOException $ex){
        die ($ex); 
    }

    $rows = $stmt->fetchAll();
    $count = count($rows);
    for($num = 0;$num < $count / $limit_row;$num++){     
        if($page == $num){
            echo '<a href="forum_main.php?page'.$num.'"><span class="page_number current">'.   ($num+1).'</span></a>';
        }else{         
            echo '<a href="forum_main.php?page='.$num.'"><span class="page_number">'.($num+1).'</span></a>';
        }
    }

Here’s we done! Thank you for reading.