CODEWITHSUNDEEP
mysql
Pawan
Pawan

Reputation: 32321

How to Know if next Record exists

The question is related to implementation of Pagination for a Page

I have a query in my application which loads the First Record of the Table 

select * from vendor_requested_categories 
where status = 0 ORDER BY document_id ASC Limit 1

The result is 

+-------------+---------+--------+------+------------------+
| document_id |   T1    |   T2   |  T3  | vendor_brand_id  |
+-------------+---------+--------+------+------------------+
|             |         |        |      |                  |
|           1 | Popcorn | Bucket | NULL |             3000 |
+-------------+---------+--------+------+------------------+

My question is :

Is it possible to know if it has next Record ??

Upvotes: 0

Views: 168

Answers (2)

Prasad Khode
Prasad Khode

Reputation: 6739

you can use count() to find the total number of records. It should be some thing like this:

SELECT document_id, T1, T2, T3, vendor_brand_id, count(*) as total_records
FROM vendor_requested_categories 
WHERE status = 0 ORDER BY document_id ASC LIMIT 1

So with this you can load the total records count and your first record at a time instead of making two queries on database.

Upvotes: 1

Sh1d0w
Sh1d0w

Reputation: 9520

For pagination creation you have to know how many records for given query (filters) you have, so you can calculate total pages depending on the limit.

So you can simply do this, before your query to get total records:

select COUNT(*) AS N from vendor_requested_categories  where status = 0

Then you can calculate the offset, from the page param:

$limit = 10;
$offset = ((int)$_GET['page'] * $limit) - $limit;

And here is the paginated query

select * from vendor_requested_categories  where status = 0  ORDER BY  document_id ASC  Limit $offset, $limit

When you know the total pages and the current page it is easy to know if you have next record.

Upvotes: 1

Related Questions