CODEWITHSUNDEEP
sqlpostgresql
Ramprasad
Ramprasad

Reputation: 8071

How to query first 10 rows and next time query other 10 rows from table

I have more than 500 rows with in my Database Table with particular date.

To query the rows with particular date.

select * from msgtable where cdate='18/07/2012'

This returns 500 rows.

How to query these 500 rows by 10 rows step by step. Query First 10 Rows and show in browser,then query next 10 rows and show in browser?

Upvotes: 30

Views: 145906

Answers (7)

Raj
Raj

Reputation: 21

<html>
<head>
    <title>Pagination</title>
</head>
<body>
<?php 

    $conn = mysqli_connect('localhost','root','','northwind');
    $data_per_page = 10;
    $select = "SELECT * FROM `customers`";  
    $select_run = mysqli_query($conn, $select);
    $records = mysqli_num_rows($select_run);
    // while ($result = mysqli_fetch_array($select_run)) {      
    //     echo $result['CompanyName'] . '<br>';        
    // }
    // $records;
    echo "<br>";
    $no_of_page = ceil($records / $data_per_page);
    if(!isset($_GET['page'])){
        $page = 1;
    }else{
        $page = $_GET['page'];
    }
    $page_limit_data = ($page - 1) * 10;
    $select = "SELECT * FROM customers LIMIT " . $page_limit_data . ',' . $data_per_page ;
    $select_run = mysqli_query($conn, $select);
    while ($row_select = mysqli_fetch_array($select_run)){
        echo $row_select['CompanyName'] . '<br>' ;
    }
    for($page=1; $page<= $no_of_page; $page++){ 
        echo "<a href='pagination.php?page=$page'> $page" . ', ';   
    }
?>
<br>
<h1> Testing Limit Functions Here  </h1>
<?php 
$limit = "SELECT CompanyName From customers LIMIT 10 OFFSET 5";
$limit_run = mysqli_query($conn , $limit);
while($limit_result = mysqli_fetch_array($limit_run)){
    echo $limit_result['CompanyName'] . '<br>';
}
?>
</body>
</html>

Upvotes: 2

Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167172

Just use the LIMIT clause.

SELECT * FROM `msgtable` WHERE `cdate`='18/07/2012' LIMIT 10

And from the next call you can do this way:

SELECT * FROM `msgtable` WHERE `cdate`='18/07/2012' LIMIT 10 OFFSET 10

More information on OFFSET and LIMIT on LIMIT and OFFSET.

Upvotes: 68

user5271805
user5271805

Reputation:

Ok. So I think you just need to implement Pagination.

$perPage = 10;

$pageNo = $_GET['page'];

Now find total rows in database.

$totalRows = Get By applying sql query;

$pages = ceil($totalRows/$perPage);    

$offset = ($pageNo - 1) * $perPage + 1

$sql = "SELECT * FROM msgtable WHERE cdate='18/07/2012' LIMIT ".$offset." ,".$perPage

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

LIMIT limit OFFSET offset will work.

But you need a stable ORDER BY clause, or the values may be ordered differently for the next call (after any write on the table for instance).

SELECT *
FROM   msgtable
WHERE  cdate = '2012-07-18'
ORDER  BY msgtable_id  -- or whatever is stable 
LIMIT  10
OFFSET 50;  -- to skip to page 6

Use standard-conforming date style (ISO 8601 in my example), which works irregardless of your locale settings.

Paging will still shift if involved rows are inserted or deleted or changed in relevant columns. It has to.

To avoid that shift or for better performance with big tables use smarter paging strategies:

Upvotes: 13

Ankit Kumar
Ankit Kumar

Reputation: 3723

for first 10 rows...

SELECT * FROM msgtable WHERE cdate='18/07/2012' LIMIT 0,10

for next 10 rows

SELECT * FROM msgtable WHERE cdate='18/07/2012' LIMIT 10,10

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

SET @rownum = 0; 
SELECT sub.*, sub.rank as Rank
FROM
(
   SELECT *,  (@rownum := @rownum + 1) as rank
   FROM msgtable 
   WHERE cdate = '18/07/2012'
) sub
WHERE rank BETWEEN ((@PageNum - 1) * @PageSize + 1)
  AND (@PageNum * @PageSize)

Every time you pass the parameters @PageNum and the @PageSize to get the specific page you want. For exmple the first 10 rows would be @PageNum = 1 and @PageSize = 10

Upvotes: 2

AlbertFerras
AlbertFerras

Reputation: 726

You can use postgresql Cursors

BEGIN;
DECLARE C CURSOR FOR where * FROM msgtable where cdate='18/07/2012';

Then use

FETCH 10 FROM C;

to fetch 10 rows.

Finnish with

COMMIT;

to close the cursor.

But if you need to make a query in different processes, LIMIT and OFFSET as suggested by @Praveen Kumar is better

Upvotes: 0

Related Questions