pagination is not working after first page while using Where

Question

i am trying to list a data from database and i want to list particular category so i am using WHERE statement. i am getting correct result in first page but when i click on 2nd page its showing nothing

<?php 

 include('dbconfig.inc.php');
        $id=$_GET['id'];

        $query = "SELECT * FROM distdb WHERE BANK_ID = '$id'";       
        $records_per_page=10;
        $newquery = $paginate->paging($query,$records_per_page);
        $paginate->dataview($newquery);
        $paginate->paginglink($query,$records_per_page);        
        ?>

and my Pagination.php

<?php

class paginate
{
    private $db;

    function __construct($dbh)
    {
        $this->db = $dbh;
    }

    public function dataview($query)
    {
        $stmt = $this->db->prepare($query);
        $stmt->execute();

        if($stmt->rowCount()>0)
        {
            while($row=$stmt->fetch(PDO::FETCH_ASSOC))
            {

    $bank = $row['BANK'];
    $ifsc = $row['IFSC'];
    $branch= $row['BRANCH'];
    $micr = $row['MICR_CODE'];
    $address = $row['ADDRESS'];
    $contact = $row['CONTACT'];
    $city = $row['CITY'];
    $district = $row['DISTRICT'];
    $state = $row['STATE'];

    $bankcode=$row['IFSC'];
    $brcode = substr($bankcode, -6);
                ?>
                <tr>
    <td width="21%"><?=$bank?></td> 
       <td width="20%"><?=$branch?></td>
       <td width="20%"><b>IFSC:</b><?=$ifsc?> <br /><b>MICR:</b><?=$micr?><br /><b>Branch Code:</b><?=$brcode?></td>
       <td width="24%"><?=$address?><br /> <b>City :</b><?=$city?> <br /> <b>District :</b><?=$district?> <br /> <b>State:</b> <?=$state?></td> 
       <td width="15%"><?=$contact?></td>
  </tr>


                <?php
            }
        }
        else
        {
            ?>
            <tr>
            <td>Nothing here...</td>
            </tr>
            <?php
        }

    }

    public function paging($query,$records_per_page)
    {
        $starting_position=0;
        if(isset($_GET["page_no"]))
        {
            $starting_position=($_GET["page_no"]-1)*$records_per_page;
        }
        $query2=$query." limit $starting_position,$records_per_page";
        return $query2;
    }

    public function paginglink($query,$records_per_page)
    {

        $self = $_SERVER['PHP_SELF'];

        $stmt = $this->db->prepare($query);
        $stmt->execute();

        $total_no_of_records = $stmt->rowCount();

        if($total_no_of_records > 0)
        {
            ?><tr><td colspan="3"><?php
            $total_no_of_pages=ceil($total_no_of_records/$records_per_page);
            $current_page=1;
            if(isset($_GET["page_no"]))
            {
                $current_page=$_GET["page_no"];
            }
            if($current_page!=1)
            {
                $previous =$current_page-1;
                echo "<a href='".$self."?page_no=1'>First</a>&nbsp;&nbsp;";
                echo "<a href='".$self."?page_no=".$previous."'>Previous</a>&nbsp;&nbsp;";
            }
            for($i=1;$i<=$total_no_of_pages;$i++)
            {
                if($i==$current_page)
                {
                    echo "<strong><a href='".$self."?page_no=".$i."' style='color:red;text-decoration:none'>".$i."</a></strong>&nbsp;&nbsp;";
                }
                else
                {
                    echo "<a href='".$self."?page_no=".$i."'>".$i."</a>&nbsp;&nbsp;";
                }
            }
            if($current_page!=$total_no_of_pages)
            {
                $next=$current_page+1;
                echo "<a href='".$self."?page_no=".$next."'>Next</a>&nbsp;&nbsp;";
                echo "<a href='".$self."?page_no=".$total_no_of_pages."'>Last</a>&nbsp;&nbsp;";
            }
            ?></td></tr><?php
        }
    }
}

?>

its working fine when i use only below statement but i want to filter data with Where

$query = "SELECT * FROM distdb";

**Danger**: You are **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that you need to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Jul 26 '15 at 07:56
ok i will do it, beside do have any answer for my problem above — Asesha George, Jul 26 '15 at 08:14
`when i click on 2nd page its showing nothing` - Define "nothing" - the page is blank?, there are no rows? The string "nothing"? You should look at (and add to the question) the url you are requesting when on page 1, and page not-1 and the _executed sql query_ in both cases. — AD7six, Jul 26 '15 at 09:05

score 0 · Answer 1 · answered Jul 26 '15 at 08:48

In your pagination you have passed just your page number but no bank id. So, in your second and other page will get the $id=$_GET['id'] as NULL. And your query will be look like:

$query = "SELECT * FROM distdb WHERE BANK_ID = ''";

and you will get blank result or some result with blank BANK_ID.

    public function paginglink($query,$records_per_page)
    {

        $self = $_SERVER['PHP_SELF'];
        $id=$_GET['id'];
        $self .= '?id=' . $id;
        $stmt = $this->db->prepare($query);
        $stmt->execute();

        $total_no_of_records = $stmt->rowCount();

        if($total_no_of_records > 0)
        {
            ?><tr><td colspan="3"><?php
            $total_no_of_pages=ceil($total_no_of_records/$records_per_page);
            $current_page=1;
            if(isset($_GET["page_no"]))
            {
                $current_page=$_GET["page_no"];
            }
            if($current_page!=1)
            {
                $previous =$current_page-1;
                echo "<a href='".$self."&page_no=1'>First</a>&nbsp;&nbsp;";
                echo "<a href='".$self."&page_no=".$previous."'>Previous</a>&nbsp;&nbsp;";
            }
            for($i=1;$i<=$total_no_of_pages;$i++)
            {
                if($i==$current_page)
                {
                    echo "<strong><a href='".$self."&page_no=".$i."' style='color:red;text-decoration:none'>".$i."</a></strong>&nbsp;&nbsp;";
                }
                else
                {
                    echo "<a href='".$self."&page_no=".$i."'>".$i."</a>&nbsp;&nbsp;";
                }
            }
            if($current_page!=$total_no_of_pages)
            {
                $next=$current_page+1;
                echo "<a href='".$self."&page_no=".$next."'>Next</a>&nbsp;&nbsp;";
                echo "<a href='".$self."&page_no=".$total_no_of_pages."'>Last</a>&nbsp;&nbsp;";
            }
            ?></td></tr><?php
        }
    }

yes you are correct $id=$_GET['id']; is not passing the value which i required. when i give $id=5 its working fine. — Asesha George, Jul 26 '15 at 11:13

score 0 · Answer 2 · answered Jul 26 '15 at 08:58

0

Can you use OFFSET? For simple $offset = ($_GET['page'] - 1) * $per_page; and add it to end of your query: YOUR-QUERY OFFSET $offset. If $offset is null (or zero) you don't need to add it.

answered Jul 26 '15 at 08:58

ventaquil

2,780
3
23
48

score -2 · Answer 3 · answered Jul 26 '15 at 07:53

-2

replace your query with this one

$query="SELECT * FROM distdb WHERE BANK_ID = $id"

because $id is a variable not a string

answered Jul 26 '15 at 07:53

Steve

134
1
15

put your database content here – Steve Jul 26 '15 at 08:33
Mysql (assumed) [is not sensitive to that](https://gist.github.com/AD7six/0e502e7a147eb0a9a395). Note also that $anything is a variable, whatever the type of content is. – AD7six Jul 26 '15 at 09:07

pagination is not working after first page while using Where

3 Answers3