mysql - How to add pagination in php

Question

I already solve my query issues now the pagination that I implement together with won't work even if the syntax may look like it will work. i already tried every suggestion of every programmers community but its not doing any changes to the result. Kindly help me because this is the last requirement to me to complete the project. Here is the code:

$fromDate = "2015-01-01";
$toDate = "2015-01-30";
$dept = "PACKING";
$user = "root";
$pass = "admin";
$host = "localhost";
$db = "tempdb";

try{
    $cxn = new PDO("mysql:host=$host;dbname=$db",$user,$pass);
    $cxn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $cxn->exec('SET NAMES "utf8"');
}
catch(PDOException $e){
    echo "Unable to connect to server" . $e->getMessage();
    exit();
}
try{
    if(isset($_POST['id'])  &&
    isset($_POST['empid'])  &&
    isset($_POST['employee'])   &&
    isset($_POST['department']))
    {
        $id = $_POST['id'];
        $empid = $_POST['empid'];
        $employee = $_POST['employee'];
        $department = $_POST['department'];
        if($_POST['submit']==0)
        {
            $query = $cxn->prepare("SELECT emptb.*, tempstore.* FROM (SELECT * FROM 
emptb WHERE id < '".$id."' Department = :dept ORDER BY id DESC LIMIT 1)emptb  
inner join tempstore 
on emptb.EmpID = tempstore.EmpID WHERE tempstore.ValidDate BETWEEN
DATE(:fromDate) AND DATE(:toDate)");
            $query->bindParam(':fromDate',$fromDate);
            $query->bindParam(':toDate',$toDate);
            $query->bindParam(':dept',$dept);
            $query->execute();
            $s = $query->fetch();
            extract($s);
            $id = $id;
            $empid = $EmpID;
            $employee = $Lastname . ", " . $Firstname;
            $department = $Department;
        }
        elseif($_POST['submit']==1)
        {
            $query = $cxn->prepare("SELECT emptb.*, tempstore.* FROM (SELECT * FROM 
emptb WHERE id > '".$id."' Department = :dept ORDER BY id ASC LIMIT 1)emptb
inner join tempstore 
on emptb.EmpID = tempstore.EmpID WHERE tempstore.ValidDate BETWEEN
DATE(:fromDate) AND DATE(:toDate)");
            $query->bindParam(':fromDate',$fromDate);
            $query->bindParam(':toDate',$toDate);
            $query->bindParam(':dept',$dept);
        $query->execute();
        $s = $query->fetch();
        extract($s);
        $id = $id;
        $empid = $EmpID;
        $employee = $Lastname . ", " . $Firstname;
        $department = $Department;
    }
}
else
{
    $query = $cxn->prepare("SELECT emptb.*, tempstore.* FROM (SELECT * FROM
emptb WHERE Department = :dept ORDER BY id ASC LIMIT 1)emptb inner join
tempstore 
on emptb.EmpID = tempstore.EmpID WHERE tempstore.ValidDate BETWEEN
DATE(:fromDate) AND DATE(:toDate)");
    $query->bindParam(':fromDate',$fromDate);
    $query->bindParam(':toDate',$toDate);
    $query->bindParam(':dept',$dept);
    $query->execute();
    $s = $query->fetch();
    extract($s);
    $id = $id;
    $empid = $EmpID;
    $employee = $Lastname . ", " . $Firstname;
    $department = $Department;
}   
}
catch(PDOException $e){
    echo "Unable to execute query " . $e->getMessage();
    exit();
}
echo "<form action='' method='post'><pre>
    ID<input type='text' readonly='readonly' value='$empid'>
    Employee<input type='text' readonly='readonly' value='$employee'>
    Department<input type='text' readonly='readonly' value='$department'>
    <button name='submit' value='0'>PREVIOUS</button> <button name='submit' 
value='1'>NEXT</button></pre></form>";

