Display Search Result with Pagination using php and mysql

Question

I have a search text box., if i search any terms for ex: PHP, html it will display a results .

now what i need is to display with a pagination pages like 1 2 3 4 5 like that

Im wasting 3days on it i tried myself a lot but no use i dono were i going wrong

no errors are coming no next page is going displays record of first 2 results

need to fetch next results on next page means page2 , 3 like that end of the result says last page. kindly help me on it .. my code is below

<?php
require('../connect.php');
$rec_limit = 2;
if (isset($_GET['search']))
{
$searchtext= $_GET['searchtext'];
}
$sql = "SELECT * from jobpost WHERE jobtitle LIKE '%$searchtext%' LIMIT 2";
mysqli_select_db($con, 'login');
$result= mysqli_query($con, $sql);
if(!$result)
{
echo "error";
}   
if(mysqli_num_rows($result) >0){
echo "Your Search Term : $searchtext ";
echo"<br>";
while($row = $result->fetch_assoc())        
{

    echo "<table>";
    echo "<tr> <td>Job Title:</td> <td>{$row['jobtitle']} </td>      
 </tr>".
        "<tr> <td>Job Description: </td> <td>  
  {$row['jobdescription']}</td> </tr> ".
        "<br>";
    echo "</table>"; 

} 
}
else{
    echo "Your Search Term : $searchtext ";
    echo "<br>";
    echo "no results found";
}
// pagination
$row = mysqli_fetch_array($result, MYSQL_NUM );
$rec_count = $row[0];
if( isset($_GET{'page'} ) )
{
$page = $_GET{'page'} + 1;
$offset = $rec_limit * $page ;
}
else
{
$page = 0;
$offset = 0;
}
$left_rec = $rec_count - ($page * $rec_limit);
$data = mysqli_query($con, "SELECT * FROM jobpost WHERE jobtitle LIKE     
'%$searchtext%' LIMIT $offset, $rec_limit") or die(mysqli_error());
if(mysqli_num_rows($data) >0){
while($row = mysqli_fetch_array($data)) 
{
    /*echo "<table>";
    echo "<tr > <td>Job Title:</td> <td>{$row['jobtitle']} </td>     
</tr>".
        "<tr > <td>Job Description: </td> <td>        
{$row['jobdescription']}</td> </tr> ".
        "<br>";
    echo "</table>"; */
}
}

else{
echo"<a href='search.php?searchtext=$searchtext&search=search'>No images     
available click here for Home </a>";
}
if( $page > 0 )
{
$last = $page - 2;
echo "<br>";
echo "<a href=\"search.php?searchtext=$searchtext&search=search=$last\"      
>Last 2 Records</a> |";
echo "<a href=\"search.php?searchtext=$searchtext&search=search=$page\"               
>Next 2 Records</a>";
}
else if( $page == 0 )
{
echo "<br>";
echo "<a href=\"search.php?searchtext=$searchtext&search=search=$page\"      
>1</a>";
}
else if( $left_rec < $rec_limit )
{
$last = $page - 2;
echo "<br>";
echo "<a href=\"search.php?searchtext=$searchtext&search=search=$last\"   
>2</a>";
}
mysqli_close($con);
?>

kindly help me..

i just want to search any input in a text box and display it in a pagination its my need.

Hitesh Vala Ahir · Accepted Answer · 2015-07-31 13:02:08Z

Use this pagination code

    include("../connect.php");
        $tableName="jobpost";   
        $targetpage = "view_data.php";  
        $limit =10;
        $_GET['searchtext'];


        $query = "SELECT COUNT(*) as num FROM $tableName";
        $total_pages = mysql_fetch_array(mysql_query($query));
        $total_pages = $total_pages['num'];

        $stages = 3;
        $page = mysql_escape_string($_GET['page']);
        if($page){
            $start = ($page - 1) * $limit; 
        }else{
            $start = 0; 
            }   


        // Get page data
        $query1 = "SELECT * FROM $tableName  WHERE jobtitle LIKE     
'%$searchtext%' LIMIT $start, $limit";
        $result = mysql_query($query1);

        // Initial page num setup
        if ($page == 0){$page = 1;}
        $prev = $page - 1;  
        $next = $page + 1;  
        $lastpage = ceil($total_pages/$limit);  
        $LastPagem1 = $lastpage - 1;    
        $paginate = '';
        if($lastpage > 1)
        {       
            $paginate .= "<div class='paginate'>";
            // Previous

            if ($page > 1){
                $paginate.= "<a href='$targetpage?page=$prev'>Previous</a>";
            }else{
                $paginate.= "<span class='disabled'>Previous</span>";   }
            // Pages    
            if ($lastpage < 7 + ($stages * 2))  // Not enough pages to breaking it up
            {   
                for ($counter = 1; $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page){
                        $paginate.= "<span class='current'>$counter</span>";
                    }else{
                        $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}    
                }
            }
            elseif($lastpage > 5 + ($stages * 2))   // Enough pages to hide a few?
            {
                // Beginning only hide later pages
                if($page < 1 + ($stages * 2))   
                {
                    for ($counter = 1; $counter < 4 + ($stages * 2); $counter++)
                    {
                        if ($counter == $page){
                            $paginate.= "<span class='current'>$counter</span>";
                        }else{
                            $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}    
                    }
                    $paginate.= "...";
                    $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
                    $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";
                }
                // Middle hide some front and some back
                elseif($lastpage - ($stages * 2) > $page && $page > ($stages * 2))
                {
                    $paginate.= "<a href='$targetpage?page=1'>1</a>";
                    $paginate.= "<a href='$targetpage?page=2'>2</a>";
                    $paginate.= "...";
                    for ($counter = $page - $stages; $counter <= $page + $stages; $counter++)
                    {
                        if ($counter == $page){
                            $paginate.= "<span class='current'>$counter</span>";
                        }else{
                            $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}    
                    }
                    $paginate.= "...";
                    $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
                    $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";
                }
                // End only hide early pages
                else
                {
                    $paginate.= "<a href='$targetpage?page=1'>1</a>";
                    $paginate.= "<a href='$targetpage?page=2'>2</a>";
                    $paginate.= "...";
                    for ($counter = $lastpage - (2 + ($stages * 2)); $counter <= $lastpage; $counter++)
                    {
                        if ($counter == $page){
                            $paginate.= "<span class='current'>$counter</span>";
                        }else{
                            $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}
                    }
                }       
            }       
                    // Next
            if ($page < $counter - 1){ 
                $paginate.= "<a href='$targetpage?page=$next'>Next</a>";
            }else{
                $paginate.= "<span class='disabled'>Next</span>";
                }
            $paginate.= "</div>";   
    }

