Edmhar Edmhar - 7 months ago 33
PHP Question

Insert Pagination in Search database. PHP MySQL

I'm a beginner in PHP MySQL, I have both working and separate Search Employee Engine and View All Employee with pagination but right now I want to merge this both file.

I want to insert the pagination function to search my engine but I don't know how to do it (although it's working in my view all employee).

Here is my View All Employee file:

<?php
session_start();
include_once 'dbconnect.php';

if(!isset($_SESSION['user']))
{ header("Location: index.php");}
$res=mysql_query("SELECT * FROM accounts WHERE user_id=".$_SESSION['user']);
$userRow=mysql_fetch_array($res);

$sql = "SELECT * FROM accounts ORDER BY agentLname ASC";

if(isset($_SESSION['usertype']) && $_SESSION['usertype'] == 'Admin') {

?>

<html>
<body>

<div class="row mt">
<div class="col-md-12">
<div class="content-panel">

<?php
if($result = mysql_query($sql)){
if (mysql_num_rows($result) > 0 ) {
?>


<table class="table table-hover">
<h3>&nbsp;&nbsp;<i class="fa fa-angle-right"></i>Employees<h5 style="float:right;"><a href="addnew.php" style="color:#ffffff;"><button type="button" class="btn btn-theme02" ><i class="fa fa-check"></i>Add New Agent</button></h5></h3></a>

<hr>
<thead>
<tr>
<th>Agent Code</th>
<th>Full Name</th>
<th>Address</th>
<th>Phone No.</th>
<th>Gender</th>
<th>Account Type</th>
<th>User Type</th>
<th>IP Address</th>
<th></th>
<th></th>

<?php
} while($row = mysql_fetch_array($result)) { ?>

</tr>
</thead>
<tbody>
<tr>
<td><?php echo $row['agentCode']; ?></a></td>
<td><?php echo $row['agentLname'].', '.$row['agentFname'].' '.$row['aSuffixName'].' '.$row['agentMname']; ?></a></td>
<td><?php echo $row['agentAddress']; ?></td>
<td><?php echo $row['agentContact']; ?></td>
<td><?php echo $row['agentGender']; ?></td>
<td><?php echo $row['user_type']; ?></td>
<td><?php echo $row['location_type']; ?></td>
<td><?php echo $row['ip_add']; ?></td>

<td>
<a href="edit.php?id=<?php echo $row['user_id']; ?>" ><button class="btn btn-primary btn-xs"><i class="fa fa-pencil"></i></button></a>
<a href="delete.php?id=<?php echo $row['user_id']; ?>" onclick="return confirm('Are you sure?');"><button class="btn btn-danger btn-xs"><i class="fa fa-trash-o "></i></button>
</td></tr>

<?php
}
}
?>


</tbody>
</table>
</div><!-- /content-panel -->
</div><!-- /col-md-12 -->
</div><!-- /row -->

</body>
</html>

<?php
} else {
header("Location: index.php");
exit;
} // end if user is not admin
?>


Here is my Search Employee file

<?php
session_start();
include_once 'dbconnect.php';


if(!isset($_SESSION['user']))
{ header("Location: index.php"); }
$res=mysql_query("SELECT * FROM accounts WHERE user_id=".$_SESSION['user']);
$userRow=mysql_fetch_array($res);

if(isset($_SESSION['usertype']) && $_SESSION['usertype'] == 'Admin') {

?>

<div class="row mt">
<div class="col-md-12">
<div class="form-panel">
<h3>&nbsp;&nbsp;<i class="fa fa-angle-right"></i>Search Employee</h3>
<p style="font-size:13pt;color: black;padding-left: 1em;">You may search either by first or last name</p>
<!-- INLINE FORM ELELEMNTS -->

<form class="form-inline" role="form" method="post" id="searchform">
<div class="form-group">

<input type="text" class="form-control" name="name" id="name" placeholder="Input First or Last Name" style="width: 300px;">
<h5 style="float:right;"><button type="submit" class="btn btn-theme02" name="submit" ><i class="fa fa-check">Search Employee</i></button></h5>

</div>



<?php

echo "<table class=\"table table-hover\">";
echo " <hr><thead><tr>";
echo "<th>Agent Code</th>";
echo "<th>Full Name</th>";
echo "<th>Address</th>";
echo "<th>Gender</th>";
echo "<th>Account Type</th>";
echo "<th>User Type</th>";
echo "<th>IP Address</th>";
echo "<th></th>";
echo "<th></th>";
echo " </tr></thead>";

if (isset($_POST['submit'])) {

if(preg_match("/[A-Z | a-z]+/", $_POST['name'])){


$name=$_POST['name'];

$sql1="SELECT * FROM accounts WHERE agentFname LIKE '%" . $name . "%' OR agentLname LIKE '%" . $name ."%'";
$result=mysql_query($sql1);

while($row=mysql_fetch_array($result)){

$aCode = $row['agentCode'];
$aFName = $row['agentFname'];
$aMName = $row['agentMname'];
$aLName = $row['agentLname'];
$aAddress = $row['agentAddress'];
$aGender = $row['agentGender'];
$a_type = $row['user_type'];
$u_type = $row['location_type'];
$ipadd = $row['ip_add'];
$ID = $row['user_id'];

echo "<tbody><tr>";
echo "<td>".$aCode."</td>";
echo "<td>".$aLName." ".$aFName." ".$aMName. "</td>";
echo "<td>".$aAddress."</td>";
echo "<td>".$aGender."</td>";
echo "<td>".$a_type."</td>";
echo "<td>".$u_type."</td>";
echo "<td>".$ipadd."</td>";
echo "</form>";
?>

<td>
<a href="edit.php?id=<?php echo $row['user_id']; ?>" ><button class="btn btn-primary btn-xs"><i class="fa fa-pencil"></i></button></a>
<a href="delete.php?id=<?php echo $row['user_id']; ?>" onclick="return confirm('Are you sure?');"><button class="btn btn-danger btn-xs"><i class="fa fa-trash-o "></i></button>
</td>

<?php
} //while

}

}//submit

?>

</div><!-- /form-panel -->

</div>
</div>



<?php
} else {
header("Location: index.php");
exit;
} // end if user is not admin
?>


