Dan Tappin wrote:
I think you might be one to something here... is there such a thing as
a while loop in MySQL? i.e. can I fill a table with data via a MySQL
query? I guess I could do it via PHP...
I could create a temp table with one column of dates for the range I am
looking for and then LEFT JOIN my log table and match the dates.
Having a dedicated table would work but would be kind of a waste of
space / resources. These queries will not be run that often.
Dan T
No while loop, but this can be done in mysql, so long as you already have a
table with enough rows. For example, to create and fill a dates table,
starting with 1995-01-01 and ending with 2005-12-31:
# create the table with 2 extra columns, one of which is auto_increment:
CREATE TABLE dates (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
date DATE,
junk INT,
UNIQUE date_idx (date)
);
# add enough rows to the table to cover the desired date range:
INSERT INTO dates (junk) SELECT id FROM big_table LIMIT 4018;
# use the auto_increment generated ids as offsets from the start
# date to fill the date column:
UPDATE DATES SET date = '1994-12-31' + INTERVAL id DAY;
# drop the now useless, extra columns:
ALTER TABLE dates
DROP COLUMN id,
DROP COLUMN junk;
Voila, dates has one row for each day from 1995-01-01 to 2005-12-31.
This example MyISAM table with 10 years worth of rows in it takes up 63,461
bytes on my disk, including the index. If that's a "waste of space /
resources", I think it's time to buy more disk. You certainly could create
such a table on the fly, with just the rows you need, as a temporary table,
but that will be relatively slow compared to simply using a pre-existing,
dedicated table to satisfy your queries. Besides, at that small size, I
don't really see the downside.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
