CODEWITHSUNDEEP
javascriptphpmysql
user3422452
user3422452

Reputation: 57

How to use LIMIT and OFFSET for pagination in 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

Upvotes: 1

Views: 6126

Answers (6)

SE Romario
SE Romario

Reputation: 1

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>

Upvotes: 0

jay.jivani
jay.jivani

Reputation: 1574

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'>";
?>

Upvotes: 0

Jurik
Jurik

Reputation: 3264

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 |
-----------------

Upvotes: 5

user1999284
user1999284

Reputation: 45

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'.

Upvotes: -2

sshet
sshet

Reputation: 1160

You can make use of LIMIT and offset in limit clause

LIMIT 5,5

Upvotes: 1

Ilesh Patel
Ilesh Patel

Reputation: 2155

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.

Upvotes: 0

Related Questions