Q:

database design for updating data weekly

I find it very difficult to explain what I want to ask, So I created a hypothetical question (taking ex of temp), Maybe my question is still not very clear, But pls take a look. let me know if you need any clarification
I want to compare the temp of last Sunday and temp of temp 3 weeks ago (Sunday),
So it should show something like this. There would be two dates in select option so I want to analyze temp on Sunday before last Sunday, 

<select>
 <option> Last  Sunday (2017-12-03) </option>
 <option> Previous Sunday (2017-11-26) </option>
</select>

<h1> Temp on 2017-12-03 </h1>

<p> 24 C </p>


<h1> temp 3 weeks ago (2017-11-12)</h1>

<p> 24.4 C</p>

<h1> Change in temp </h1>

<p> .4 </p>

Assume I can get the temp only current Sunday, So I need to update this database on weekly basis (on every sunday)
city_id    sunday_temp   temp_pre_week1   temp_pre_week2  temp_pre_week3 last_sunday_date  
  1          24.0                24.3              35.2               24.4   2017-12-03            
 1           24.3                35.2               24.4               28.0  2017-11-26               
 2           3.0                 4.0                2.0                6.0  2017-12-03            
 2           4.0                 2.0                6.0                7.0  2017-11-26               
 .
 .
around 2000 rows

Another option that i thought would be saving the data for 4 weeks, So when user select the Sunday before last Sunday I would be able to use 4th-week column,
Something like this
city_id    sunday_temp   temp_pre_week1   temp_pre_week2   temp_pre_week3  temp_pre_week4 last_sunday_date  
 1           24.0                24.3              35.2           24.4       28.0              2017-12-03                      
 2           3.0                 4.0                2.0           6.0        7.0               2017-12-03                      
 .
 .

I only want to  analyze two Sundays not more than that, So i think adding one more column would be more efficient, 
Thanks :)

A:

Don't store all the data for a query in each row. Let the database do the work for you. Your database structure should just include city_id, date,temp. If you only have Sundays included, that's OK. You can then use something like
SELECT `temp`,`date` FROM `citydata` WHERE (`city_id` = 1) and (`date` <= '2017-12-03') ORDER BY `date` DESC LIMIT 4

which will retrieve the last 4 records available for city 1 that are on or before 2017-12-03. If you have data for every Sunday, that will get you the specific date ("last Sunday") plus the 3 previous weeks. If you decide you want more - just change the LIMIT to 5 or 6 or whatever you want. If you later decide to add different days of the week you won't have to change the database structure (though you will need to adjust your queries a bit). Most importantly, you won't need to look to previous records when you are collecting new data - you just add the new records and get the previous weeks of data when you actually need them.

