MySQL Query with while loop

Question

So, I need to make MySQL query where I select data according to time.

1| SET @t = 1490894826;
2| SELECT id
3| FROM table
4| WHERE status=1 AND
5| fav>500 AND
6| time BETWEEN @t-86400 AND @t
7| LIMIT 10

My question is, I need 10 rows from this query, but if it gives me 7 rows(for example), how can I set my query to do it again and again while number of selected rows is not 10 or time is bigger than 1480531730, and every next time @t variable needs to be substracted by 86400. Sorry for my english, take a look how I did it using PHP just to show you what I really want and I hope it will be more clearly.

$time=1490894826;
do{
   $query = $db->query("SELECT * FROM table WHERE status=1 AND fav>500 AND time BETWEEN ? AND ? LIMIT 10",[$time-86400,$time]);
   $time=$time-86400;
}while($query->rowCount()!=10||$time>1480531730);

Thank you

If I'm understanding your question correctly.. You aren't trying to get the syntax of the query correct per say... but instead wanting the Query its self to continue retrying Until one of the 2 conditions are true? (Rows > 10 OR Time >1490894826 ) — DarbyM
– DarbyM, Commented Mar 30, 2017 at 19:01
The only mechanism in MySQL to get a query to "do it again and again" is to create a stored procedure, and have that stored procedure run as an event on a schedule to give a better answer, can you please explain what you mean by "I need 10 rows from this query," eg, where are these 10 rows going? display in MySQL work bench? Written to another table? Returned to a PGP script? — DarbyM
– DarbyM, Commented Mar 30, 2017 at 19:09

Gordon Linoff · Accepted Answer · 2017-03-30 18:58:47Z

2

I would suggest that you do something like this:

SET @t = 1490894826;
SELECT id
FROM table
WHERE status = 1 AND fav > 500 AND
      time <= @t
ORDER BY time DESC
LIMIT 10;

That is, remove the BETWEEN and just take the 10 most recent times.

answered Mar 30, 2017 at 18:58

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alen Over a year ago

yea thats it. what worries me is only my stupid brain. thank you Gordon! :)

bobflux · Accepted Answer · 2017-03-30 18:57:59Z

1

Try this:

SET @t = 1490894826;
SELECT id FROM table WHERE status=1 AND fav>500 AND
time <= @t
ORDER BY time DESC LIMIT 10

This will sort by descending time, and give you the 10 most recent rows which have time <= @t. It is a very good idea to have an index on time, or (status,fav,time) to speed it up.

answered Mar 30, 2017 at 18:57

bobflux

11.6k3 gold badges31 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySQL Query with while loop

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related