How to transfer data from one database to another.

[KangaBytes]

Ok, I have two websites, both are on the same server, and, I want to transfer all data in the table on one, to a table of the same name on the other.

I want to transfer data in the table of one of those websites to the other.   What is the best way to do that?

I have tried INSERT INTO db2.$g  SELECT * FROM db1.$g

I get that error, SELECT permission is denied.

Some quick facts....
** I have WHM and cPanel
** Separate webhostings for each of these two websites
** Both are on the same server
** I use PHP 4.4, although I can get PHP 8 if that would be better

Now, I have done the following....
** I have set up another file, {twin-view}, to show information from the table of the same name, in each of the two websites.   Two HTML tables sit side by side on the resulting web page. This file works perfectly.
** I have used the same pair of mysql_connect statements in my transfer program as I did with the twin-viewer program.
** I have different user names for each of the two websites.  Would it be possible/handy for me to somehow add a common user to both websites, and, if so, how would I do that?
** I have set up all of the tables in, the second website. I just want to copy the data in the tables across.  The second website remains empty of data.
** The transfer and twin-view files are on the website 2, of which I aim to copy to.  Would it work better if I had this on the source-site?

The only way of which I can see for it to work right now is,  to do a mysql_query, then use a while loop to insert that data into database 2, just like I would be populating a HTML table for a web page.

I am happy to try

[kicken]

19 minutes ago, KangaBytes said:

** I use PHP 4.4, although I can get PHP 8 if that would be better

I hope that's a typo and you mean 7.4.  Either way, you need to upgrade, but if you really are on 4.4 then you need to upgrade like, yesterday.

 

20 minutes ago, KangaBytes said:

I have different user names for each of the two websites.  Would it be possible/handy for me to somehow add a common user to both websites, and, if so, how would I do that?

Having a user that can access both databases would allow you to use the INSERT query you tried.  If you're on shared hosting, you probably won't have the ability to do that.  If you're using your own server, then presumably you'd be able to use the root user to run the query or adjust the privileges.

Assuming this is a one time thing, or just once in a while thing, then the best option with separate users would be to access the command line if you can and use mysqldump command to dump the table from the first database, then use the mysql command load the generated sql file into the second database.

 

[KangaBytes]

I have tried everything.   I can not find the means to, give me a user, of which has global access to all databases, across all webhostings on this server.

I have tried, and, I do not have mysqldump as an option.

It is not my server, it is one I have hired.  I do have dedicated access to this whole server.

What should I put into the mysql_connect field?

How does Remote user fit into all of this?

I have tried numerous google searches on these matters, and fail to get any response.

[KangaBytes]

The one problem when I try and add a user, it requires a prefix.  I can not get around this prefix, and, when I request a list of users, I only get those with that prefix.

[kicken]

19 minutes ago, KangaBytes said:

I do have dedicated access to this whole server.

You should probably have SSH access to the server then.  Use that to access a shell and you should have access to the mysql and mysqldump tools, they are pretty standard.

[gizmola]

You really need to define what "I do have dedicated access to this whole server."

If this statement is in anyway true, there is no reason for this to be so hard.

Quote

 

The one problem when I try and add a user, it requires a prefix.  I can not get around this prefix, and, when I request a list of users, I only get those with that prefix.

 

I have no idea what you are talking about here, nor why this is a problem.  

Now, let's assume that you have 2 users you actually can connect to your server with and execute queries with a php program:

• db1user
• db2user

While this is a needlessly inefficient method, you can do what you are requiring by writing a php program that:
 

1. Makes 2 connections
   1. db1Connection
   2. db2Connection
2. Selects data from db1.$g and fetches it all into an array
3. foreach through the db1 result
   1. insert a db2.$g row

 

There are many potential issues we can't address because you didn't even begin to describe the actual table structure of the $g table is (and why are you using a $ in the table name?), or how you will figure out what data from db1 you need.  For example, if you have an auto increment primary key in the table(s), then you can't just take db1.$g.id = 300 and insert the whole row into db2.$g if for example, there is already a row with that id.  

 

What is the purpose of this exercise.  If people know what you are trying to accomplish and why, there might be some other solution to solving the actual problem.

 

[Andou]

I don't know if the OP will respond, but just in case: have you done a mysql dump or gotten the issue sorted out yet?