How to load the news is not in chronological order?

The site has a news feed while the user flips through the first 10 news, in DB can be added new or deleted some of those right now, looks user

There is also a preview mode according to the rating where the rating can be from 0 to 5, of course in this scenario the chronology of the conclusion deteriorate, and can no longer be based on the latest ID news when uploading new (WHERE `id` > blabla)

The query looks something like this:
SELECT *, (cunning the formula for calculating the rating) as rating FROM `news`
WHERE (some condition for additional sorting categories)
ORDER BY `rating` ASC
LIMIT 20


How to load new news?
June 26th 19 at 14:24
3 answers
June 26th 19 at 14:26
Solution
Just use the pagination control:
SELECT * FROM something `news`
WHERE (some condition to filter by category) AND date < "time of page hit"
ORDER BY `something` ASC
LIMIT 20, 20

I have added this request only two small changes. First - set the offset for the LIMIT, it is necessary to calculate in PHP and insert into a query. The first number is just the page number minus one, multiplied by the number of posts on the page (page_num-1)*page_size. The second digit as usual, the number of posts on the page.
Second izmeneniye added additional where condition: select only the news that was already in the database, the user has opened the page. This is because if in the process of loading of the news is added to another, the page will go (you might notice this when flipping Habr and on the next page, first see the last post on the previous), and some news sabbionetta in the list on the client. Time of page hit is to be stored on the client and transmitted to the server when the upload of each piece of data.

This method depends on how you sort news by date, rating, any other field... the main thing that podruzka the first and all subsequent were sorted the same way.
I'm a little not understand how to work the `date` if, theoretically, a rating of (something) will go opposite of at odds with the date, and the next news will be a date greater than the last created user, then `date <` just throw the result - Vicenta commented on June 26th 19 at 14:29
no. Look closely, I am guided not on the date some news from the first sample and the date of the receipt page. The user will be given all the news that was already in the database at the time of page loading. - Shirley.Wunsch commented on June 26th 19 at 14:32
: then I'm a little confused as to what should be kept in a date column in the table news? - Vicenta commented on June 26th 19 at 14:35
Date (and time) of publication of news. Describe with an example. Suppose in database there are 25 news. The user comes to the site and it loaded the first 20 news. This time, all the news in the database are publication time that is less than the current. Remember the current time (it is logical to do it on the client, i.e., JS).
While the user is staring at the headlines, someone adds another news. Now 26, news. The time of publication attached newsletter for more than the time that we will remember for the client.
Uploading the next batch of news we tell the server "give me 20 news conceding 20 from the beginning, but work only with those news, the publication time which is less than what I give you". Thus, we 26 news in the database, not look added, skip the first 20 and get the last 5.

If we pay attention to new news, it could be the case that the news, by our rules sort gets into the top twenty. Then, the news which was at number 20 when you first download, be news at number 21, with the download of the second part of the data, and the user in the list of news will be repeated.

This approach is not ideal. The user will not see the new news as leafs down. The user can stumble into a remote the news, if a long time will look at the list of news. But usually, the news is more often added than deleted, and the likelihood that the user will click on a link with non-existent news can be neglected.
The system of complete synchronization list from the user list in a database on a news site would be redundant. This will require a constant connection to the server and a full client application in JS. This is how to hammer in nails a microscope. - Shirley.Wunsch commented on June 26th 19 at 14:38
I get it now. Thank you - Vicenta commented on June 26th 19 at 14:41
June 26th 19 at 14:28
determine for yourself what is more important, less important and specify it using a comma in the order by

p.s. where - it does not sort and selection
June 26th 19 at 14:30
Why not use the output of date/publication?
Because on the website there is a button "Most popular" - Vicenta commented on June 26th 19 at 14:33
well rated I date of publication - Shirley.Wunsch commented on June 26th 19 at 14:36

Find more questions by tags SQLMySQLPHP