Struggling with PHP/MySQL Pagination

Question

I am struggling to understand the best way to introduce pagination using php and mysql. I have the below code where i have tried to display the first 5 buttons, example 1-5, but i would like a "next button" to then show 5-10, then 10-15 and so on. Any help much appreciated.

$q=mysql_query("SELECT * FROM posts WHERE $ctq approved=1 Limit 15");
$pages = ceil(mysql_num_rows($q)/$item_per_page)+1; 

//create pagination
if($pages > 0)
{
    for($i = 1; $i<$pages; $i++)
    {
        $pagination .= '<a href="#" id="'.$i.'-page" class="inactive paginate_click">'.$i.'</a>';
    }
}

$ctajax='';
if(isset($_GET['cat_id']) && !empty($_GET['cat_id'])){
   $ctajax.=",'cat_id':".$_GET['cat_id'];
}
?>



<script type="text/javascript">
jQuery(document).ready(function($) {
    $("#results").load("fetch_pages.php", {'page':0<?php echo $ctajax ?>}, function() {
        $("#1-page").removeClass('inactive').addClass('current');
    });  

    //initial page number to load
    $(".paginate_click").click(function (e) {

        $("#results").prepend('<div class="loading-indication"><img src="ajax-loader.gif" /> Loading...</div>');

        var clicked_id = $(this).attr("id").split("-"); //ID of clicked element, split() to get page number.
        var page_num = parseInt(clicked_id[0]); //clicked_id[0] holds the page number we need 
        $('.paginate_click').addClass('inactive');
        $('.paginate_click').removeClass('current'); //remove any active class

        //post page number and load returned data into result element
        //notice (page_num-1), subtract 1 to get actual starting point
        $("#results").load("fetch_pages.php", {'page':(page_num-1)}, function(){

        });

        $(this).addClass('current'); //add active class to currently clicked element (style purpose)

        return false; //prevent going to herf link
    }); 
});
</script> 

    <div id="results"></div>

    <nav role="navigation" id="nav-below" class="site-navigation paging-navigation clearfix">
        <div class="pagination">
            <?php echo $pagination; ?>
            <a href="#" id="<?php echo $iadd;?>-page" class="inactive paginate_click">Next</a>
        </div>                                              
    </nav>
    <?php } ?>
    <!-- #nav-below --> 
</section>
<!-- /#content -->

Please read about [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection). Instead of building queries with string concatenation, use [**prepared statements**](https://secure.php.net/manual/en/pdo.prepare.php) with [**bound parameters**](https://secure.php.net/manual/en/pdostatement.bindparam.php). See [**this page**](https://phptherightway.com/#databases) and [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for some good examples. — Alex Howansky, Dec 11 '18 at 22:13
Also note that the `mysql_*` functions have been deprecated since v5.5 (Jun 2013) and removed since v7.0 (Dec 2015). Instead use [**mysqli**](https://secure.php.net/manual/en/book.mysqli.php) or [**PDO**](https://secure.php.net/manual/en/book.pdo.php). — Alex Howansky, Dec 11 '18 at 22:14
Thank you for your responses, unfortunately I am a beginner so am looking for a basic explanation to show how i can achieve my goal with the existing out dated code "not ideal i know". — Josh, Dec 11 '18 at 22:22

score 2 · Answer 1 · edited Dec 11 '18 at 22:55

2

limit 15 just gives you the first 15 rows, if you want the next 15, you would use LIMIT 15 OFFSET 15.

You would also want an "order by" on your sql to avoid random results.

if the result set is large and you are not using an index, this method can cause substantial performance issues. There are other ways to deal with that and could be a more involved answer.

edited Dec 11 '18 at 22:55

node_modules

4,790
6
21
37

answered Dec 11 '18 at 22:44

Piyper

121
7

Struggling with PHP/MySQL Pagination

1 Answers1