My name is Matthew Bredel and as of March, 2007, I am a full-time, work-at-home internet marketer.
For close to 10 years, I worked for a defense company which was an OK job, but I was so uninspired in life and frankly, I needed some more money. That is when I first discovered internet marketing! Now I admit that I didn't start making thousands in my first couple of months (in fact, I lost my shirt!), but I finally saw the "internet light"...
Now I don’t recommend doing this for your general database management. All of your tables should remain on one database per website. But sometimes there are reasons to move data from one database to another. In my case, I was recently confronted with this when I wanted to moved my old, custom MySQL database into a new database (in this case, a Wordpress MySQL database).
Years ago (before Wordpress…yes, there was a time) I created TheWebReviewer with its own article database. It is now 2013 and Wordpress is IT. Of course, I didn’t feel like manually copying over about 400 articles (with their tags, categories, titles, excerpts, etc.) so I needed to copy (and kind of manipulate) data from one database to another.
I’m not going to go into the Wordpress article tables at this time (maybe next post???), but I do want to show you how to copy from one database table to another:
FIRST, we need to create the database connection for both database:
if ($dbc_old = mysql_connect (’HOST1′, ‘USERNAME1′, ‘PASSWORD1′)) { mysql_select_db (’DATABASE1′); }
if ($dbc_new = mysql_connect (’HOST2′, ‘USERNAME2′, ‘PASSWORD2′)) { mysql_select_db (’DATABASE2′); }
HERE, we define the database variables differently. Usually these database variables are created and ignored. Now, we will use these variables to distinguish between the two databases.
* NOTE: Be careful here! We typically define our database variables with Global variables OR we define the variable connection in another file which we reference with an include() function. I would avoid both here (it will only confuse you). Remember, this should be a temporary operation and once it is complete, you should disable the file!
AT this point, MySQL query is the same as before:
$query_cat = “SELECT title, body, description, id FROM articles”;
BUT now, we will add a second variable to the mysql_query (in this case, $dbc_old):
$result_cat = @mysql_query ($query_cat,$dbc_old);
Typically, this parameter is ignored and defaults to the one that is defined. Now, we can use it to distinguish between database #1 and database #2.
Pretty simple and straightforward stuff! The queries are all the same (you can SELECT, UPDATE, DELETE, CREATE, etc.). But like I mentioned, I would avoid more than one database for normal processing and use this only when you need to.
cheers…matt
