> Does anyone know if it is possible to find the most recently added table
> in a database ?
> Every week, I add a new table containing updated data into a statistics
> database, and the data is loaded using LOAD DATA INFILE. I then use Perl
> DBI to access this and return a result based on a search.
> What I want to be able to do is to have Perl scan the tables in the
> database and run the query on the latest table.
> Is this possible, and if so, how should I do it ? I don't /think/ this
> is Perl question - I am sure that here must be a field or variable I can
> interrogate ?
Well, as far as I can tell from the manual, there is no record of a table's
creation date that you can access via SQL, so you'll have to make your own
solution by simply adding the date of creation to the table's name. For
example, name the tables 'yourname_YYYY-MM-DD'.
All you then need to do in your Perl scripts is to perform a SHOW TABLES
query to get all the table names. You won't be able to get MySQL to sort the
results (it can only do a LIKE clause on SHOW TABLES). But, with a bit of
trickery, you can sort it yourself. Probably something like splitting the
table 'name' and 'date' into a two dimensional array, converting the date
into a numerical timestamp somehow and sorting the array by the timestamp.
The first (or last, depending on sort order) array element will then be the
table you want.
BTW, I'm no Perl expert, so what I describe above about arrays may not be a
good way to do it - I was just thinking in terms of how I would do it in
PHP. I'm sure it's applicable to Perl, though. :)
Basil Hussain (basil.hussain@stripped)