用php和mysql分页的奇怪行为

Question

So I have this portfolio page where are loaded items(images) from MySQL database. The HTML is bootstrap and when user click on some category is rearrange all elements and show only from that category.

<div class="filters">
            <ul class="wow lightSpeedIn">
                <li><a href="#" data-filter="*" class="active">All</a></li>
                <li><a href="#" data-filter=".cat_1">Cat_1</a></li>
                <li><a href="#" data-filter=".Cat_2">Cat_2</a></li>
                <li><a href="#" data-filter=".Cat_3">Cat_3</a></li>
                <li><a href="#" data-filter=".Cat_4">Cat_4</a></li>
            </ul>
</div> <!-- *** end filters *** -->

I've made them pulled from database and they work so far but now I want to limit them on the page and put pagination but something strange(at least for me) happening. This is the whole part of the page with items and the pagination.

<!-- *****  Portfolio Filters ***** -->
        <div class="filters">
<div class="filters">
            <ul class="wow lightSpeedIn">
                <li><a href="#" data-filter="*" class="active">All</a></li>
                <li><a href="#" data-filter=".cat_1">Cat_1</a></li>
                <li><a href="#" data-filter=".Cat_2">Cat_2</a></li>
                <li><a href="#" data-filter=".Cat_3">Cat_3</a></li>
                <li><a href="#" data-filter=".Cat_4">Cat_4</a></li>
            </ul>
</div> <!-- *** end filters *** -->
        </div> <!-- *** end filters *** -->
    </div> <!-- *** end container *** -->

    <!-- *****  Portfolio  wrapper ***** -->
    <div class="portfolio-wrapper margin-bottom-medium">
<?php
    require_once 'misc/database.inc.php';
    $pdo = Database::connect();
    error_reporting(E_ALL); 
    ini_set('display_errors', 1);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
    $start_from = ($page-1) * 3;
    $result = $pdo->prepare("SELECT * FROM projects ORDER BY project_id ASC LIMIT $start_from, 8");
    $result->execute();
    for($i=0; $row = $result->fetch(); $i++)
        {
            echo '<div class="portfolio-item '.$row['project_category'].'">';
            echo '<div class="portfolio">';
            echo '<a href="single-project.php?project_id='.$row['project_id'].'" data-lightbox-gallery="portfolio">';
            echo '<img src="'.$row['project_image'].'" alt="Portfolio 1">';
            echo '<div class="portfolio-overlay hvr-rectangle-out">';
            echo '<h2 class="margin-bottom-small">

                        <strong class="white-color bold-text"></strong>
                    </h2>';
            echo '<div class="button">See the project</div>';
            echo '</div><!-- END PORTFOLIO OVERLAY -->
              </a>
           </div>
        </div> <!-- *** end  portfolio-item *** -->
        ';
        }
    Database::disconnect();       
?> 


    </div> <!-- *** end  portfolio-wrapper *** -->
 <?php
        echo '<div id="pagination">';

        $result = $pdo->prepare("SELECT COUNT(project_id) FROM projects");
        $result->execute();
        $row = $result->fetch();
        $total_records = $row[0];
        $total_pages = ceil($total_records / 3);
        //$total_pages=(ceil($total_records/$limit)) + 1;

        for ($i=1; $i<=$total_pages; $i++) {
            echo "<a href='portfolio.php?page=".$i."'";
            if($page==$i)
            {
                echo "id=active";
            }
                echo ">";
                echo "".$i."</a> ";
        };
        echo '</div>';
?>   

The problem is:

1. When I put LIMIT clause in the query it shows 8 items per page but the buttons for categories doesn't work properly. The work only on 1st page and showing only items from categories with ids between 1 and 8 because of the LIMIT. That means that if I have 8 items loaded on first page and click on category 3 and there are only items with ID's > 8 wont show anything. Also all of this buttons are not working on other pages i.e.?page=2..?page=3` etc.

2. Currently I'm testing with 11 items in database and 8 per page. This mean that I must have only 2 pages under them but there are 4 pages.

On page 1 is showing 8 items with ID'd from 1-8.

On second page is showing items from 4-11???

On third page is showing items with ID's from 7 to 11...

and on 4th page is showing items with ID's 10 and 11.

Answer 1

Your code says $start_from = ($page-1) * 3; but you say that you want to show 8 per page. So you need to multiple by 8 instead of 3:

$start_from = ($page-1) * 8;

Again, here:

$total_pages = ceil($total_records / 3);

That should probably be / 8

I would recommend setting up a $perPage variable and using that throughout your code so that you only need to change it once, to avoid issues like this in the future.