Combining Databases - Please help before I mess up!

nightfox

Nov 21 2005, 11:30 PM

I am intergrading this Private Messaging system into my website so that my members can login and access their PMs through the seprate PM software without loging in twice and registering twice since if I left it as is, that is what they would have to do... All I really need is 1 single table to be combined without damaging my few member's accounts (they are all friends, but I don't want to tell them that I was playing with the database and lost your account information...

)

Since I don't know much about SQL, and with my luck, if I tried this, SOMETHING would go wrong, can some one combine these two tables into ONE single table? thanks!!

This is from the Private Message System, take note that some rows (e.g. email, etc.) are the same in both! By the way, I copied this strait from the installdata.php file so I don't know if this is correct or not...

CODE

`id` int(10) unsigned NOT NULL auto_increment,
`user` text,
`pass` text,
`email` text,
`name` text,
`timeoffset` text NOT NULL,
`count` int(10) NOT NULL default '0',
`emailnot` text NOT NULL,
`nots` text NOT NULL,
`inmemlist` text NOT NULL,
`showmail` text NOT NULL,
`timestamp` int(10) NOT NULL default '0',
PRIMARY KEY (`id`)

This is my code, I removed user emails and MD5 encrypted passwords for security, I can easily replace them because I have a back-up for once the two are combined. The SQL below is what I'll use to restore the database once the two are combined, NAME below is the person's username, NAME (in the SQL) above is their REAL name and USER is their user name

CODE

--
-- Table structure for table `users`
--

CREATE TABLE `users1` (
`id` int(16) NOT NULL auto_increment,
`name` varchar(25) NOT NULL default '',
`pass` varchar(75) NOT NULL default '',
`active` int(1) NOT NULL default '0',
`activation` varchar(100) NOT NULL default '',
`icq` varchar(16) NOT NULL default '',
`aim` varchar(40) NOT NULL default '',
`yim` varchar(40) NOT NULL default '',
`msnm` varchar(75) NOT NULL default '',
`email` varchar(75) NOT NULL default '',
`sitename` varchar(45) NOT NULL default '',
`url` varchar(75) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=16;

--
-- Dumping data for table `users`
--

INSERT INTO `users1` VALUES (3, 'USERNAME', 'PASSWORD-MD5', 1, '495c13caedb92061204b3e56d5ce506e824', '', '', '', '', '.com', '', '');
INSERT INTO `users1` VALUES (10, 'USERNAME', 'PASSWORD-MD5', 1, '923bfeefc6559f58a568ee39fc9f41e2344', '', '', '', '', '.com', '', '');
INSERT INTO `users1` VALUES (6, 'USERNAME', 'PASSWORD-MD5', 1, 'b6604b6deb693b09050c68e3605f7326855', '', '', '', '', '.com', '', '');
INSERT INTO `users1` VALUES (13, 'USERNAME', 'PASSWORD-MD5', 1, '5f826043b92d3f202f5dd98e5bee6b68394', '', '', '', '', '.com', '', '');
INSERT INTO `users1` VALUES (8, 'USERNAME', 'PASSWORD-MD5', 1, '4db1533bd02f5b057a1d9adb6d5fbd35685', '', '', '', '', '.com', '', '');
INSERT INTO `users1` VALUES (15, 'USERNAME', 'PASSWORD-MD5', 1, 'ceade8756dd352bb836fe498ad360483945', '', '', '', '', '.com', '', '');
INSERT INTO `users1` VALUES (14, 'USERNAME', 'PASSWORD-MD5', 1, '652e7d6c4441cbfeed516787a000a103869', '', '', '', '', '.com', '', '');

Thanks a whole lot! I was sitting looking at the two SQL tables clueless on how to combine them! Thanks!!!

[N]F

Reply

Houdini

Nov 22 2005, 02:51 PM

That appears to be the users table from the PMsys which only has three tables all prefixed with pms_ so you have pms_log, pms_messages and pms_users the first code you displayed. Are both the databases in the same satabase or are they sperate, the only difference being the prefix, you could take your origional database and add the tables pms_... to it then you are not having to query different databases, so first make sure that you have that done, just do an export from the pms database andadd it to your then a set of queries can be set up depending on whether they are using the PM or the normal site.

I gave you that link for the PMsys in an earlier post and would like to help you get it working the way you want without a bunch of acrobatics with the database on your part.

Reply

nightfox

Nov 22 2005, 11:51 PM

QUOTE(Houdini @ Nov 22 2005, 10:51 AM)

That appears to be the users table from the PMsys which only has three tables all prefixed with pms_ so you have pms_log, pms_messages and pms_users the first code you displayed. Are both the databases in the same satabase or are they sperate, the only difference being the prefix, you could take your origional database and add the tables pms_... to it then you are not having to query different databases, so first make sure that you have that done, just do an export from the pms database andadd it to your then a set of queries can be set up depending on whether they are using the PM or the normal site.

I gave you that link for the PMsys in an earlier post and would like to help you get it working the way you want without a bunch of acrobatics with the database on your part.

The tables are in the same DATABASE, but the user TABLES need to be combined! I do not want my members to have to register and sign up twice as I think that is stupid for a module on the main site.

[N]F

Reply

Houdini

Nov 23 2005, 12:25 AM

I guess what I was trying to say is that the prefix in the case of the PMsys (consista of three tables) and your regular database it may or may not have a a prefix it might just be users, you would use a join either left join, inner join or outer join depending on what you are wanting from the database.

The dump in the second part of the code you have provided only lets me know that it was pulled from your database by whatever name but with no prfeix because that was the part of the overall database.

The second database has no prefix, but the first is a query to create the database but what is missing is the first part which would let you know what is different about the database.

Here is a link to help with joins in SQL http://www.plus2net.com/sql_tutorial/sql_inner_join.php
Since some of the code is missing then it would be hard to show a proper join and then how to use the data.

ADD REPLY / Got an Opinion!	a humble request :-)	RAPID SEARCH!	Free Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE