SEO

I have created a custom Apache logfile entries database using Php and
MySQL. It reads the logfiles and inserts each entry into a database.

My original idea was to create a new database table every month to keep
the table sizes smaller - and ... well ... it just seemed like it would
be much more manageable.

It appears to me now that I made a mistake, so I would like to now merge
four tables into one and I am wondering what the easiest way to do that is.

I currently have four tables:
jan06
feb06
mar06
april06

They are identical in structure. They have an entry_id as the primary
key (auto_increment) and then columns to hold the log file data for each
entry. I think I know how to insert all the records from each table
into one new table (simply called "logentries"), but I am confused about
how to handle the auto increment column - the primary key. Each table
starts at 1 so they use the same values.

Does MySQL have a built-in procedure for merging these tables? What
would be the easiest way to copy (or merge) all four of these tables
into one - with an auto_incremented primary key (entry_id)?

Thanks in Advance,

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
*****************************

>I have created a custom Apache logfile entries database using Php and
>MySQL. It reads the logfiles and inserts each entry into a database.
>
>My original idea was to create a new database table every month to keep
>the table sizes smaller - and ... well ... it just seemed like it would
>be much more manageable.

Using variable table names is almost always a mistake. It is preferable
to add a column and put what would have been the table name, or part of
it, into that column.

>It appears to me now that I made a mistake, so I would like to now merge
>four tables into one and I am wondering what the easiest way to do that is.
>
>I currently have four tables:
>jan06
>feb06
>mar06
>april06
>
>They are identical in structure. They have an entry_id as the primary
>key (auto_increment) and then columns to hold the log file data for each
>entry. I think I know how to insert all the records from each table
>into one new table (simply called "logentries"), but I am confused about
>how to handle the auto increment column - the primary key. Each table
>starts at 1 so they use the same values.
>
>Does MySQL have a built-in procedure for merging these tables? What
>would be the easiest way to copy (or merge) all four of these tables
>into one - with an auto_incremented primary key (entry_id)?

Something like:

INSERT INTO bigtable(entry_id, whole, bunch, of, other, fields)
SELECT null, whole, bunch, of, other, fields from jan06 ;

Note that this just throws out the existing entry_id and creates
a new one.

Some issues: If you've got four tables each with an entry_id and
it's referenced elsewhere so you can't just throw away the existing
entry_id and generate a new one, you've got a problem. You might
try adding 10000 to all the IDs from jan06, 20000 from all the IDs
from feb06, etc. so the numbers don't overlap. You still have
to deal with fixing the references the same way.

INSERT INTO bigtable(entry_id, whole, bunch, of, other, fields)
SELECT entry_id + 10000, whole, bunch, of, other, fields from jan06 ;
INSERT INTO bigtable(entry_id, whole, bunch, of, other, fields)
SELECT entry_id + 20000, whole, bunch, of, other, fields from feb06 ;

I presume there's a date field in there somewhere so you can still
tell what month the row belongs to.

Gordon L. Burditt

Gordon Burditt wrote:

>>I have created a custom Apache logfile entries database using Php and
>>MySQL. It reads the logfiles and inserts each entry into a database.
>>
>>My original idea was to create a new database table every month to keep
>>the table sizes smaller - and ... well ... it just seemed like it would
>>be much more manageable.
>>
>>
>
>Using variable table names is almost always a mistake. It is preferable
>to add a column and put what would have been the table name, or part of
>it, into that column.
>
>
>
>>It appears to me now that I made a mistake, so I would like to now merge
>>four tables into one and I am wondering what the easiest way to do that is.
>>
>>I currently have four tables:
>>jan06
>>feb06
>>mar06
>>april06
>>
>>They are identical in structure. They have an entry_id as the primary
>>key (auto_increment) and then columns to hold the log file data for each
>>entry. I think I know how to insert all the records from each table
>>into one new table (simply called "logentries"), but I am confused about
>>how to handle the auto increment column - the primary key. Each table
>>starts at 1 so they use the same values.
>>
>>Does MySQL have a built-in procedure for merging these tables? What
>>would be the easiest way to copy (or merge) all four of these tables
>>into one - with an auto_incremented primary key (entry_id)?
>>
>>
>
>Something like:
>
> INSERT INTO bigtable(entry_id, whole, bunch, of, other, fields)
> SELECT null, whole, bunch, of, other, fields from jan06 ;
>
>Note that this just throws out the existing entry_id and creates
>a new one.
>
>
Exactly what I want to do. I can create this INSERT/SELECT using
PhpMyAdmin. In fact ... I just did. Very nice. Thanks.

>Some issues: If you've got four tables each with an entry_id and
>it's referenced elsewhere so you can't just throw away the existing
>entry_id and generate a new one, you've got a problem.
>
Thankfully I don't have that problem, but thanks for going into this detail.

>I presume there's a date field in there somewhere so you can still
>tell what month the row belongs to.
>
>
Yes, I do. There's a datetime field for the date and time of the http
request.

Thanks for the help.

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
*****************************