There is a two table query

SELECT w.*, u.name as user FROM words as w JOIN users as u ON (u.id = w.user_id) WHERE w.status = "approved" ORDER BY w.created_at LIMIT 5

Request select the words from the database, added by users. The task is to add the ability to include in this request a specific record by its id .

That is, the first five records + are being selected for this request; page navigation is provided for, and later, when the next page is called, the values ​​in LIMIT change.

But you need to make it so that you can record, for example with id = 2 , output together with the first request, and not include them in subsequent ones. So make it possible?

If poorly explained: By default, all entries should be displayed as follows. as output with the request described above.

But if the GET-parameter record_id in the address bar is added, with a value of, say, 2, then an entry with id = 2 must be included in the request, even if it was not intended there.

  • one
    Add the union такой-же-select to the end of your query union такой-же-select , but with where id = 2, for order by work to be possible, the subquery may need to be enclosed in brackets and make select * from it - Mike
  • Alternatively, the solution from this question is .stackoverflow.com/ questions/ 269361/… - Visman

1 answer 1

You can do the following, add OR u.id = 2 to the WHERE condition, then the record with the identifier 2 is guaranteed to be included in the selection. In order for this record to fall into the first block, you need to position it at the beginning of the resulting table. To do this, you can modify the ORDER BY construct by placing a dynamic expression in front of the w.created_at column, which returns 0 to be written with u.id = 2 and 1 for all others. As a result, the record with u.id = 2 will be fixed at the beginning of the resulting table and will be displayed first.

 SELECT w.*, u.name as user FROM words as w JOIN users as u ON u.id = w.user_id WHERE w.status = "approved" OR u.id = 2 ORDER BY IF(u.id = 2, 0, 1), w.created_at LIMIT 5