SQL: Insert multiple rows between dates

[Morlan]

Hey guys,

I have a start date and an end date:

$start_date = '2011-05-01'
$end_date = '2011-05-04'

I want to insert four records between those dates so that my table looks like this:

id | date
---------------
1 | 2011-05-01
2 | 2011-05-02
3 | 2011-05-03
4 | 2011-05-04


Is this something I could do with one SQL query or would I need a PHP loop?

 

[suarve]

I'd probably use a loop, but would be in interested to see if there is a MySQL only version.

 

[GravyMonster]

Here you go:

DECLARE @Begin DateTime, @End DateTime, @Current DateTime

SET @Begin = Cast('2011-05-01' as DateTime)
SET @End = Cast('2011-05-04' as DateTime)
SET @Current = @Begin

WHILE (@Current <= @End)
BEGIN
	INSERT INTO MyTable (Date) VALUES (@Current)
	SET @Current = DateAdd(day, 1, @Current)
END

Balls. Just noticed you mentioned PHP, so I guess you wanted this in MySQL? It should be relatively easy to translate.

 

[Morlan]

DECLARE @Begin DateTime, @End DateTime, @Current DateTime

SET @Begin = Cast('2011-05-01' as DateTime)
SET @End = Cast('2011-05-04' as DateTime)
SET @Current = @Begin

WHILE (@Current <= @End)
BEGIN
	INSERT INTO MyTable (Date) VALUES (@Current)
	SET @Current = DateAdd(day, 1, @Current)
END

Thanks for this. I've tried to make it work, but there are errors.


Here's my table

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(5) NOT NULL auto_increment,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

include("includes/dbc.php");

mysql_query("DECLARE @Begin DateTime, @End DateTime, @Current DateTime

SET @Begin = Cast('2011-10-19' as DateTime)
SET @End = Cast('2011-10-21' as DateTime)
SET @Current = @Begin

WHILE (@Current <= @End)
BEGIN
	INSERT INTO test (date) VALUES (@Current)
	SET @Current = DateAdd(day, 1, @Current)
END") or die(mysql_error());

I get an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @Begin DateTime, @End DateTime, @Current DateTime SET @Begin = Cast('20' at line 1

I'm a SQL noob. Any help would be greatly appreciated.

 

[sharkkk]

http://dev.mysql.com/doc/refman/5.1/en/while-statement.html

 

[nervex]

would probably do something like this

$start_date = mktime(0,0,1,5,1,2011);
$end_date = mktime(0,0,1,5,4,2011);
$qry=array();
while($start_date < $end_date){
	$qry[] = '('.date('Y-m-d', $start_date).')';
	$start_date += 86400;
}
mysql_query("INSERT INTO `table` (`date`) VALUES ".implode(', ', $qry));