SQL Query Once Daily in Sequential ID order

Question

I'm using Dreamweaver CS5 and MySQL. I had a similar discussion about this on a previous post here:

The query I am using from that discussion is:

select description from Weather
where ID = 1 + MOD(
    (EXTRACT(DAY FROM CURDATE()) 
    * EXTRACT(MONTH FROM CURDATE()) 
    * EXTRACT(YEAR FROM CURDATE())),
(select MAX(ID) from Weather));

I am working on adding a new feature to my website. I am having a hard time trying to change the query, instead of it randomly picking an ID from the table I want to to query in sequential order, then after hitting the max ID, returning to ID 1. I am using unique ID's for this SQL data base and there will be 28 rows.

Any help would be awesome! Thank you!

Update****

This works, only if I manually change the date to today 2017-02-02:

SELECT description
FROM `Moon`
WHERE ID = 1 + MOD(DATEDIFF('2017-01-01',CURDATE()), (SELECT MAX(ID) from `Moon`))";

<script type="text/javascript">
setInterval("my_function();",100000); 

function my_function(){
    $('#weather').load('website.php #today');
}
 <div id="weather">
                <div id="today">
                <?php echo ucfirst($row_Recordset2['description']); ?>
                </div>
                </div>

I am using .load to refresh the data every 24 hours. How do I correct this so I won't have to manually update the date?

You will have to keep track of what was the last row returned. Otherwise there isn't a way to tell which row you need to return next. — Renats Stozkovs, Feb 02 '17 at 19:49
SQL isn't my strong point, I've been slowly teaching myself how to use it. Do you have an example of what that would look like given the string I am already using? — Cyndi, Feb 02 '17 at 19:50
Are you trying to rotate 'text' daily or on each query? I assume query is executed multiple times a day. — Renats Stozkovs, Feb 02 '17 at 19:55
I'm trying to rotate the text daily. I want it to dynamically change once every 24 hour period. — Cyndi, Feb 02 '17 at 19:59
Thank you very much bc! :) With Mike's answer once it reaches row 28 is it going to automatically move back to ID 1 and complete the cycle again? — Cyndi, Feb 02 '17 at 20:11
It looks like DATEDIFF is throwing an SQL error. It's not completing the query. I am sure I am doing something wrong here. — Cyndi, Feb 02 '17 at 20:56
It looks like DATEDIFF is producing an error. "incorrect parameter count in the call to native function 'DATEDIFF' MySQL #1582" — Cyndi, Feb 02 '17 at 21:02

score 1 · Answer 1 · answered Feb 02 '17 at 19:50

1

Take number of days since a fixed date, then MOD on 28

SELECT text
FROM WeatherTexts
WHERE ID = DATEDIFF(day,'2017-01-01',CURDATE()) % (SELECT MAX(ID) from WeatherTexts) + 1

answered Feb 02 '17 at 19:50

Mike

335
4
20

This is assuming daily text rotation; from the question it sounds like she wants a different text on each query. – Renats Stozkovs Feb 02 '17 at 19:53
Ahh, then yes some stored variable is a requirement. – Mike Feb 02 '17 at 19:55
1

It's my poor explanation, I'm sorry BC. I think Mike might have what I need here. Everything is already set to pull every 24 hour period, which is fine, just as long as it makes a new query once per day and displays the next ID in the sequential order. Will this also have it start back to ID 1 once it reaches day 28? The text I want to display is already contained n the data base, I'll just be displaying it on the website. I want it to refresh on the 24 hour timer with the next row perpetually until it reaches the end and starts over. – Cyndi Feb 02 '17 at 19:57
It looks like DATEDIFF is producing an error. "incorrect parameter count in the call to native function 'DATEDIFF' MySQL #1582" – Cyndi Feb 02 '17 at 21:02

Renats Stozkovs · Accepted Answer · 2017-02-02T22:00:18.220

1

Try this:

SELECT text
FROM WeatherTexts
WHERE ID = 1 + MOD(DATEDIFF('2017-01-01',CURDATE()), (SELECT MAX(ID) from WeatherTexts));

edited Feb 02 '17 at 22:00

answered Feb 02 '17 at 21:12

Renats Stozkovs

2,549
10
22
26

Unfortunately this is returning no data from the table. $query_Recordset2 = "SELECT description FROM `Moon` WHERE ID = DATEDIFF('2017-01-01',CURDATE()) % (SELECT MAX(ID) from `Moon`) + 1"; – Cyndi Feb 02 '17 at 21:19
If I change the date to 2017-02-02 it brings up the data from the table. But if it is any other date it returns no data. – Cyndi Feb 02 '17 at 21:44
Try my edit; kinda difficult to work without having MySQL instance installed – Renats Stozkovs Feb 02 '17 at 22:00
It's still not working. If I manually change the date to today 2017-02-02 it brings up the data for me, but only displays it on the website until exactly one minute later. However I am using the .load function on the text so the page doesn't have to be reloaded each time. Code added above in original text – Cyndi Feb 02 '17 at 22:10
Correction, the data is there, but if I change the date to any other day but today, it doesn't get data from the table – Cyndi Feb 02 '17 at 22:25
@Cyndi Try switching '2017-01-01' and CURDATE(). The result is negative, so I think that's why you don't get any results – Renats Stozkovs Feb 03 '17 at 20:13

SQL Query Once Daily in Sequential ID order

2 Answers2