[RESOLVED] Unite Two Tables in Database PHP-SQL

[lordadel]

Hello

I have 2 Databases Each one has a table called files.

Each Table contains 5 fileds (id,filename,filetag,filessize,filedate) and the id filed is autoincremental

I want to unite those 2 tables into one big table i mean i put one table under the other table which makes another big table which has also the id field autoincremental and also has all the other fields

Here is a picture of what i have in mind..




I hope that someone will help me

Thanks in advance

Cheers
Adel

[penagate]

I can think of two things that you might mean:

1. Combine the tables permanently.
   Use an INSERT query that is based on a SELECT. For example:
   

   INSERT INTO table1 (
     filename,
     filetag,
     filesize,
     filedate
   )
   SELECT
     filename,
     filetag,
     filesize,
     filedate
   FROM table2;

   Since you are not selecting the table2.ID field, IDs will be generated for each row imported into table1.
   
   
2. Combine data from the two tables into a single resultset.
   Use a UNION ALL query or create a VIEW.
   I don't think this is what you mean though.

Hope that helps.

[lordadel]

Thanks for your response

But I have the 2 tables in 2 different databases so how do i do that?

sorry for my newbish questions but i am still learning


oh and another thing please how do i take a backup of a table of the database on the server not to download it just take it on the server..

Thanks again

Adel

[penagate]

You didn't say which of the two options you meant.

Assuming you are using MySQL: you can reference a table in another database simply by prepending the schema name.

INSERT INTO db1.tablename (...) SELECT (...) FROM db2.tablename

To back up a table (again, assuming MySQL) use mysqldump. If you don't have shell access, then you can make a rudimentary sort of backup using a tool like phpMySQL. If this isn't your server, ask your host how to do it.

[lordadel]

Thanks alot for your reply and help

Edit: by the way it gave me mysql error "Operand should contain 1 column"

I removed the brackets ( ) after SELECT and it worked fine

Just wanted to let you know

Thanks again

[lordadel]

Hey Sorry

I face another problem now i want to do that in php and the problem is that both databases have different usernames and passwords i tried to connect to both of them and it didn't work

would you please tell me how can i do that?

Thanks in advance
Cheers
Adel

[penagate]

You will need one MySQL user account that has permission to read from both databases.