Welcome Guest ( Log In | Register )



 
 Reply to this topicStart new topic
> Combining Databases, Please help before I mess up!
nightfox
post Nov 21 2005, 11:30 PM
Post #1


NiGHTFoX - Hiding in the dark
Group Icon

Group: Members
Posts: 680
Joined: 3-April 05
Member No.: 3,584
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... rolleyes.gif )

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
Go to the top of the page
 
+Quote Post
Houdini
post Nov 22 2005, 02:51 PM
Post #2


Super Member
Group Icon

Group: Members
Posts: 572
Joined: 25-April 05
From: Nashville Tennessee
Member No.: 4,340
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.
Go to the top of the page
 
+Quote Post
nightfox
post Nov 22 2005, 11:51 PM
Post #3


NiGHTFoX - Hiding in the dark
Group Icon

Group: Members
Posts: 680
Joined: 3-April 05
Member No.: 3,584
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
Go to the top of the page
 
+Quote Post
Houdini
post Nov 23 2005, 12:25 AM
Post #4


Super Member
Group Icon

Group: Members
Posts: 572
Joined: 25-April 05
From: Nashville Tennessee
Member No.: 4,340
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.
Go to the top of the page
 
+Quote Post
nightfox
post Nov 26 2005, 02:28 AM
Post #5


NiGHTFoX - Hiding in the dark
Group Icon

Group: Members
Posts: 680
Joined: 3-April 05
Member No.: 3,584
Thanks, I'll take a look at it when I have time.

[N]F
Go to the top of the page
 
+Quote Post
« Next Oldest · Databases · Next Newest »
 

Reply to this topicStart new topic

Collapse

> Similar Topics

Topics Topics
  1. The Best Database(41)
  2. Need Advice On Creating Online Music Database(6)
  3. Windows XP: System Files & Hard Drive Mess-up(5)
  4. Mess With Messenger(12)
  5. Hosting A Server On Database(6)
  6. Remote Databases(4)
  7. phpBB And Mambo Login(2)
  8. Migrating Databases(2)
  9. Creating Databases(4)
  10. Unexpected Database Crashes(0)
  11. I Lost Mysql Databases And Privileges!?(10)
  12. Where Have My Databases Gone(7)
  13. Did I Mess Up My Computer?(6)
  14. CMS102 - Content Management System Design(9)
  15. Updating A Database's Tables(10)
  1. What Is A Database How Do I Use One?(7)
  2. Accessing And Retrieving Data From A Databases.(2)
  3. How To Setup SMF Databases?(4)
  4. Copy Databases In phpMyAdmin(3)
  5. Need Help Programming With Databases (VB.NET)(9)
  6. Can You Make Your Own MySQL Databases?(7)
  7. Ms Databases(6)
  8. (help With Sql And Php)retrive Datas From Realted Tables And Display Them!(4)
  9. How Do You Make Large Databases?(14)
  10. Examining Databases At A Whole Glance(7)
  11. All About Databases(3)
  12. Free Graphical Tools For Databases(7)
  13. Databases(2)


 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

- Lo-Fi Version Time is now: 7th October 2008 - 01:51 AM
Powered By IP.Board 2.2.1 © 2008  IPS, Inc.
Licensed to: Xisto Corporation