List:General Discussion« Previous MessageNext Message »
From:Basil Hussain Date:January 5 2001 1:55pm
Subject:Re: How to find most recently added table in a database
View as plain text  
Hi,

> 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. :)

Regards,

------------------------------------------------
Basil Hussain (basil.hussain@stripped)

Thread
How to find most recently added table in a databasePeter T Garner5 Jan
  • Re: How to find most recently added table in a databaseDavid Hodgkinson5 Jan
  • Re: How to find most recently added table in a databaseBasil Hussain5 Jan
  • Re: How to find most recently added table in a databasePeter T Garner5 Jan
  • Re: How to find most recently added table in a databaseDan Nelson5 Jan
    • Meaning of SHOW TABLE STATUS info? (Was: How to find mostrecently added table in a database)Basil Hussain5 Jan
      • Re: Meaning of SHOW TABLE STATUS info? (Was: How to find mostrecently added table in a database)Jody Williams5 Jan
        • RE: Meaning of SHOW TABLE STATUS info?Carsten H. Pedersen5 Jan
          • Re: Meaning of SHOW TABLE STATUS info?Tim Bunce8 Jan