Copy tables from different Databases

@k0r54Nov 20.2004

Hi, ok moving away from my previous thread

How do i copy tableA from databaseA into databaseB where the username and password for the two databases are different.

to post a comment

27 Comments(s) _↴

@ShrineDesignsNov 20.2004 — #try this:

[code=php]<?php
 $link1 = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database', $link1);
 $link2 = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database', $link2);
 
 $result = mysql_query("SELECT * FROM table_name", $link1);
 
 $fields = "";
 $values = "";
 
 while($row = mysql_fetch_array($result, MYSQL_ASSOC))
 {
 reset($row);
 $sep = "";
 
  while(list($field, $value) = each($row))
  {
   $fields .= $sep . "" . $field . "";
   $values .= $sep . "'" . $value . "'";
   $sep = ",";
  }
  mysql_query("INSERT INTO table_name ($fields) VALUES ($values)", $link2);
 }
 mysql_free_result($result);
 mysql_close($link1);
 mysql_close($link2);
 ?>[/code]

@k0r54authorNov 20.2004 — #That works! ?

but my first question is how do i take them from certain fields and then put them into certain fields on the database

AND

it only seams to be copying the first row accross and not all of them???

why is that?

@ShrineDesignsNov 20.2004 — #oops, try this:

[code=php]<?php
 $link1 = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database', $link1);
 $link2 = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database', $link2);
 
 $result = mysql_query("SELECT * FROM table_name", $link1);
 
 while($row = mysql_fetch_array($result, MYSQL_ASSOC))
 {
 reset($row);
 $sep = "";
 $fields = "";
 $values = "";
 
  while(list($field, $value) = each($row))
  {
   $fields .= $sep . "" . $field . "";
   $values .= $sep . "'" . $value . "'";
   $sep = ",";
  }
  mysql_query("INSERT INTO table_name ($fields) VALUES ($values)", $link2);
 }
 mysql_free_result($result);
 mysql_close($link1);
 mysql_close($link2);
 ?>[/code]

@k0r54authorNov 20.2004 — #YOU ARE A STAR ? thank you so much!!!!

I know this is asking a lot but how would you suggest i do this

[code=php]
 $link = mysql_connect('localhost','apc00001update','xxx') or die(mysql_error()); 
 $link2 = mysql_connect('localhost', 'apc00001main', 'xxx') or die(mysql_error()); 
 
 $insertringtones = "INSERT INTO apc00001main.ringtones(Code, Artist, Title, Category, Example, New) SELECT ringtones.ID, Artist, TrackName, new_cat, PreviewPath, DateAdded FROM ringtones";
 $insertringtones_res = mysql_query($insertringtones) or die(mysql_error());
 [/code]

I tried doin it in one query, but i dont think it is possible when im dealing with two databases and different permisions using the code above how would you do it. Im messin about with it but if you could give me some advice that would be brill.

Thanks you i have been working all day and last night on this im so tired lol

?

@k0r54authorNov 20.2004 — #by the way the SELECT bit (ringtones.ID) is from apc00001update database where the INSERT bit is from apc00001main they both have different permisions

Thanks

Adam

@ShrineDesignsNov 20.2004 — #no problem

lemme see if i can do just that on my server

@k0r54authorNov 20.2004 — #Yes i have spent like 15 hrs trying to learn something that can't be done lol

?

so do you have any idea's how i can do the same thing ?

Thanks

adam

@ShrineDesignsNov 20.2004 — #see if this works, you will need to use a WHERE clause in the sub-select:

[code=php]<?php
 $link1 = mysql_connect('localhost', 'apc00001update', 'xxx') or die(mysql_error());
 $link2 = mysql_connect('localhost', 'apc00001main', 'xxx') or die(mysql_error());
 
 $rt_sql = "INSERT INTO apc00001main.ringtones (Code, Artist, Title, Category, Example, New) VALUES ((SELECT ID, Artist, TrackName, new_cat, PreviewPath, DateAdded FROM apc00001update.ringtones))";
 $rt_result = @mysql_query($rt_sql);
 
 if($rt_result === false)
 {
 echo mysql_error();
 }
 echo $rt_result;
 ?>[/code]

