Combining 3 Tables Into 1

Tim_Silva

Hi,

I am following some advice I got at another forum, here at PHPbuilder. I am combining 3 mySQL tables into a single one. I was able to export the tables as csv and than import the data into the newTable.

The problem I am having is the following. Since all 3 tables have the same structure, they also have the same ID values. The first table I imported was fine. The last ID in this first table was 219.

When I imported the second table, however, I was hoping to get the ID’s in the second imported table to be replaced by 220, 221, 222… etc. Instead, what I got was a much larger ID’s: 106901, 106902, 106903…etc. Not only that, but all other fields were pushed one column to the right.

I tried to fix the problem by deleting the ID field when I exported. It solved the problem of wrong fields but the large ID numbers continued.

Of course I could change the IDs row by row but I am sure this is not the right way to do it.

Did I do something wrong when I exported the table, when I imported, or both?

BTW, I am using PhpMyAdmin.

Thanks,

Tim

Piranha

I don't know if anything went wrong or not, but I can tell you that you don't have to worry about the id being large. It is just for the database, so the numbers really doesn't matter at all.

Tim_Silva

Piranha,

I know what you're saying and I agree with you. Maybe I am just a "neat freak" but I like the idea of ID's matching the number of rows. And I am sure one might find it helpful sometimes to know that the table's IDs are in sinc with the overall table size, don't you think so?

Anyway, as far as you can tell, do you (or anybody else) see what could be causing this behavior?

Thanks,

Tim

Piranha

No, I don't think so. If that should be the case then you should change the id every time that you have deleted a row.

Basically as I said in the previous post, it doesn't matter the least. If you think it does matter then you do one of two things:
1. You use the id for something else, and that is causing a problem. I don't like that way of using PKs, but some does. It will cause troubles in that case.
2. You don't use the id for something else, the problem then is that you haven't really understood relationship databases.

About what could be causing this behavior I have no idea.

Tim_Silva

Okay, thanks. I will follow your suggestion and ignore the large ID numbers.

But know about something else. You say you don't like "that way of using primary keys". Can you elaborate on that? If the pk should not be in the IDs where then?

Tim

Piranha

Ok, I'll elaborate a bit. Let's say that you have a table with users, and the information you want to use is username and password. You won't allow more than one user with the same username, in other words, when I have chosen Piranha as a username you can't use the same username. There are basically two ways of building this table. Note that I have simplified it just to show the difference.

Option one is to use the username as the PK:
CREATE TABLE user (
   username varchar(30)
   password varchar(30)
   PRIMARY KEY (username)
)

Option two is to use something that doesn't have anything to do with the columns you will use as a PK:
CREATE TABLE user (
   id int
   username varchar(30)
   password varchar(30)
   PRIMARY KEY (id)
   UNIQUE (username)
)

Now both ways works since it will only be one user with that name. But what happens if a user want to change name? In the second case you just change the name but in the first you change the primary key.

When you have only one table without any relationships it is no problem, but let's say that users can post comments (stored in another table). What happens now when you change the name? In the second case you use id as the connection to the other table, so you still just change the name. But in the first case you have to change the name both in the user table and in the comments table.

Then use the first way in all tables of a pretty big database and you understand that you might get in trouble. With artificial ids you have one thing less to worry about.

Sxooter

Instead of exporting / importing, why not do this in place:
create table newtable (column list here);
insert into newtable (field2, field3, field4) select field2, field3, field4 from oldtable1;
insert into newtable (field2, field3, field4) select field2, field3, field4 from oldtable2;
insert into newtable (field2, field3, field4) select field2, field3, field4 from oldtable3;

assuming field1 is the autoinc, it will get set automagically.

Tim_Silva

Sxooter,

Thanks for your help. I just did what you suggested and it works as far as inserting the new rows but the problem of not following the ID number sequence remains. Instead of the expected 220, 221, 222… etc, I got a much larger IDs: 106901, 106902, 106903…etc. btw, field 1 (ID) is the pk and it is autoinc.

I was wondering, do you think this behavior could be connected to the fact that I am in a shared server. Could it be that it reflects other users in the same machine, using the same mySQL installation????

Tim

Sxooter

I don't think it's the shared server thing, seems to repeatable. Can you show us the SQL for what you're doing?