[RESOLVED] Unite Two Tables in Database PHP-SQL

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..


http://img138.imageshack.us/img138/7292/databaseif1.jpg

I hope that someone will help me

Thanks in advance

Cheers
Adel

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:
   
   Code:

   ---

   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.

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

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.

Code:

---

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.

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

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

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