mysql - 根据创建日期选择最后两个表

Question

I need some advise on implementing my database schema properly. I have a cron script whose main functions are to Parse HTML docs => Create a DB tbl => Insert Records

I am used to run this script only once per month but I need to do it more frequently which means my database tables will increase significantly. At the moment my tables follow this format : table_03 where the last two digits represent the current month.

Now I am considering using PHP time() function to replace the current month.
My first question, is this a good way to approach it ?

My second issue is how do you go about creating a dynamic SELECT statement that fetches the last two tables in a Database based on their date ? or could be better to know if there is an MySQL query that does this job instead of relying of the table names ?

e.g

table_29_03 // 29 March
table_26_03 // 26 Mar...
table_25_03
...

My query should return the difference between the last two or three table, but not having consistent dates ( as monthly ) I am not sure how to do it.

At the moment I am doing the following :

$thisMonth = 'table_'.date('m');
$PrevMonth = 'table_'.date('m', strtotime('first day of last month'));

// find new records in this table not available in previous one
$sql = "
  SELECT * FROM `".$thisMonth."` WHERE `".$thisMonth."`.`id`
  NOT IN (
  SELECT `".$PrevMonth."`.`id`
  FROM `".$PrevMonth."`
  WHERE `".$thisMonth."`.`id` = `".$PrevMonth."`.`id`
          ); ";

Answer 1

My second issue is how do you go about creating a dynamic SELECT statement that fetches the last two tables in a Database based on their date ?

is this what you want to achieve in MySQL?

SELECT create_time,table_name 
FROM INFORMATION_SCHEMA.TABLES 
ORDER BY create_time DESC LIMIT 2

you can add additional filters like what schema should we extract the data from, etc...

As for your first question, I am using gmdate as of now to extract the month and year of time.

$now = time();
$month =  gmdate("m", $now);
$year =  gmdate("y", $now);

gmdate: time returned is in Greenwich Mean Time (GMT).

using time(); to set your month is alright and i can see no problem in that.

Side Note: If you are running PHP and MySQL on separate machines, there would be some discrepancies in time if the datetime of those two are not synced. if you want an accurate time representation of both. You should be dependent on one machine.

If you want PHP to handle time logging, you can include a column in in your tables like 'created_at' which indicates the time when it was generated, based on PHP time();

If you want MySQL to handle it, you can query first the current date select curdate(); then create the table names based on the month you fetched from your query.