echo "<table><tr><th>Date</th><th>TimeIn</th><th>LunchOut</th>  
<th>LunchIn</th><th>Timeout</th></tr>";
while($r = $query->fetch())
{
extract($r);
if($TimeIn=="00:00:00"){
    $TimeIn="";
}
else{
    $TimeIn= date("g:i",strtotime($TimeIn)) . " " . "AM";
}

if($LunchOut=="00:00:00"){
    $LunchOut="";
}
else{
    $LunchOut= date("g:i",strtotime($LunchOut)) . " " . "nn";
}

if($LunchIn=="00:00:00"){
    $LunchIn="";
}
else{
    $LunchIn=date("g:i",strtotime($LunchIn)) . " " . "PM";
}

if($TimeOut=="00:00:00"){
    $TimeOut="";
}
else{
    $TimeOut= date("g:i",strtotime($TimeOut)) . " " . "PM";
}
echo "<tr>
    <td>$ValidDate</td>

    <td>$TimeIn</td>

    <td>$LunchOut</td>

    <td>$LunchIn</td>

    <td>$TimeOut</td>
</tr>";
}
echo "</table>";

Answer 1

You need to first count the number of rows in your present query:

$numrows = $s->rowCount();

and need to place a vaiable for results per page say $resultsPerPage:

$resultsPerPage=10;

Then the page you are currenty in:

$offset=$_REQUEST['offset'];

Then you need to run the below code :

    $limit=$resultsPerPage;
    $PHP_SELF=$_SERVER['PHP_SELF'];
    if($numrows >= 1) { 
           // determine if offset has been passed to script, or if offset has been tampered with.
            if (empty($offset) || ($offset < 0) || ($offset > $numrows)) {
                $offset=0;
            }
            // Determine if a "PREV" link is necessary - if so, add it to the links array
            if (($offset > 0) && ($offset <= $numrows)) { 
                $prevoffset = $offset - $limit;
                $link_array[] = "<a href="$PHP_SELF?offset=$prevoffset" . $addOn . "">Prev</a> &nbsp; 
";
            }

            // Determine the total number of pages needing links
            $pages=intval($numrows/$limit);
            // $pages variable now contains integer number of pages needed, unless there is a remainder from division
            if ($numrows % $limit) {
                // There is a remainder, so add one page
                $pages++;
            }
        /*
            for ($i=1; $i<=$pages; $i++) { // loop thru
                $newoffset=$limit*($i-1);
                if ((intval($offset/$limit)) == (intval($i-1))) 
                {   $link_array[] = "[$i] &nbsp; 
"; }
                else {  
                    $link_array[] = "<a href="$PHP_SELF?offset=$newoffset" . $addOn . "">$i</a> &nbsp; 
"; 
                }
            }
            */

        $start_page=intval($offset/$limit)-4;
        $end_page=intval($offset/$limit)+5;

        if($start_page<=0){
        $start_page=1;  

        }

        if($start_page<2){
        $end_page=10;  

        }

        if($end_page>$pages){
          $end_page=$pages;
        }



            for ($i=$start_page; $i<=$end_page; $i++) { // loop thru
                $newoffset=$limit*($i-1);

            if ((intval($offset/$limit)) == (intval($i-1))) 
                {   $link_array[] = "[$i] &nbsp; 
"; }
                else {  
                $link_array[] = "<a href="$PHP_SELF?offset=$newoffset" . $addOn . "">$i</a> &nbsp; 
"; 
                }
            }

            // Determine if this is the last page.
            if (!(($offset/$limit)==$pages) && $pages!=1) {
                $newoffset=$offset+$limit;
                // if not last page give NEXT link
                if((($numrows - $offset) > $limit) && ($pages !=1) && ($offset < $numrows)){
                    $link_array[] = "<a href="$PHP_SELF?offset=$newoffset" . $addOn . "">Next</a><br>
";
                }
            }
        }else{
            ; // redirect to error page
        }

if ($resultsPerPage > 0  && count($link_array) > 1)
{   echo "Page: ";
    array_walk($link_array, 'printArray'); 
}