MySQL - How can I add consecutive dates to many existing records?

Question

I was hoping this would be easy enough to figure out on my own but apparently I need help. I can't find the answer online either so I'm asking here. Perhaps I'm just not using the right search terms.

I have a database table with 1100 existing records. There are 3 fields: id, code_name, code_date. The code_date is a date in Y-m-d format used to indicate the date on which the associated code is active.

All I want to do is autofill every record with each consecutive date starting with today's date. So, first row will be 2013-06-14, the next row will be 2013-06-15, and so on.

I feel like this should be incredibly simple but apparently I'm an idiot today. Help would be appreciated!

I can use PHP to help with this, if I need to run any loops or anything.

Got your answer for ya, let me know if it works :) – Kylie Jun 15 '13 at 03:31 — Kylie, Jun 15 '13 at 03:31

score 6 · Accepted Answer · answered Jun 15 '13 at 03:45

6

With pure SQL:

set @i = unix_timestamp(date(now())) - 86400;
update thetable set code_date = date(from_unixtime(@i:=@i+86400)) order by id;

86400 is the number of seconds in a day.

answered Jun 15 '13 at 03:45

urraka

997
7
9

This did exactly what I wanted without having to use any PHP. Thank you very much! – vertigoelectric Jun 15 '13 at 04:17
Very nice 2 line answer with no PHP. I hope you're around to answer next time I have a MySQL question! – Buttle Butkus Jun 15 '13 at 04:24
Yes. At first it returned an error and I was disappointed but then I realized that my actual field is `code_day` and I mistakenly said `code_date` in the post... but again, this solution works great, and THANKS! – vertigoelectric Jun 15 '13 at 07:42

ThatTechGuy · Answer 2 · 2013-06-15T03:46:43.423

$records = mysql_fetch_assoc(mysql_query(SELECT * FROM records_table));
$start_date = strtotime('now');

foreach ($records as $record)
       {
           $record['time'] = strtotime('+1 day' , $start_date = time());
           $start_date = $record['time'];

           mysql_query ("UPDATE records_table
                         SET time = $record['time']
                         WHERE id = $record['id']");
       }

My strtotime function construction might be a bit off, if someone could verify/edit but other then that I believe that would accomplish the task. Also if you need to order the rows then in the original assoc array query simply add "ORDER BY id desc" for example if you wanted to order the rows by the ID in descending order.

You have syntax error here...$strtotime('+1 day' , $start_date = time()); should be strtotime('+1 day' , $start_date = time()); — Kylie, Jun 15 '13 at 03:43

score 0 · Answer 3 · answered Jun 15 '13 at 03:04

0

is this what you are looking for?

its a date_add function in MySql

answered Jun 15 '13 at 03:04

voidMainReturn

3,339
6
38
66

I know about the date functions in MySQL, but I don't know how to use it to do what I need. – vertigoelectric Jun 15 '13 at 03:10

score 0 · Answer 4 · edited May 23 '17 at 12:23

SELECT `id` FROM `table` ORDER BY `id` ASC

Get the results of the above query and put it into an array, such as $ids.

Refer to this link I used to add days.

//$today = date('Y-m-d');

$sql = 'INSERT IGNORE INTO `table` (`id`, `code_date`)';
$sql .= 'VALUES ';

$values_array = array();
$i = 0;
foreach($ids as $id) {
  $values_array[] = '($id, "' . date('Y-m-d', strtotime('+' . $i . ' day') . '");
  $i++;
}
$sql .= implode(',' . "\n",$values_array);

$sql .= "\n" . 'ON DUPLICATE KEY UPDATE `code_date`=VALUES(`code_date`)';

Now you have have your query. Send it with mysql or PDO or whatever.

E.g.

$result = mysql_query($sql);

Explanation (in case it's not obvious): 1.) You got all the existing ids with the SELECT query. 2.) You set $today variable with today's date. 3.) You construct INSERT query using foreach loop to increment the days for each id. 4.) You send the query.

Let me know if that works. It should, unless I made a typo or some other minor mistake. I did not test it.

Kylie · Answer 5 · 2013-06-15T03:29:15.820

This should work for you, theres probably a more efficient way, but this is how I would do it.

//Get the database values
     $q = mysql_query("SELECT * FROM yourtable");
     $res = mysql_fetch_array($q);
     $date = date('Y-m-d', strtotime('now'));

 //Or you can specify like $date = date('Y-m-d', strtotime('2013-06-14'));
 //Either way

     foreach ($res as $val)
   {   
       //update the row
       mysql_query ("UPDATE yourtable
                     SET code_date = $date
                     WHERE id = $val['id']")
       //Increment date forward..
       $date = date('Y-m-d', strtotime( '+1 day' , strtotime ($date) ));


   }

For the record, I'm actually using MySQLi to access the database... but I get the idea. I'll try it. — vertigoelectric, Jun 15 '13 at 03:41

MySQL - How can I add consecutive dates to many existing records?

5 Answers5

Linked