CODEWITHSUNDEEP
mysql
user1869132
user1869132

Reputation: 75

Query for pagination on the SQL side

How can I implement custom pagination in mysql using a query? Based on the page number I need to fetch the records from the database, is it possible? It would be helpful to get some queries for this.

Thanks.

Upvotes: 1

Views: 2310

Answers (3)

Prashant16
Prashant16

Reputation: 1526

SELECT * FROM items LIMIT 0,10

To display your pagination you also need to know the total of rows in order to calculate the number of page links to display like:

Page: 1 2 3 4 5 6 ….

Making a query to MySQL to count the number of rows like “SELECT COUNT(*) …” is not an option, you would lose some of the performance you saved with the “LIMIT” query in the first place. There’s a much more efficient way of doing this, you just need to modify your original query just a little, like:

$result = mysql_query('SELECT SQL_CALC_FOUND_ROWS * FROM items LIMIT 0,10');

Immediately after that first query you run:

$countData = mysql_fetch_assoc(mysql_query('SELECT found_rows() AS total'));
$totRows = $countData['total'];

Upvotes: 2

John Woo
John Woo

Reputation: 263693

example,

SELECT * FROM `your_table` LIMIT 0, 10

will display 10 records starting from the first record.

Upvotes: 1

sourcecode
sourcecode

Reputation: 1802

Through PHP you need:

$PageSize - means how many rows you want to dispaly.
$Pageoffset - means (CurrentPageNo * PageSize)

     $query =" select * from table limit $Pageoffset, $PageSize';
     // limit restricts the no. of rows fetching.

Like wise you can do in mysql.

Upvotes: 1

Related Questions