Here is a table of articles, id, date, title, description, view. What do you think, what is the most humane and correct (proven by your personal experience) way to implement anchoring an article when sampling on a page.

Suppose we add a field [position INT (10) NOT NULL default '1'] and it will appear in the sample under the ORDER BY position ASC / DESC. But there may be a coincidence of values ​​and confusion.

Or add the [status INT (10) NOT NULL default '5'] field and paint the response when sampling according to conditions from 1-5, where 5 are default articles (2 is possible as well, for example).

And what do you think?

  • if position 1 is fixed, if 0 then no, then everything will work. - Naumov
  • then it makes sense ORDER BY date, position. And if we suppose you want to have several output options and use display: table-header-group and display: table-footer-group for this - Andrew
  • What do we think mean? You formulate the task, and not how we think ask. Write what exactly you want to "fix". - teran
  • in my opinion everything is clear) - Andrew
  • @Andrew duck and with the decision everything is obvious, but you start to invent some incomprehensible things, so it starts to seem that everything may not be obvious. - teran

2 answers 2

Suppose there is a table with the fields id, date, title, description, view .
To fix the record in the top, you can add another boolean field is_top , which will indicate the sign of placement in the top. Suppose that posts are sorted by date, and is_top = 1 means placement in the top. Then ORDER BY will be like this:

 ORDER BY is_top desc, date

As a result, in the selection, the first posts will be fixed, sorted by date, and then ordinary posts, also sorted by date, will go.

    Of course, for a meaningful response, it would be desirable to know exactly what is meant by the term "fixing an article when sampling on the page."

    I agree that the option of the "is_top" additional field is the most humane and most used.

    If you want to do without an additional field, you can simply make the ID field sign. And to fix / undock an article, change the sign without changing the value ( UPDATE table SET id = -id ). Then, when sorting by ascending ID, the fixed ones will be the very first. Needless to say the method does not apply if there are FK on the ID table.

    • Change ID bad form. If it is correct to use FK, then the change in a head will occur in all associated keys. But what if something works with the data on the ID at the time of its replacement? - ilyaplot
    • I am considering a simple way to carry out our plans. But let the ratio of profits and rakes be evaluated by the one to whom it is implemented. Ps. The client should not at all not see what the ID - even should not know about it. This field is not for him, but for the server. for its data integrity and consistency control subsystem. Pps. If all the same ID is used by the client, it is not so difficult to provide for identification by ABS (ID). With interactive work with ONE recording, this is permissible. - Akina
    • I meant the post editor, for example. Suppose a person opened the post editing page, he edited its contents for half an hour. During this time, the ID of the post has been changed. Variants of events two. Either an error occurs, or a new post is added. - ilyaplot
    • So do I. Stupidly UPDATE ... WHERE ABS(id) = ABS($id) - Akina