CODEWITHSUNDEEP
phpmysqllimitoffsetrecord
Donalda Donalda
Donalda Donalda

Reputation: 99

LIMIT and OFFSET - start from beginning if offset is bigger than number of rows or query returns less records than 5

I have a table vid and some records in it. By default first 5 records are displayed in the page. If a user clicks the button jQuery runs ajax that runs php script and retrieves next 5 rows and returns the answer that replaces previous records on page and sp on. The problem is when the offset gets bigger than records left in table. Say I have 15 records. Firstly query takes first 5 records:

  $start = $_POST['start_forward'];
  $query = "SELECT * FROM vid ORDER BY datev DESC LIMIT $start,5"

  ($_POST['start_forward']

is a hidden input field that increments each time by 5 starting from 0.

So next time user clicks the button $start is now 5. Then next time 10. And when it gets to 15 - there is nothing to display as there are only 15 records at all. So I need to be sure that there are always at least 5 records to display. If less - I want to get lacking records from the beginning of the columns. So, in this case - when $start os 15 I want it to be 0 again so I can retrieve records from the start again. It should be like an infinite loop. And if the total number of records is 18. Then when $start is 15 I want get the other two lacking records from the beginning of the column. So if calculating it by id. The id of final answer records should be 16, 17, 18, 1 and 2. Last 2 should be retrieved from the beginning.

I searched for the info and what I found is:

 $query = "SELECT FOUND_ROWS();";
 $result = mysqli_query($conn, $query);

I know it will count the total number of rows in the table but I can't wrap my head around how to use it in my case. Any help?

I think jQuery code here is irrelevant as it is just an usual ajax script that sends $start_forward variable to php script.

Thanks in advance.

Upvotes: 1

Views: 2023

Answers (2)

Marten Koetsier
Marten Koetsier

Reputation: 3539

After getting the rows from the database using the query you have now, you could count the number of rows, for example with:

$count = $result->num_rows;

Then, if $count is not high enough (less than 5), you could repeat the query, this time with a different LIMIT:

$query = "SELECT * FROM vid ORDER BY datev DESC LIMIT " . (5 - $count);

Make sure to also send this amount (5 - $count) with your ajax response.

Upvotes: 1

faulix90
faulix90

Reputation: 180

You can compare in PHP if the offset is higher than the total number (num_rows) and if it's higher, then you simply deduct the total number as long as the result doesn't get negative (recursive function).

Upvotes: 0

Related Questions