change the database names accordingly. the table names should already correspond with the info that you gave me.
what this set of queries does, is creates a new table (ithq2_users), sets the uname and email fields to unique, inserts data from your 2 live tables into this new table
then, because the drop index query wouldn't work for me (to remove the unique user/email indexes), the queries create a new table called ithqFinal_users, transfers all data into that table (from ithq2_users), and then deletes ithq2_users
so, your finished user table will be ithqFinal_users. whenever you're ready to go, delete your existing xoops_users table and replace it with ithqFinal_users.
also, these queries do not take into account your members & their groups. if you want that data transferred as well, lemme know.
either way, you WILL have to create a query to add all users to the members group, otherwise they will not be able to access your sites appropriately.
DROP TABLE IF EXISTS `csmapce_dev.ithq2_users`;
CREATE TABLE `csmapce_dev.ithq2_users` (
`uid` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(60) NOT NULL default '',
`uname` varchar(25) NOT NULL default '',
`email` varchar(60) NOT NULL default '',
`url` varchar(100) NOT NULL default '',
`user_avatar` varchar(30) NOT NULL default 'blank.gif',
`user_regdate` int(10) unsigned NOT NULL default '0',
`user_icq` varchar(15) NOT NULL default '',
`user_from` varchar(100) NOT NULL default '',
`user_sig` tinytext NOT NULL,
`user_viewemail` tinyint(1) unsigned NOT NULL default '0',
`actkey` varchar(8) NOT NULL default '',
`user_aim` varchar(18) NOT NULL default '',
`user_yim` varchar(25) NOT NULL default '',
`user_msnm` varchar(100) NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`posts` mediumint(8) unsigned NOT NULL default '0',
`attachsig` tinyint(1) unsigned NOT NULL default '0',
`rank` smallint(5) unsigned NOT NULL default '0',
`level` tinyint(3) unsigned NOT NULL default '1',
`theme` varchar(100) NOT NULL default '',
`timezone_offset` float(3,1) NOT NULL default '0.0',
`last_login` int(10) unsigned NOT NULL default '0',
`umode` varchar(10) NOT NULL default '',
`uorder` tinyint(1) unsigned NOT NULL default '0',
`notify_method` tinyint(1) NOT NULL default '1',
`notify_mode` tinyint(1) NOT NULL default '0',
`user_occ` varchar(100) NOT NULL default '',
`bio` tinytext NOT NULL,
`user_intrest` varchar(150) NOT NULL default '',
`user_mailok` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`uid`),
KEY `uname` (`uname`),
KEY `email` (`email`),
KEY `uiduname` (`uid`,`uname`),
KEY `unamepass` (`uname`,`pass`)
) TYPE=MyISAM;
ALTER TABLE `ithq2_users` ADD UNIQUE (`uname`);
ALTER TABLE `ithq2_users` ADD UNIQUE (`email`);
INSERT IGNORE INTO csmapce_dev.ithq2_users( name,
uname,
email,
url,
user_avatar,
user_regdate,
user_icq,
user_from,
user_sig,
user_viewemail,
actkey,
user_aim,
user_yim,
user_msnm,
pass,
posts,
attachsig,
rank,
LEVEL ,
theme,
timezone_offset,
last_login,
umode,
uorder,
notify_method,
notify_mode,
user_occ,
bio,
user_intrest,
user_mailok )
SELECT name, uname, email, url, user_avatar, user_regdate, user_icq, user_from, user_sig, user_viewemail, actkey, user_aim, user_yim, user_msnm, pass, posts, attachsig, rank,
LEVEL , theme, timezone_offset, last_login, umode, uorder, notify_method, notify_mode, user_occ, bio, user_intrest, user_mailok
FROM csmapce_dev.ithq_users
UNION (
SELECT name, uname, email, url, user_avatar, user_regdate, user_icq, user_from, user_sig, user_viewemail, actkey, user_aim, user_yim, user_msnm, pass, posts, attachsig, rank,
LEVEL , theme, timezone_offset, last_login, umode, uorder, notify_method, notify_mode, user_occ, bio, user_intrest, user_mailok
FROM csmapce_dev.demo_users
);
CREATE TABLE `csmapce_dev.ithqFinal_users` (
`uid` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(60) NOT NULL default '',
`uname` varchar(25) NOT NULL default '',
`email` varchar(60) NOT NULL default '',
`url` varchar(100) NOT NULL default '',
`user_avatar` varchar(30) NOT NULL default 'blank.gif',
`user_regdate` int(10) unsigned NOT NULL default '0',
`user_icq` varchar(15) NOT NULL default '',
`user_from` varchar(100) NOT NULL default '',
`user_sig` tinytext NOT NULL,
`user_viewemail` tinyint(1) unsigned NOT NULL default '0',
`actkey` varchar(8) NOT NULL default '',
`user_aim` varchar(18) NOT NULL default '',
`user_yim` varchar(25) NOT NULL default '',
`user_msnm` varchar(100) NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`posts` mediumint(8) unsigned NOT NULL default '0',
`attachsig` tinyint(1) unsigned NOT NULL default '0',
`rank` smallint(5) unsigned NOT NULL default '0',
`level` tinyint(3) unsigned NOT NULL default '1',
`theme` varchar(100) NOT NULL default '',
`timezone_offset` float(3,1) NOT NULL default '0.0',
`last_login` int(10) unsigned NOT NULL default '0',
`umode` varchar(10) NOT NULL default '',
`uorder` tinyint(1) unsigned NOT NULL default '0',
`notify_method` tinyint(1) NOT NULL default '1',
`notify_mode` tinyint(1) NOT NULL default '0',
`user_occ` varchar(100) NOT NULL default '',
`bio` tinytext NOT NULL,
`user_intrest` varchar(150) NOT NULL default '',
`user_mailok` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`uid`),
KEY `uname` (`uname`),
KEY `email` (`email`),
KEY `uiduname` (`uid`,`uname`),
KEY `unamepass` (`uname`,`pass`)
) TYPE=MyISAM;
INSERT IGNORE INTO csmapce_dev.ithqFinal_users( name,
uname,
email,
url,
user_avatar,
user_regdate,
user_icq,
user_from,
user_sig,
user_viewemail,
actkey,
user_aim,
user_yim,
user_msnm,
pass,
posts,
attachsig,
rank,
LEVEL ,
theme,
timezone_offset,
last_login,
umode,
uorder,
notify_method,
notify_mode,
user_occ,
bio,
user_intrest,
user_mailok )
SELECT name, uname, email, url, user_avatar, user_regdate, user_icq, user_from, user_sig, user_viewemail, actkey, user_aim, user_yim, user_msnm, pass, posts, attachsig, rank,
LEVEL , theme, timezone_offset, last_login, umode, uorder, notify_method, notify_mode, user_occ, bio, user_intrest, user_mailok
FROM csmapce_dev.ithq2_users;
DROP TABLE IF EXISTS `csmapce_dev.ithq2_users`;
