PHP - limit 10 offset 1 if rows are greater than 20

Question

I'm displaying the top 10 newest and the last 10 oldest topics after the 10 newest from database. I'm able to display what I need in the top 10 topics.My problem is that when I have 21 topics in database, the last 10 topics is displayed according to what I want,but when I have only 20 topics,the last topic in the newest will still be in the oldest.To make it more clear,here is a reference picture.

-This is what happen when I have 21 or more topics in database-

-This is what happen when I have 20 topics in database-

I don't want a topic to be repeated like what happen when I have 20 topics. Here is my code for fetching the last 10 topics from database:

    //  fetching last 10 topics from forum
 function history() {
    $sql = "SELECT * FROM (
    SELECT f_id AS id, f_title AS name,f_dept AS dept,f_last_view AS last_view
    FROM forum
    ORDER BY last_view ASC
    LIMIT 10 OFFSET 1
  ) AS `table` ORDER by last_view DESC ";

  //-run  the query against the mysql query function 
  $result=mysql_query($sql) or die(mysql_error());  


  $history = array();  

  //-create  while loop and loop through result set 
  while (($row = mysql_fetch_assoc($result)) !== false){
    $history[] = array(
        'id'            => $row['id'],
        'name'          => $row['name'],
        'dept'          => $row['dept'],
        'last_view'     => $row['last_view'],
    );
}

return $history;
}

P.S. I know that mysql_* is deprecated but please bear with me. Thank you in advance

Answer 1

Your question is a little unclear - do you want the newest 10 (which you have working) and the Oldest 10? or the newest 10 and the next newest 10?

I'll assume that what you mean is the newest 10 and next newest 10.

Your problem appears to be in understanding of the offset keyword in sql. Offset causes that number of results to be skipped.

Based on this, you are currently sorting your results ascending by last view (which I will assume is some form of timestamp) - so oldest to newest, then picking 10 of them, starting at the second one.

If what you wanted was the 11-20th newest results then try

ORDER BY last_view DESC
  LIMIT 10 OFFSET 10

within your query.

Also see this stackoverflow question with a much more detailed answer

Answer 2

There are two issues raised in your question. The first is a simple technical problem with your query:

    SELECT f_id AS id, f_title AS name,f_dept AS dept,f_last_view AS last_view
    FROM forum
    ORDER BY last_view ASC
    LIMIT 10 OFFSET 1

Will return the 11 oldest entries minus the oldest, due to your use of OFFSET 1. To get the 10 oldest, use:

SELECT f_id AS id, f_title AS name,f_dept AS dept,f_last_view AS last_view
FROM forum
ORDER BY last_view ASC
LIMIT 10

The correct use of OFFSET would be if you wanted, for example, the next 10 oldest (so if you had, say, 100 entries and wanted to output entries 81-90) which you would use:

SELECT f_id AS id, f_title AS name,f_dept AS dept,f_last_view AS last_view
FROM forum
ORDER BY last_view ASC
LIMIT 10 OFFSET 10

The second issue you raise is not wanting the "oldest" list to have entries already output by the "newest" list, which should only occur (if both lists have 10 entries) when there are 19 or less entries total in the table. So if you had 15 entries, you would want:

Newest

• 15
• 14
• 13
• 12
• 11
• 10
• 9
• 8
• 7
• 6

And the oldest list as:

Oldest

• 5
• 4
• 3
• 2
• 1

To avoid the overlap, you can write the query:

SELECT oldest.f_id AS id, f_title AS name,f_dept AS dept,f_last_view AS last_view
FROM forum AS oldest
LEFT JOIN (
     SELECT f_id FROM forum ORDER BY last_view DESC LIMIT 10 
) AS newest ON newest.f_id = oldest.f_id
     WHERE newest.f_id IS NULL
ORDER BY last_view ASC
LIMIT 10

This will ensure that any entries that would be in the top ten are filtered out, leaving only (in this example) the oldest 5 for the oldest list.

** Updated to use LEFT JOIN to grab and filter out the top 10, since LIMIT is apparently not allowed on sub-queries used in IN and similar comparison functions.