merging multiple databases in 1 issue

[mdshare]

Ok I have a coding issue and don't feel to write a php function for it as I think it should be possible under SQL

Data:

db1 , db2, db3

2 databases that have to merge into a 3rd database

So using the UNION command

 

CREATE TABLE /*! IF NOT EXISTS */ db3 SELECT * FROM db1 UNION SELECT * FROM db2;

 

result: works but.... double records

next step....

Using the SELECT DISTINCT

 

CREATE TABLE /*! IF NOT EXISTS */ db3 SELECT DISTINCT field1,field2,field3,field4, field5 FROM db1 UNION SELECT DISTINCT field1,field2,field3,field4, FROM db2;

result: perfect... allmost

it removes identicall records but does not add the auto incriment of field_ID

any help would be apreciated

problem case:

1. merging 2 similar databases in 1

2. deleting identicall records

3. auto increment has to follow up correctly

in php point 3 can be done but I want it done in SQL

thx

md

[mdshare]

Re: merging multiple databases in 1 issue

point 3 in PHP

fetching highest ID

 

function highest_id ($idname,$dbname,$name)
{
$query="SELECT MAX(`ID_{$idname}`) FROM {$db_prefix}{$dbname}";

$result=mysql_query($query) or
       die( mysql_error() );

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
       {
       while( list ($key, $value) = each($row) )
               {
               echo "Number of $name : $value 
";
       $number = "number";
       $temp = "$dbname$number";
           $$temp = $value;
       return $$temp;
               }
       }
}

 

$membersnumber = highest_id ("MEMBER","members","members");

 

Changing the auto increment ID

 

$membersarray = array();

$query="SELECT ID_MEMBER FROM {$import_prefix}members";

$result=mysql_query($query) or
       die( mysql_error() );

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
while( list ($key, $value) = each($row) )
{
   $membersarray[] = $value + $membersnumber;
   echo "Member $value adjusted
";
}
}

 

function insertrows ($dbname,
{
$query="INSERT INTO {$db_prefix}{$dbname}";

$result=mysql_query($query) or
       die( mysql_error() );

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

 

so a lot of work and only partially solving the problem, so I think there must be a simple way in SQL to do this

[stryker]

Re: merging multiple databases in 1 issue

i would use the mysql command UNION

[mdshare]

Re: merging multiple databases in 1 issue

doesn't help with what I want to do

[stryker]

Re: merging multiple databases in 1 issue

i have been searching for days, i can't find how to do it all in mysql but i can do it easily in php

[monbster]

Re: merging multiple databases in 1 issue

are you trying to combine two databases, or two tables?

i wanted to migrate some db's so i used PHPAdmin to export to file (as SQL statements), then import into the other database.

[Guest Anonymous]

Re: merging multiple databases in 1 issue

BUMP

We know the solution to this ... but we are opening this one up as a little test of your SQL skills.

Have fun... ;)

[Floydian]

Re: merging multiple databases in 1 issue

As far as I know, a select requires a table to be selected from. I do not think you can simply select a database entirely

With that said, you would of course have to have a user setup that has access to each database, and has the coresponding priviliges needed for the commands used in the sql query.

database.table is the proper syntax for selecting a table from a specific database.

The tables in two databases can be joined, unioned and all that. So, besides the fact that you need to declare what database you are selecting from, there isn't any thing else involved here that isn't involved in doing the same thing from just one database.

[Nickson]

Re: merging multiple databases in 1 issue

Some of you are really thinking to far about this one. I have asked Nyna if my solution was the one they were looking for and it was.

it's not huge, it's not that complex ... think of the basics!

PS: not posting answer, i'll let you all have to fun :P

[Floydian]

Re: merging multiple databases in 1 issue

 

Some of you are really thinking to far about this one.

(SNIPPED)

As far as I can tell, I'm the only one that posted in this forum after Nyna bumped this thread, but perhaps you are assuming that people are reading this and not posting, but thinking a lot about it instead. lol

I still don't see how you do a select database1, database2 without having a table listed in there. ;)

[Guest Anonymous]

Re: merging multiple databases in 1 issue

Who needs to select from anywhere Floydian?

SELECT "Nyna";

Works nicely ;)

[Nickson]

Re: merging multiple databases in 1 issue

Well i actually read it later again, thought about it but never posted my solution

today, nyna asked another question in the irc chat (see other topic) and i thought about this one again :P

it's actually a very old topic xD, the bump brought it back to live, but when md originally created this topic, i didn't knew the answer, now my knowledge is a bit larger on this subject ;)

like i said, keep yourself to the basics and throw in a bit, just look at a bit bigger as you would normally do xD

[Floydian]

Re: merging multiple databases in 1 issue

I didn't say it, mdshares original post is talking about selecting "FROM db1".

I don't see how you can select an entire database that way. Or maybe you can can, who knows. The only way I'd know is to test it out for myself, but seeing as how I don't have a need to do something like that, I'll save it for later when I do, unless of course someone wants to just say, "hey, you can select an entire database" lol. Until then, I'm assuming that before I do such a thing, testing will be required to ensure that it works, if it can.