How To paste result From PDO query into Paginate?

Question

I have a good result using PDO query retrieve all data from database. But, this only display the result with LIMIT like 10. My questions now is how to paste this result for paginate? I want to set result for 10 perpage and have prev 1 2 3 4 next for all data. below script is good and fast result for me.

    <?php
//load database connection
    $host = "localhost";
    $user = "root";
    $password = "";
    $database_name = "";
    $pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
// Search from MySQL database table
$search=$_POST['search'];
$query = $pdo->prepare("select * from table where colum1 LIKE '%$search%' OR colum2 LIKE '%$search%'  LIMIT 0 , 10");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();
// Display search result
         if (!$query->rowCount() == 0) {
                echo "Search found :<br/>";
                echo "<table style=\"font-family:arial;color:#333333;\">";  
                echo "<tr><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">Title Books</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">Author</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">Price</td></tr>";             
            while ($results = $query->fetch()) {
                echo "<tr><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;\">";            
                echo $results['name'];
                echo "</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;\">";
                echo $results['description'];
                echo "</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;\">";
                echo "$".$results['thumb'];
                echo "</td></tr>";              
            }
                echo "</table>";        
        } else {
            echo 'Nothing found';
        }
?>

You want pagination? – Masivuye Cokile Mar 22 '17 at 11:07 — Masivuye Cokile, Mar 22 '17 at 11:07

score 2 · Accepted Answer · answered Mar 22 '17 at 11:56

I would suggest that you create a class for pagination then include the class.

pagination.php

<?php
class paginate
{

    private $pdo;

    function __construct($pdo)
    {

        $this->db = $pdo;

    }

    public function dataview($query,$Search)
    {

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

        $results = $stmt->fetchall();

        if (count($results) > 0) {?>
           Search found : <br/>
           <table style="font-family:arial;color:#333333;">";  
            <tr><td style="border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;">Title Books</td><td style="border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;">Author</td><td style="border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">Price</td></tr>
            <?php 

            foreach ($results as $row):

?>

                <tr><td style="border-style:solid;border-width:1px;border-color:#98bf21;">
                <?php echo $row['name'];?></td>
                <td style="border-style:solid;border-width:1px;border-color:#98bf21;">;
                <?php echo $row['description'];?>
                </td>
                <td style="border-style:solid;border-width:1px;border-color:#98bf21;">
                <?php echo $row['thumb'];?>
                </td></tr>
<?php
            endforeach;
            echo "</table>";
        } else {

            echo "<p> Nothing found </p>";
        }


    }


    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) {
?>
<ul class="pagination"><?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 "<li><a  href='" . $self . "?page_no=1' >First</a></li>";
                echo "<li><a  href='" . $self . "?page_no=" . $previous . "'>Previous</a></li>";


            }
            for ($i = 1; $i <= $total_no_of_pages; $i++) {
                if ($i == $current_page) {

                    echo "<li class='active'><a  href='" . $self . "?page_no=" . $i . "' >" . $i . "</a></li>";


                } else {

                    echo "<li><a   href='" . $self . "?page_no=" . $i . "'>" . $i . "</a></li>";
                }
            }
            if ($current_page != $total_no_of_pages) {
                $next = $current_page + 1;

                echo "<li><a   href='" . $self . "?page_no=" . $next . "'>Next</a></li>";
                echo "<li><a href='" . $self . "?page_no=" . $total_no_of_pages . "'>Last</a></li>";
            }
?></ul>
<?php
        }
    }


}
?>

page.php

<?php
    //your connection
    $host = "localhost";
    $user = "root";
    $password = "";
    $database_name = "";
    $pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    )); 
    include_once'pagination.php';
    $paginate = new paginate($pdo);


$search=$_POST['search'];
$Search = "%$search%";

$query = "SELECT * from table where colum1 LIKE ? OR colum2 LIKE ? ";       
$records_per_page=10;
$newquery = $paginate->paging($query,$records_per_page,$Search);
$paginate->dataview($newquery,$Search);
$paginate->paginglink($query,$records_per_page); 
?>

This should work. Suggestions/comments are welcome.

This is one of the projects I have used the above code to do a pagination :

or use jquery dataTables which will also work perfect

all u need is to download https://datatables.net/

With Datatables, simple download it then add required scripts and css, then on your table add an ID, then initialize datatable

 $('#TableID').dataTable();

You might have to do the proper table markup,

<table>
   <thead>
       <tr>
         <th>Title</th>
       </tr>
   </thead>
   <tbody>
   <tr>
      <td>Text</td>
   </tr>
  </tbody>

Thanks a lot @Masivuye Cokile this help me and very useful.. result show 10 item is so good..but the page number display all like 1 2 3 4 5 to 300 next last .. so how to make just 1 to 10 or standard paginate number? realy thanks full in advance ;q — netboy78, Mar 22 '17 at 19:19
for result less than 100 iten this display very good but if result more than hundred the output display all of page number and make browser slowly load also very very width... and hight.. this pagination need to limit to? thanks to help.. — netboy78, Mar 22 '17 at 23:08
this i shoot what i am face [link]https://1.bp.blogspot.com/-QjmiUB1cRBY/WNMKZlHI91I/AAAAAAAALCs/KLaMBzq29UcIzHgXb-xpeMeAwR6qvBRKwCLcB/s1600/Screenshot%2B-%2B23_03_2017%2B%252C%2B7_25_36%2BAM.png — netboy78, Mar 22 '17 at 23:38
@netboy78 check here : http://stackoverflow.com/questions/8361808/limit-pagination-page-number — Masivuye Cokile, Mar 23 '17 at 09:03
after i show error to display i face Notice: Undefined index: search in /var/www/test/pagination.php on line 111 how do you think sir? likely the statement cant get the $search from form page might be? — netboy78, Mar 23 '17 at 14:30
i have view your suggestion question and answer above but i seen their is not in pdo statement sir.. ;q — netboy78, Mar 23 '17 at 14:32
[code] $total_no_of_records = $stmt->rowCount(); if ($total_no_of_records > 0) { ?>
First"; echo " — netboy78, Mar 23 '17 at 14:41

How To paste result From PDO query into Paginate?

1 Answers1