fetchColumn variable not being set on 2nd page and breaking pagination

Question

I am using http://www.phpeasystep.com/phptu/29.html for pagination and have used it before on a previous site and it works well.

I am now using this on a different site with the same code layout as on the previous site with different pdo statements.

The pagination is working, and on page 1 the 1,2,3,4,5 links are showing, when I click to page 2 or more, the results show up, but the pagination links disappear.

If I add &page=2 or 3 or 4 into the url, the correct results show up, but not the pagination links.

If I manually set $total_pages to a number eg 22, the pagination links will work on every page, so I think the problem is that $total_pages is not being set after page 1.

The PDO statement I am using is:

    $tbl_name="index";      

$adjacents = 3;


$targetpage = "search.php";     
$limit = 6;                                 
$page = $_GET['page'];
if($page) 
    $start = ($page - 1) * $limit;          
else
    $start = 0;

if ($_GET['search'] == '' && $_GET['cat'] == 'acategory') {

    $cat = $_GET['cat'];

    $search = '';

    $array = $pdo->prepare("SELECT * from `index` where `category_name` = 'acategory' LIMIT         :start, :limit");
    $array->execute(array(':start' => $start, ':limit' => $limit));

    $query = $pdo->prepare("SELECT COUNT(*) from `index` where `category_name` = 'acategory'     LIMIT :start, :limit");
    $query->execute(array(':start' => $start, ':limit' => $limit));

    $total_pages = $query->fetchColumn();
}

    if ($page == 0) $page = 1;                  
$prev = $page - 1;                          
$next = $page + 1;                          
$lastpage = ceil($total_pages/$limit);      
$lpm1 = $lastpage - 1;                      


$pagination = "";
if($lastpage > 1)
{   
    $pagination .= "<div class=\"pagination\">";

    if ($page > 1) 
        $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$prev\">� previous</a>";
    else
        $pagination.= "<span class=\"disabled\">� previous</span>"; 


    if ($lastpage < 7 + ($adjacents * 2))   
    {   
        for ($counter = 1; $counter <= $lastpage; $counter++)
        {
            if ($counter == $page)
                $pagination.= "<span class=\"current\">$counter</span>";
            else
                $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$counter\">$counter</a>";                 
        }
    }
    elseif($lastpage > 5 + ($adjacents * 2))    
    {

        if($page < 1 + ($adjacents * 2))        
        {
            for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class=\"current\">$counter</span>";
                else
                    $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$counter\">$counter</a>";                 
            }
            $pagination.= "...";
            $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$lpm1\">$lpm1</a>";
            $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$lastpage\">$lastpage</a>";       
        }

        elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
        {
            $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=1\">1</a>";
            $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=2\">2</a>";
            $pagination.= "...";
            for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class=\"current\">$counter</span>";
                else
                    $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$counter\">$counter</a>";                 
            }
            $pagination.= "...";
            $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$lpm1\">$lpm1</a>";
            $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$lastpage\">$lastpage</a>";       
        }

        else
        {
            $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=1\">1</a>";
            $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=2\">2</a>";
            $pagination.= "...";
            for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class=\"current\">$counter</span>";
                else
                    $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$counter\">$counter</a>";                 
            }
        }
    }


    if ($page < $counter - 1) 
        $pagination.= "<a href=\"$targetpage?search=$search&cat=$cat&page=$next\">next �</a>";
    else
        $pagination.= "<span class=\"disabled\">next �</span>";
    $pagination.= "</div>\n";       
}

<?=$pagination; echo $total_pages; ?>

There are a few if statements, but I know the correct if statement is being executed by the results that I see.

I have tried to echo the $total_pages variable after page 1 and it does not output anything, and it does not show any errors.

Why would the $total_pages be set on the first and then not for the following pages when all thats changed is the $page and $start variables?

Thanks

Read over [how to squeeze an error message out of PDO](http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo). Setup your `$pdo` object to use `PDO::ERRMODE_EXCEPTION` so it throws useful exceptions when things go wrong - by default, PDO _errors silently_. — Michael Berkowski, Feb 23 '14 at 02:27
Then, have a look at [this question](http://stackoverflow.com/questions/5508993/pdo-limit-and-offset) because I suspect the error you'll see when you get the errors showing is related to your use of named params in `LIMIT`, and passing them as array args to `execute()` instead of calling `bindParam()` and specifying their types as ints. — Michael Berkowski, Feb 23 '14 at 02:29
I should have added this to the code but I have this set already $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); — user3312792, Feb 23 '14 at 02:30
Then make sure you have display_errors turned on. If PDO throws an exception, you should see a fatal error since you have not wrapped it in a try/catch. `ini_set('display_errors', 1);` — Michael Berkowski, Feb 23 '14 at 02:31
ok ill try this, although the link to the other question doesnt seem to work for me — user3312792, Feb 23 '14 at 02:39
This one: http://stackoverflow.com/questions/5508993/pdo-limit-and-offset — Michael Berkowski, Feb 23 '14 at 02:44
I changed to bindParam and specified int for each, added the ini_set('display_errors', 1); and still no error or pagination — user3312792, Feb 23 '14 at 02:45
Post the code which writes out the pagination links. That is likely where your fault lies. — Michael Berkowski, Feb 23 '14 at 02:46
Its unrelated to the problem, but this is one of the nice things about using a framework like Laravel. It has built in pagination so you don't have to worry about stuff like this (pagination is frustrating). — 1andsock, Feb 23 '14 at 05:25

score 1 · Accepted Answer · edited May 23 '17 at 12:03

You have 2 problems.

Turn off emulate prepares if you're running PHP 5 >= 5.1.0 which will sort out your LIMIT problem .See this Answer

$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

The pagenation problem is caused by LIMITING query.

TRY

$query = $pdo->prepare("SELECT COUNT(*) from `index` where `category_name` = 'acategory' ");
$query->execute();
$total_pages = $query->fetchColumn();

fetchColumn variable not being set on 2nd page and breaking pagination

1 Answers1