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?

 

[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.