Geek Answers Handbook

How do I request this in mysql

I have a web application in which I show a series of messages based on this table schema (there are thousands of rows like this and other columns too (deleted as not required for this question)): -

+---------+----------+----------+ | ID | COL1 | COL2 | +---------+----------+----------+ | 1 | NULL | ---- | | 2 | --- | NULL | | 3 | NULL | ---- | | 4 | --- | NULL | | 5 | NULL | NULL | | 6 | --- | NULL | | 7 | NULL | ---- | | 8 | --- | NULL | +---------+----------+----------+

And I use this query: -

 SELECT * from `TABLE` WHERE `COL1` IS NOT NULL AND `COL2` IS NULL ORDER BY `COL1`;

And the result of the set is obtained as follows: -

 +---------+----------+----------+ | ID | COL1 | COL2 | +---------+----------+----------+ | 12 | --- | NULL | | 1 | --- | NULL | | 6 | --- | NULL | | 8 | --- | NULL | | 11 | --- | NULL | | 13 | --- | NULL | | 5 | --- | NULL | | 9 | --- | NULL | | 17 | --- | NULL | | 21 | --- | NULL | | 23 | --- | NULL | | 4 | --- | NULL | | 32 | --- | NULL | | 58 | --- | NULL | | 61 | --- | NULL | | 43 | --- | NULL | +---------+----------+----------+

Note that the identifier column is mixed using the order by clause.

I have the correct indexes to optimize these queries. Now let me explain the real problem. In my web application, I have a lazy function. So, I show about 10 posts per page, using LIMIT 10 after the request for the first page.

We are good so far. But the real problem arises when I have to load a second page. What am I requesting now? I do not want messages to be repeated. And there are new messages that appear almost every 15 seconds, which makes them go on top (top literally means the first line) of the result set (I do not want to display these last messages on the second or third page, but they change so I can not use LIMIT 10,10 for the second page, and so on, as the messages will be repeated.).

Now, all I know is the last identifier of the posted message. Say 21 here. So, I want to display messages ID 23,4,32,58,61,43 (see the table of results above). Now load all the lines without using the LIMIT and display the 10 identifiers that appear after id 21 . But for this I will have to interact with thousands of useless strings. But I can’t use the LIMIT for the 2nd, 3rd ... pages, which is for sure. Also, the ids are mixed, so I definitely can't use WHERE ID>... So where are we going now?

+8
sql php mysql
source share
4 answers

Hmm .. I thought a little and came up with 2 solutions .: -

  • To save the identifiers of an already published message and request WHERE ID NOT IN(id1,id2,...) . But this will require additional memory. And if the user loads 100 pages, and the identifiers - in 100000, then one GET request will not be able to process it. At least not in all browsers. POST request can be used.

  • Change the way messages are displayed from COL1 . I do not know if this is good for you. But it can save you some tape and make your code cleaner. It could also be a better way. I would suggest the following: - SELECT * from TABLE where COL1 IS NOT NULL AND COL2 IS NULL AND Id>.. ORDER BY ID DESC LIMIT 10,10 . This can affect how you display your jumping and restriction messages. But, as you said in your comments that you check to see if the message meets the criteria and changes COL1 from NULL to the current timestammp, I assume that the more messages, the higher you want to display them. This is just an idea.

+2
source share

I am not sure if I understood your question correctly, but here is how I think I will do it:

  • Add a timestamp column to your table, call date_added
  • When displaying the first page, use your as-is request (with LIMIT 10 ) and insert it into the timestamp of the most recent record; call him last_date_added .
  • For the 2nd, 3rd, and subsequent pages, modify your query to filter out all entries using date_added > last_date_added , and use LIMIT 10, 10 , LIMIT 20, 10 , LIMIT 30, 10 , etc.

This will freeze your result set in time and reset it every time you access the first page.

Notes:

  • Depending on the order of your result set, you may need a separate request to get last_date_added . Alternatively, you can simply disconnect at the current time, that is, the time when the first page was loaded.
  • If your identifiers are sequential, you can use the same identifier trick.
+3
source share

I assume that new messages will be added with a higher identifier than the current maximum ID-right? So you could not just run your request and capture the current maximum ID. Then, when you request for page 2, execute the same request, but with "ID <max_id". This should give you the same result as your query on page 1, because any newlines will have ID> max_id. Hope this helps?

+1
source share

What about?

 ORDER BY `COL1`,`ID`;

It will always contain identifiers in order. This will allow you to use:

 LIMIT 10,10

for your second page.

+1
source share

More articles:


All Articles