Here is the function.php where the pagination script allocated

<?php

function pagination($query, $per_page = 3,$page = 1, $url = '?'){
$query = "SELECT COUNT(*) as `num` FROM {$query}";
$row = mysql_fetch_array(mysql_query($query));
$total = $row['num'];
$adjacents = "2";

$page = ($page == 0 ? 1 : $page);
$start = ($page - 1) * $per_page;

$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($total/$per_page);
$lpm1 = $lastpage - 1;

$pagination = "";
if($lastpage > 1)
{
$pagination .= "<ul class='pagination'>";
$pagination .= "<li class='details'>Page $page of $lastpage</li>";
if ($lastpage < 7 + ($adjacents * 2))
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a> </li>";
else
$pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";
}
}
elseif($lastpage > 5 + ($adjacents * 2))
{
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";
}
$pagination.= "<li class='dot'>...</li>";
$pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
$pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";
}
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<li><a href='{$url}page=1'>1</a></li>";
$pagination.= "<li><a href='{$url}page=2'>2</a></li>";
$pagination.= "<li class='dot'>...</li>";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";
}
$pagination.= "<li class='dot'>..</li>";
$pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
$pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";
}
else
{
$pagination.= "<li><a href='{$url}page=1'>1</a></li>";
$pagination.= "<li><a href='{$url}page=2'>2</a></li>";
$pagination.= "<li class='dot'>..</li>";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";
}
}
}

if ($page < $counter - 1){
$pagination.= "<li><a href='{$url}page=$next'>Next</a></li>";
$pagination.= "<li><a href='{$url}page=$lastpage'>Last</a></li>";
}else{
$pagination.= "<li><a class='current'>Next</a></li>";
$pagination.= "<li><a class='current'>Last</a></li>";
}
$pagination.= "</ul>\n";
}


return $pagination;
}
?>

Answer

Please try by this way

<?php

function pagination($num, $per_page = 3,$page = 1, $url = '?'){        

    $total = $num;
    $adjacents = "2"; 

    $page = ($page == 0 ? 1 : $page);  
    $start = ($page - 1) * $per_page;                               

    $prev = $page - 1;                          
    $next = $page + 1;
    $lastpage = ceil($total/$per_page);
    $lpm1 = $lastpage - 1;

    $pagination = "";
    if($lastpage > 1)
    {   
        $pagination .= "<ul class='pagination'>";
                $pagination .= "<li class='details'>Page $page of $lastpage</li>";
        if ($lastpage < 7 + ($adjacents * 2))
        {   
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<li><a class='current'>$counter</a> </li>";
                else
                    $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
            }
        }
        elseif($lastpage > 5 + ($adjacents * 2))
        {
            if($page < 1 + ($adjacents * 2))        
            {
                for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<li><a class='current'>$counter</a></li>";
                    else
                        $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                }
                $pagination.= "<li class='dot'>...</li>";
                $pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
                $pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";      
            }
            elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
            {
                $pagination.= "<li><a href='{$url}page=1'>1</a></li>";
                $pagination.= "<li><a href='{$url}page=2'>2</a></li>";
                $pagination.= "<li class='dot'>...</li>";
                for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<li><a class='current'>$counter</a></li>";
                    else
                        $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                }
                $pagination.= "<li class='dot'>..</li>";
                $pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
                $pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";      
            }
            else
            {
                $pagination.= "<li><a href='{$url}page=1'>1</a></li>";
                $pagination.= "<li><a href='{$url}page=2'>2</a></li>";
                $pagination.= "<li class='dot'>..</li>";
                for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<li><a class='current'>$counter</a></li>";
                    else
                        $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                }
            }
        }

        if ($page < $counter - 1){ 
            $pagination.= "<li><a href='{$url}page=$next'>Next</a></li>";
            $pagination.= "<li><a href='{$url}page=$lastpage'>Last</a></li>";
        }else{
            $pagination.= "<li><a class='current'>Next</a></li>";
            $pagination.= "<li><a class='current'>Last</a></li>";
        }
        $pagination.= "</ul>\n";        
    }


    return $pagination;
} 
?>

add this option below-

<?php
define("DB_HOST", "yourhost");
define("DB_USER", "DBuser");
define("DB_PASSWORD", "passwrod");
define("DB_DATABASE", "DB name");
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD,DB_DATABASE );
?>

Or you can use here your own database connection file

//set pagination

$query = "SELECT * FROM accounts WHERE  agentFname LIKE '%" . $name . "%' OR agentLname LIKE '%" . $name  ."%'";
$num = mysqli_num_rows($conn->query($query));

$per_page = 3;
$page = 1;
$url = 'pagination.php';

if(isset($_REQUEST['start'])){
    $start = $_REQUEST['start'];
}else{

    $start = '0';
}



$sql = "SELECT * FROM accounts WHERE  agentFname LIKE '%" . $name . "%' OR agentLname LIKE '%" . $name  ."%' LIMIT $start, $per_page ";
$result = $conn->query($sql);

while($row=mysql_fetch_array($result)){
    //all info you want to print
    $aCode = $row['agentCode'];
      $aFName  = $row['agentFname'];
      .
      .
} 

echo  pagination($num, $per_page = 3,$page = 1, $url = '?');