科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>javascript - How to use LIMIT and OFFSET for pagination in MYSQL? - Stack Overflow

    javascript - How to use LIMIT and OFFSET for pagination in MYSQL? - Stack Overflow

    programmeradmin0浏览0评论

    By this code I can able to retrieve the first five records from database.

    But I need to display remaining 5 rows from database after pressing the pagination arrow. I am doing one video sharing website same as like Youtube.

      <?php
include "config.php";


$q = mysql_query("SELECT * FROM register r
                  JOIN videos v ON r.id = v.video_id
                  ORDER BY r.id LIMIT 5") or die (mysql_error());

$headers = $col = "";
$row=mysql_num_rows($q);
$s=null;
echo "<h1> Top Most Videos </h1>";
while ($row = mysql_fetch_array($q)) 
{
if($row['id']!=$s)
{
$s = $row['id'];

echo "<div class='property'>";
echo "<div class='property1' >";
echo "<a href='#'><video src=\"".$row['path']."\" height='100' width='170' style= margin:5px; controls='controls'></video></a>";
echo "</div>";
echo "<div class='property2'>";
echo $row['videoname'];
echo "</div>";
echo "<div class='property3'>";
echo "<br/>";
echo "by:";
echo $row['name'];
echo "<br/>";
echo "</div>";
echo "<div class='property4'>";
echo $row['views'].'&nbsp;&nbsp;views&nbsp;';
echo $row['time'].'&nbsp;&nbsp;year ago';
echo "</div>";
echo "</div>";
}
}
echo "<input type='button' class='btn1'>";
 ?>
 </div>

    Prefer to javascript,php,mysql

    By this code I can able to retrieve the first five records from database.

    But I need to display remaining 5 rows from database after pressing the pagination arrow. I am doing one video sharing website same as like Youtube.

      <?php
include "config.php";


$q = mysql_query("SELECT * FROM register r
                  JOIN videos v ON r.id = v.video_id
                  ORDER BY r.id LIMIT 5") or die (mysql_error());

$headers = $col = "";
$row=mysql_num_rows($q);
$s=null;
echo "<h1> Top Most Videos </h1>";
while ($row = mysql_fetch_array($q)) 
{
if($row['id']!=$s)
{
$s = $row['id'];

echo "<div class='property'>";
echo "<div class='property1' >";
echo "<a href='#'><video src=\"".$row['path']."\" height='100' width='170' style= margin:5px; controls='controls'></video></a>";
echo "</div>";
echo "<div class='property2'>";
echo $row['videoname'];
echo "</div>";
echo "<div class='property3'>";
echo "<br/>";
echo "by:";
echo $row['name'];
echo "<br/>";
echo "</div>";
echo "<div class='property4'>";
echo $row['views'].'&nbsp;&nbsp;views&nbsp;';
echo $row['time'].'&nbsp;&nbsp;year ago';
echo "</div>";
echo "</div>";
}
}
echo "<input type='button' class='btn1'>";
 ?>
 </div>

    Prefer to javascript,php,mysql

    • javascript
    • php
    • mysql
    Share edited Apr 23, 2014 at 9:38 Jurik 3,2641 gold badge33 silver badges52 bronze badges asked Apr 23, 2014 at 9:19 user3422452user3422452 571 gold badge2 silver badges5 bronze badges
    Add a ment  | 

    6 Answers 6

    Reset to default 5

    LIMIT 5 takes the first 5 rows from your results (position 0, 1, 2, 3 and 4 from your result set).

    LIMIT 5,5 takes 5 rows from your result set but starts at position 5. So you will get the 'next' 5 results that are at position 5, 6, 7, 8 and 9.

    Example: If your result without LIMIT is like:

    -----------------
|  ID  |  video |
-------+---------
|  1   |  cat   |
-------+---------
|  2   |  dog   |
-------+---------
|  3   |  bird  |
-------+---------
|  4   |  cow   |
-------+---------
|  5   |  snake |
-------+---------
|  6   |  fish  |
-------+---------
|  7   |  mouse |
-------+---------
|  8   |  shark |
-------+---------
|  9   | seal   |
-------+---------
|  10  | rabbit |
-----------------

    And now you use LIMIT 5 or LIMIT 5, 0

    -----------------
|  ID  |  video |
-------+---------
|  1   |  cat   |
-------+---------
|  2   |  dog   |
-------+---------
|  3   |  bird  |
-------+---------
|  4   |  cow   |
-------+---------
|  5   |  snake |
-------+---------

    And now you use LIMIT 5, 5

    -----------------
|  ID  |  video |
-------+---------
|  6   |  fish  |
-------+---------
|  7   |  mouse |
-------+---------
|  8   |  shark |
-------+---------
|  9   | seal   |
-------+---------
|  10  | rabbit |
-----------------

    You can make use of LIMIT and offset in limit clause

    LIMIT 5,5

    You can use LIMIT and OFFSET concept of DATABASE to provide Pagination in the application. Or even you can use DATATABLE plugin of Jquery to provide Pagination.

    Try this your script

    <?php
$sql = "SELECT * FROM register r JOIN videos v ON r.id = v.video_id ORDER BY r.id desc";
$result = mysql_query($sql);
$no = mysql_num_rows($result);

if (isset($_GET['page'])) { 
    $page = preg_replace('#[^0-9]#i', '', $_GET['page']); 
} else { 
    $page = 1;
} 
$itemsPerPage = 5; 

$lastPage = ceil($no / $itemsPerPage);

if ($page < 1) { 
    $page = 1; 
} else if ($page > $lastPage) { 
    $page = $lastPage; 
} 

$centerPages = "";
$sub1 = $page - 1;
$sub2 = $page - 2;
$add1 = $page + 1;
$add2 = $page + 2;
if ($page == 1) {
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $page . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $add1 . '">' . $add1 . '</a> &nbsp;';
} else if ($page == $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $page . '</span> &nbsp;';
} else if ($page > 2 && $page < ($lastPage - 1)) {
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $sub2 . '">' . $sub2 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $page . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $add1 . '">' . $add1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $add2 . '">' . $add2 . '</a> &nbsp;';
} else if ($page > 1 && $page < $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $page . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $add1 . '">' . $add1 . '</a> &nbsp;';
}

$limit = 'limit ' .($page - 1) * $itemsPerPage .',' .$itemsPerPage; 

$sql2 = mysql_query("SELECT * FROM register r JOIN videos v ON r.id = v.video_id ORDER BY r.id desc $limit"); 
$paginationDisplay = ""; 

if ($lastPage != "1"){

    $paginationDisplay .= 'Page <strong>' . $page . '</strong> of ' . $lastPage. '&nbsp;  &nbsp;  &nbsp; ';

    if ($page != 1) {
        $previous = $page - 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $previous . '" style="text-decoration:none;"> Previous </a> ';
    } 

    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';

    if ($page != $lastPage) {
        $nextPage = $page + 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $nextPage . '" style="text-decoration:none;"> Next</a> ';
    } 
}

echo "<h1> Top Most Videos </h1>";
while ($row = mysql_fetch_array($sql2)) 
{
    if($row['id']!= '')
    {
        $s = $row['id'];

        echo "<div class='property'>";
        echo "<div class='property1' >";
        echo "<a href='#'><video src=".$row['path']." height='100' width='170' style= margin:5px; controls='controls'></video></a>";
        echo "</div>";
        echo "<div class='property2'>";
        echo $row['videoname'];
        echo "</div>";
        echo "<div class='property3'>";
        echo "<br/>";
        echo "by:";
        echo $row['name'];
        echo "<br/>";
        echo "</div>";
        echo "<div class='property4'>";
        echo $row['views'].'&nbsp;&nbsp;views&nbsp;';
        echo $row['time'].'&nbsp;&nbsp;year ago';
        echo "</div>";
        echo "</div>";
    }
}
echo "<input type='button' class='btn1'>";
?>

    you would have to make it dynamic. The limit and offset should not be a static value.

    You have to increase the offset every time you change your page number. 

    $page_number = $_GET['page']; // page number 2 
$limit = 5; // How many rows to display per page
$offset =  $limit * $page_number; // this displays rows from 10 - 15   

 $q = mysql_query("SELECT * FROM register r
                  JOIN videos v ON r.id = v.video_id
                  ORDER BY r.id LIMIT $limit OFFSET $offset") or die (mysql_error());

    Using a link we can send the page number to the query page

    <a href="?page=2">Next</a>

    for next five records you have to change your query. take id of last row and write query with where clause for example id >'id last 5th row'.

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论