If you have not used DBI before then this article by MJD is a good place to start.

You would proceed along these lines. To begin create the database and connect to it. Now create the "TableIndex" table once only.

CREATE TABLE TableIndex
(
    table_date    date,
    table_id      integer
)
[download]

Each day you add an entry to the "TableIndex" table that consists of the date and a unique name "table_id" for the days data table (the concatenated date would do or an MD5 hash or an incremented integer - whatever). Now create a new table called "table_id" and insert your days data. Each day you will add one record to the TableIndex table and create one new table in the database. This should get you started:

my ($dbh,$sth,$sql);

# connect to database
$dbh = DBI->connect('DBI:Oracle:mydata')
    or die "Couldn't connect to database: " . DBI->errstr;

# do some stuff to get data into $date and $table_id

# insert the days table date and table_id into the index
$sql = 'INSERT INTO TableIndex VALUES (?,?)';
$sth = $dbh->prepare($sql)
    or die "Couldn't prepare SQL\n$sql\n" . $dbh->errstr;
$sth->execute($date,$table_id)
    or die "Couldn't execute SQL\n$sql\n" . $dbh->errstr;
$sth->finish;

# create a new table to hold the days data
$sql =<<SQL;
CREATE TABLE "$table_id" 
(
    data1    varchar(10),
    data2    varchar(10)
)
SQL;
# now execute this sql to create the table

# insert the data into the new table
$sql = qq/INSERT INTO "$table_id" VALUES (?,?)/;
# now insert your data...etc
[download]

You can now access you data by date by quering the "TableIndex" table for the table id(s) you are interested in and then quering that/those table(s)

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

tachyon,

This is indeed a nice solution, but I don't see a reason to create a new table for each day.

Isn't this what relational databases are for. I just would the data in one or more tables with the date stamp as Primary Key.
This way, when you want to retrieve the data from a specific day, you only need one query, instead of fetching first the table name, and than doing the actual query on this table.
At least, that is my honest opinion.
---------------------------
Dr. Mark Ceulemans
Senior Consultant
IT Masters, Belgium

Thank you for your reply :)

I was thinking about making the script that way you showed me but wanted to make sure if its efficient since they'll be 1000+ data tables in the next 3 yrs or so. I'm using MySQL on a Win2K server. The data that is going to be inputted for each day would be around 2-10 names of people.

Anthony

Sounds to me like you most defenitly want to use a timestamp and one table with 1000s of entries, instead of 1000s of tables. Not sure if MySQL is optimized for such a large number of tables, but a large number of rows in a single table is extreamly common.

-- Dan

Would it work to keep a timestamp on your table, searching only for specified days? It seems easier to me that way.