CODEWITHSUNDEEP
phpmysqlpaginationlimit
AAA
AAA

Reputation: 3168

Basic pagination

I am not a well-versed programmer so i don't want to use a complicated pagination. Is there an alternative to using a pagination like in my queries can i limit them to say 20 results and then if their are more than 20 results i can show a "next" button and then that button will further load the next results??

I only want "next" to show if there are 20> results? Any help will be appreciated. Thanks.

Code: 

  $query="SELECT * FROM actresses where actress_id = '$actressid' and production_full_name LIKE '%$q%'";  
       $result=mysql_query($query);

        $num=mysql_numrows($result);

        mysql_close();

          echo "";

       $i=0;
         while ($i < $num) {

         $1=mysql_result($result,$i,"production_full_name");
           $2=mysql_result($result,$i,"production_id");
     $3=mysql_result($result,$i,"actress");


          echo "<br><div id=linkcontain><a id=slink   href=$data/actress.php?id=$2>$3</a><div id=production>$1</div></div>";

            echo "";

            $i++;
            }

Upvotes: 1

Views: 3866

Answers (7)

A.A Noman
A.A Noman

Reputation: 5270

This is more easy way to create pagination of a data

<?php
    require_once 'db_connect.php';

    $limit = 20;  
    if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };  
    $start_from = ($page-1) * $limit;  

    $sql = "SELECT * FROM employee ORDER BY id ASC LIMIT $start_from, $limit";  
    $rs_result = mysqli_query($con, $sql);  
?>


<table class="table table-bordered">  
        <thead>  
            <tr>  
                <th>Name</th>  
                <th>Salary</th>
                <th>Age</th>  
            </tr>  
        </thead>  
            <tbody>  
                <?php  
                    while ($row = mysqli_fetch_assoc($rs_result)) {?>  
                        <tr>  
                            <td><?php echo $row["employee_name"]; ?></td>  
                            <td><?php echo $row["employee_salary"]; ?></td>  
                            <td><?php echo $row["employee_age"]; ?></td>  
                        </tr>  
                        <?php  
                    };  
                ?>  
            </tbody>  
</table>



<?php  
        $sql = "SELECT COUNT(id) FROM employee";  
        $rs_result = mysqli_query($con, $sql);  
        $row = mysqli_fetch_row($rs_result);  
        $total_records = $row[0];  
        $total_pages = ceil($total_records / $limit);  
        $pagLink = "<nav><ul class='pagination'>";  
        for ($i=1; $i<=$total_pages; $i++){  
            $pagLink .= "<li><a href='index.php?page=".$i."'>".$i."</a></li>";  
        };  
        echo $pagLink . "</ul></nav>";  
?>


<script type="text/javascript">
    $(document).ready(function(){
        $('.pagination').pagination({
            items: <?php echo $total_records;?>,
            itemsOnPage: <?php echo $limit;?>,
            cssStyle: 'light-theme',
            currentPage : <?php echo $page;?>,
            hrefTextPrefix : 'index.php?page='
        });
    });
</script>

Upvotes: 0

rcapote
rcapote

Reputation: 1044

What you can do is fetch a COUNT(*) of how many rows are in your set of data. Then decide how many items you want per page. $numberOfRows / $itemsPerPage gives you the number of pages. In MySQL, to limit results you use LIMIT X, Y X is the beginning of the range, and Y is the number of items you want to fetch. So to fetch all the items on a page, your query would be LIMIT ($currentPage * $itemsPerPage), $itemsPerPage Then it's up to you, depending on how you want to format your pagination, to write the view logic. Just go from back to front: if the current page is 1, then the previous button is disabled. If the page is not 1, then the current page number is the number of pages from the beginning. If the total number of pages is bigger than the current page, then count from the current page, until the last page (unless you want to cut off the page count at some point)

I use the following method to assist in creating a list of pages, no matter what direction. I could plug in 5 for the current page, and 0 as the goal page, and it would return a list of 5 pages for me to iterate through, in order.

/*
 * Generates a list of pages based on the current page to the goal page number
 */
function generatePageList($currentPage, $goal) {
    $span = $goal - $currentPage;
    $step = abs($span) / $span;
    $list = array();

    for($x = $currentPage; $x !== $goal;) {
        // We want to add the step at the beginning of the loop, instead of 
        // at the end
        // Fixes bug when there are only two pages
        $x += $step;
        $list[] = $x;
    }

    return $list;

}

Upvotes: 1

Patrick
Patrick

Reputation: 1827

If your query is set up as

"SELECT SQL_CALC_FOUND_ROWS * FROM actresses where actress_id = '$actressid' and production_full_name LIKE '%$q%' LIMIT ".($page*$lpage).",".$lpage

... the next statement can be SELECT FOUND_ROWS() to get the actual number of results. Don't forget to put mysql_real_escape_string() on those variables.

NEVER use SELECT * with mysql_num_rows; if it's a big table, it will waste a LOT of time.

Upvotes: 1

Filgera
Filgera

Reputation: 343

You can find many examples searching on google. http://www.php-mysql-tutorial.com/wikis/php-tutorial/paging-using-php.aspx for example.

You only have to edit $rowsPerPage for amount of results, and if you want further edit on what to get from the DB, the $query.

Includes are not necessary, though.

edit: note the implication of using a $_GET that will have influence on the DB query. You have to be careful to not allow dangerous values in it (mysql injection). In the example of the webpage, it uses ceil, so I believe that it will output 0 for a non numeric value, which is safer.

Upvotes: 2

Parris Varney
Parris Varney

Reputation: 11478

You could add a limit to your query.

$lower_limit = $results_per_page * $page_number;
$upper_limit = $lower_limit + $results_per_page

..and production_full_name LIKE '%$q%' LIMIT $lower_limit, $upper_limit

Then make a conditional "next page" link

if ($upper_limit > 20) echo '<a href="{page url}?page_number='.($page_number+1).'">Next</a>;

Upvotes: 3

CappY
CappY

Reputation: 1580

Something like that:

$data = mysql_query("SELECT * FROM `table`");
$total_data = mysql_num_rows($data);

$step = 30;
$from = $_GET['p'];

$data = mysql_query("SELECT * FROM `table` LIMIT '.$from.','.$step.'"

And creating links:

$p=1;
for ($j = 0 ; $j <= $total_data+$step; $j+=$step) 
{ 
    echo ' <a href="page.php?p='.$j.'">'.$p.'</a> '; 
    $p++;

} ?>

Not tested.

Upvotes: 1

Akhilesh Sharma
Akhilesh Sharma

Reputation: 1628

You can use the PEAR packages. They are much easier to use similar to the frameworks. But before you use them you need to check whether the PEAR works on your server or not. If you are using it locally then there are steps to install the PEAR packages on your local machine. For more instructions to install PEAR. Click here to view the installation steps.

Once you have installed the PEAR package please follow the instruction to configure the PAGER class.http://www.codediesel.com/php/simple-pagination-in-php/

J

Upvotes: 1

Related Questions