0


I want to do something which may sound wierd.I have a database for my main application which holds few html templates created using my application.These templates are stored in a traditional RDBMS style.A table for template details and other for page details of the template.
I have a similar application for different purpose on another domain.It has a different database with the same structure as the main app.I want to move the templates from one database to the other,with all columns intact.I cannot export as both have independent content of there own i.e same in structure and differ in content. 1st is the template table and 2nd is the page table

   +----+----------+----------+
   | id |templatename     |
   +----+----------+----------+|
   |  1 | File A   |           |
   |  2 | File B   |           |
   |  3 | File C   |
   |  4 | File 123 |
   | .. | .......  | ........  |
   +----+----------+----------+

  +----+----------+----------+
  | id | page_name| template_id|(foreign key from above table)
  +----+----------+----------+
  |  1 |   index  | 1          |
  |  2 |   about  | 1          |
  |  3 |   contact| 2          |
  |  4 |          |            |
  | .. | ........ | ........   |
  +----+----------+------------+

I want to select records from 1st database and insert them to the other.Both are on differnet domains.
I thought of writing a PHP script which will use two DB connections,one to select and the other for insert to the other DB,but I want to know if I can achieve this in any other efficient way using command line or export feature in any way

EDIT: for better understanding
I have two databases A and B both n diff servers.Both have two tables say tbl_site and tbl_pages.Now both are independently updated on their domains via application interface.I have a few templates created in database A stored in tbl_site and tbl_pages as mentioned in the question above.I want the template records to be moved to the database B

Improve this question
2
  • I'm struggling a little to see what you'e doing, but are you trying to say "Move 'template_name' in table 1 & add it to table 2"? Some more details would be great! Also, you might need to use curl if they're on different domains. I ran into an issue a few days ago where my code, much to my suprise, couldn't access a database built on another domain despite having the ip & database credentials. Commented Mar 20, 2014 at 12:43
  • I have two databases A and B both n diff servers.Both have two tables say tbl_site and tbl_pages.Now both are independently updated on their domains via application interface.I have a few templates created in database A stored in tbl_site and tbl_pages as mentioned in the question above.I want the template records to be moved to the database B. Commented Mar 20, 2014 at 15:13

4 Answers 4

Reset to default
1

You can do this in phpMyAdmin (and other query tools, but you mention PHP so I assume phpAdmin is available for you).

On the first database run a query to select the records that you want to copy to the second server. In the "Query results operations" section of the results screen, choose "Export" and select "SQL" as the format.

This will produce a text file containing SQL INSERT statements with the records from the first database.

Then connect to the second database and run the INSERT statements from the generated file.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Yes but this way my ids wont stya intact.Once I insert the records from template table to the target DB,they will have new template id's which will be the foreigh\n key in the second table.Now if I insert the second table it will have the template_is's of the source database and not that of the target.
You will have to import table 1 first then see what the new ids are then modify the inserts for table 2
I believe then writing a script would be a better option as I will need it in future too! Thank you for the response.I will post the script once I am done!
1

As other mentioned you can use phpmyadmin, but if your second database table fields are different, then you can write down a small php script to do that for you. Please follow the following steps.

Note : Consider two databases A and B, and you want to move some data from A to B and both are on different servers.

1) First allow remote access on database A server for the database A. Also get a host, username and password for database A.

2) Now using mysqli_ extension, connect to that database. As you have the host for the other database A server, so you have to use that, not localhost. On most servers, the host is the IP of the other remote server.

3) Query database table and get your results. After you get results, close the database connection.

4) Connect to database B. Please note that in this case, database B host may be localhost. Check your server settings for that.

5) Process the data you got from database A and insert them to database B table(s).

I use this same method to import data from different systems (Drupal to Prestahop, Joomla to a customized system), and it works fine.

I hope this will help

Improve this answer

Comments

1

Export just data of db A (to .sql). Or use php script - can then be automated if you need to do it again

Result:

INSERT table_A values(1, 'File A')

....

INSERT table_B values(1, 'index', 1)

....

Be careful now when importing data - if you have ids the same you will get error (keep this in mind). Make any mods to the script to solve these problems (remember if you change an id for table_A you will have to change the foreign key in table_B). Again this is a process which you might be forced to automate.

Run the insert scripts in db B

Improve this answer

Comments

0

As my question was a bit different I preffered answering it.Also the above comments are relevant in different scenarios so,I won't say they are totally wrong.

I had to run a script to make the inserts happen based on new ids to the target database. To make it a bit easy and avoid cross domain request to database,I took a dump of the first database and restored it to the target.
Now I wrote a script to select records from one database and insert them to the other i.e the target.So the ids were taken care of automatically.Only the problem(not a problem actually) was I had to run the script for each record independently.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.