if(mysqli_num_rows($result) >0){
while($row = mysqli_fetch_array($result)) 
{
    /*echo "<table>";
    echo "<tr > <td>Job Title:</td> <td>{$row['jobtitle']} </td>     
</tr>".
        "<tr > <td>Job Description: </td> <td>        
{$row['jobdescription']}</td> </tr> ".
        "<br>";
    echo "</table>"; */
}
}

add this where add pagination

echo "<center>".$paginate."</center>";

add style sheet

.paginate {
    font-family:"Times New Roman", Times, serif;
    padding: 3px;
    margin: 3px;
}
.paginate a {
    padding:2px 5px 2px 5px;
    margin:2px;
    border:1px solid #999;
    text-decoration:none;
    color: #666;
}
.paginate a:hover, .paginate a:active {
    border: 1px solid #999;
    color: #0066FF;
}
.paginate span.current {
    margin: 2px;
    padding: 2px 5px 2px 5px;
    border: 1px solid #999;
    font-weight: bold;
    background-color: #999;
    color: #FFF;
}
.paginate span.disabled {
    padding:2px 5px 2px 5px;
    margin:2px;
    border:1px solid #eee;
    color:#DDD;
}

...but don't use any mysql_* functions, as they are all deprecated now.
This method is fine, although does execute 2 queries per page. Returning all records each time, counting the total number, and displaying a sub-set of the returned records can be achieved with 1 query.

MaggsWeb · Accepted Answer · 2015-07-31 13:01:30Z

2

Your problem is LIMIT 2 which will only return 2 rows.

You need to:

Remove the LIMIT
Count the number of records
Decide how many records to display per page
Pass a 'page' variable in the URL
use array_splice() to output a subset of the results depending on page number

No one said it was easy. :)

answered Jul 31, 2015 at 13:01

MaggsWeb

3,0171 gold badge17 silver badges23 bronze badges

Collectives™ on Stack Overflow

Display Search Result with Pagination using php and mysql

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related