Jump to content
MakeWebGames

merging multiple databases in 1 issue


Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 1 month later...
  • 4 weeks later...
  • 9 months later...
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... ;)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. ;)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...