@k0r54authorNov 20.2004 — #Hi, i get this error

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ID, Artist, TrackName, new_cat, PreviewPath, `Da

?????

@ShrineDesignsNov 20.2004 — #older versions of MySQL don't support sub-selects

@k0r54authorNov 20.2004 — #what do you suggest i do ?

im not in control of the version ?

Thanks

Adam

@ShrineDesignsNov 20.2004 — #try modifying my previous script to *suit* your needs

yell at your host, tell them to upgade, lol

@ShrineDesignsNov 20.2004 — #

[code=php]<?php
 $link1 = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database', $link1);
 
 $link2 = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database', $link2);
 
 $result = mysql_query("SELECT ID, Artist, TrackName, new_cat, PreviewPath, DateAdded FROM table_name", $link1);
 
 while($row = mysql_fetch_array($result, MYSQL_NUM))
 {
 reset($row);
 $sep = "";
 $values = "";
 
  while(list($value) = each($row))
  {
   $values .= $sep . "'" . $value . "'";
   $sep = ",";
  }
  mysql_query("INSERT INTO table_name (Code, Artist, Title, Category, Example, New) VALUES ($values)", $link2);
 }
 mysql_free_result($result);
 mysql_close($link1);
 mysql_close($link2);
 ?>[/code]

@k0r54authorNov 20.2004 — #Hi,,

Thanks so much for your time

I seam to be getting a very wierd error

in this order

(Code, Artist, Title, Category, Example, New)

in the database it is goin TOO i get 0,1,2,3,4 in the fields instead of what is in the previous database???

lol dont know why and i have email my host SHOUTING :p

Thanks again

Adam

@k0r54authorNov 20.2004 — #Hi,

BTW i am running version 4.0.18 of mysql, is that below what it should be for sub query

also any idea's why i get that funny error lol :p

Thanks

Adam

@ShrineDesignsNov 21.2004 — #you can do sub-selects with mysql 4, hmm... weird

try this:

[code=php]<?php
 $link1 = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database', $link1);
 
 $link2 = mysql_connect('localhost', 'username', 'password');
 mysql_select_db('database', $link2);
 
 $result = mysql_query("SELECT ID, Artist, TrackName, new_cat, PreviewPath, DateAdded FROM table_name", $link1);
 
 while($row = mysql_fetch_array($result, MYSQL_NUM))
 {
 reset($row);
 $sep = "";
 $values = "";
 
  while($value = each($row))
  {
   $values .= $sep . "'" . $value . "'";
   $sep = ",";
  }
  mysql_query("INSERT INTO table_name (Code, Artist, Title, Category, Example, New) VALUES ($values)", $link2);
 }
 mysql_free_result($result);
 mysql_close($link1);
 mysql_close($link2);
 ?> [/code]

@k0r54authorNov 21.2004 — #Hi,

Thanks

I have figured it out now ? thanks to your help i mixed your second post with my tables and where i want and it works brill ?

just as a note you would happen to know why my update query is slow

[code=php]
 $clickcountringtones = "UPDATE ringtones, click_count SET ringtones.click_count = click_count.Count WHERE click_count.Code = ringtones.Code AND click_count.DB = 'Ringtones'";
 $clickcountringtones_res = mysql_query($clickcountringtones, $conn1) or die(mysql_error());
 [/code]

There is about 10000 rows in the ringtones database and about 20000 in clickcount???

its taken a VERY long time

Thanks

Adam

@ShrineDesignsNov 21.2004 — #to speed up you MySQL queries a little bit add MYSQL_CLIENT_COOMPRESS (or 32) to the [i]client_flags[/i] parameter of mysql_connect()

example:

$link = mysql_connect('hostname', 'username', 'password', 0, MYSQL_CLIENT_COMPRESS);
 // persistant connection
 $link = mysql_pconnect('hostname', 'username', 'password', MYSQL_CLIENT_COMPRESS);

@k0r54authorNov 21.2004 — #ok thanks do i add them to my mysql_connect and put them on persistant??? what about the ,0,

also should that take like 2 min to execute?

@ShrineDesignsNov 21.2004 — #here is the parameters for mysql_connect()

mysql_connect(string hostname, string username, string password, bool new_link, int flags);

the new_link tells mysql to make a new connection if set to true

2 minutes ?!?!

you are only updating the ringtones table, right? try this:UPDATE ringtones SET ringtones.click_count = click_count.Count WHERE click_count.Code = ringtones.Code

@k0r54authorNov 21.2004 — #hi in phpmyadmin

it still took 108 seconds just under 2 min :s

P.S Thanks so much for your time and knowledge, much appreciate it!

@ShrineDesignsNov 21.2004 — #no problem

you might try adding more indexes or changing your existing indexes to help speed things up

@k0r54authorNov 21.2004 — #what do you mean exacly sorry ??

@ShrineDesignsNov 21.2004 — #the table indexes i.e.: PRIMARY, INDEX, etc.

@k0r54authorNov 21.2004 — #lol yeah sorry i know that but how will that help me speed it up ??

Sorry i oly being doing PHP/MYSQL for about 3-4 months

@ShrineDesignsNov 21.2004 — #read http://dev.mysql.com/doc/mysql/en/Optimizing_Database_Structure.html

@k0r54authorNov 21.2004 — #OK thanks for you help ?

i really appreciate it. ill do my homework now

Thanks again

Adam

Also in #PHP _↴

How to reset $_SERVER variable?Encoding Url parameters retrieve any number of images from website

Success!

Help @k0r54 spread the word by sharing this article on Twitter...

Tweet This

Copy tables from different Databases

27 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Copy tables from different Databases

27 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

27 Comments(s) _↴

Also in #PHP _↴