Move Multiple Tables into One, with Addition of Extra Field Value

Question

I have 31 separate tables (actually I have 365, but lets keep this simple) in a MySQL database, each containing data for a given day. The tables are (badly) named based on the day.

Example:

island01Aug07
island02Aug07
island03Aug07
island04Aug07
...
island31Aug07

I would like to combine all the tables into one master table:

island_08

It would be simple to use INSERT INTO but my problem is that the tables do not have a column to denote the day. It would have to be added into the destination table, and then I would need to populate that when moving/copying the tables over.

Suggestions, advice and solutions welcome.

Answer 1

CREATE TABLE island_08 (mydate DATE NOT NULL, field1 …)

INSERT
INTO     island_08 (mydate, field1, field2)
SELECT  '2007-07-01', field1, field2
FROM    island01Aug07
UNION ALL
SELECT  '2007-07-02', field1, field2
FROM    island02Aug07
UNION ALL
…

Answer 2

As alternative option you can list all tables in to array like table_name=>mysql_date, after that loop through and copy data from one table and insert in to another. After data was transferred successfully you can remove the table.

Here is example of getting list of tables and extracting date from it:

$prefix = 'island';
$lenght = strlen($prefix);

$result = $this->query("SHOW TABLES LIKE '{$prefix}%'");

$arrayDates = array();

if($db->num_rows($result))
{
 while($v = $db->fetch_array($result))
 {
  $mysql_table = current($v);

  $arrayDates[$mysql_table] = date('d-m-Y',strtotime(substr($mysql_table,0,$lenght))); 
 }
}
//Now you can walk through your array and copy data from one table tyo another and append you mysql value