XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. Q and A 
  4.  / Need Help with MySQL syntax to merge two xoops_user tables
Register
1
brash
Need Help with MySQL syntax to merge two xoops_user tables

  • 2004/12/10 12:54

  • brash

  • Friend of XOOPS

  • Posts: 2206

  • Since: 2003/4/10


Hi All,

I'm really needing some help getting a MySQL statement to work so that I can merge the xoops_user tables from two different websites. As we are dealing with user data, the uid, uname and email fields MUST be unique. If duplicates are found in the database being imported, then those records should be dropped.

Jmass has be very kind in coming up with the following code, but it is not working for me as I get a "Commands out of sync; You can't run this command now" error when I try to execute it.

(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 first_database.xoops_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 second_database.xoops_users)


I've tried looking up the MySQL syntax for UNION, but it just isn't making sense at the moment. I'm hoping a MySQL guru can spot the problem and correct it for me. Thanks.
2
christian
Re: Need Help with MySQL syntax to merge two xoops_user tables

  • 2004/12/10 14:44

  • christian

  • Just can't stay away

  • Posts: 401

  • Since: 2002/2/24


This query is ok for me without modification (copy/paste), but the tables are in the same database.
3
brash
Re: Need Help with MySQL syntax to merge two xoops_user tables

  • 2004/12/10 23:27

  • brash

  • Friend of XOOPS

  • Posts: 2206

  • Since: 2003/4/10


Really ? I can't seem to get it to work at all . The only thing I am changing from the sytax above is to actually change the first_database and second_database names to represent the real database names.

The tables I am trying to merge are in seperate databases, but I even tried renaming one table and placing both in the one database. Still no luck . I've checked permissions just incase, but I just can't see why the data is not being merged. I think my brain has gone on strike
4
brash
Re: Need Help with MySQL syntax to merge two xoops_user tables

  • 2004/12/12 0:04

  • brash

  • Friend of XOOPS

  • Posts: 2206

  • Since: 2003/4/10


anyone?
5
poiinthepark
Re: Need Help with MySQL syntax to merge two xoops_user tables


//XOOPS DATABASE SETTINGS

$dbServer='localhost';
$dbUser='YOUR_DBUSERNAME';
$dbPass='YOURDBPASSWORD';

//YOUR 2ND XOOPS DATABASE SETTINGS

$fromDB="2ND_XOOPSDBNAME"; // 
$fromPrefix="2ND_XOOPSTABLEPREFIX"; // 

//YOUR XOOPS DATABASE SETTINGS

$toDB="YOUR_XOOPSDBNAME"; // XOOPS DATABASE NAME
$toPrefix="YOUR_XOOPSTDBTABLEPREFIX"; // XOOPS PREFIX

//* FUNCTIONS TO CONVERT DATABASE TABLES */

BuildUserTable();


echo "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>conversion scripttitle>head><body>";

echo "<font color=blue><b> ** Converting Database **<br>";

$link = mysql_connect($dbServer, $dbUser, $dbPass) or die("Could not connect, check your DB settings");
echo "** Connected successfully **font>b><p>";


//Function to delete records from the DB
function DeleteRecords($tbname) {
    $sql = "DELETE FROM ".$tbname;
    $result = mysql_query($sql);
    if (!$result) echo(" - delete from $tbname didn't work
 -  Broken SQL = $sql
");
}


//Perform table actions
function DoTable($delete, $sql, $tbname) {
echo "Moving ".$tbname." table
";
if ($delete) DeleteRecords($tbname);
    $result1 = mysql_query($sql);
    if (!$result1)    
        echo(" - $tbname incomplete
 -  Broken SQL = $sql
");
}


// Do the users table - exclude any user that would overwrite XOOPS admin's
function BuildUserTable()  {
global $toPrefix, $fromPrefix, $fromDB, $toDB;
$dsql = "DELETE FROM ".$toDB.".".$toPrefix."_users WHERE uid>1";
$sql = "INSERT INTO ".$toDB.".".$toPrefix."_users (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 2ND_XOOPSDBTABLE_USERS)
        (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 YOUR_XOOPSDBTABLE_USERS)
        FROM ".$fromDB.".".$fromPrefix."_users WHERE ".$fromDB.".".$fromPrefix."_users.user_id > '1'";
$delete=False;
$tbname=$toDB.".".$toPrefix."_users";
DoTable($delete, $dsql, $tbname);
DoTable($delete, $sql, $tbname);
$date_array = array('Jan' => '01',
                    'Feb' => '02',
                    'Mar' => '03',
                    'Apr' => '04',
                    'May' => '05',
                    'Jun' => '06',
                    'Jul' => '07',
                    'Aug' => '08',
                    'Sep' => '09',
                    'Oct' => '10',
                    'Nov' => '11',
                    'Dec' => '12');
$msql = "SELECT user_id,user_regdate FROM ".$fromDB.".".$fromPrefix."_users WHERE user_id>1";
$result = mysql_query($msql);
if(!$result)
    echo(" - Update Regdate failed! ");
while(list($uid,$regdate)=mysql_fetch_row($result)){
    $regdate = str_replace(',','',$regdate);
    $var = explode(' ',$regdate);
    $newdate = mktime(0,0,0,$date_array[$var[0]],$var[1],$var[2]);
    $rsql = "UPDATE ".$toDB.".".$toPrefix."_users SET user_regdate='$newdate' WHERE uid='$uid'";
    $result1 = mysql_query($rsql);
    if(!$result1)
        echo(" - Update Regdate failed! ");
}

//=========================================

echo "

** Disconnected **
";
echo "If you see any errors on this page, I recommend that you 
adjust whatever is causing the errors and rerun this script. One thing that you REALLY need to think 
about is setting the "buildusertable" to "0" so that you won't over write the XOOPS admin stuff. Use 
the table conversion at your own risk!!!";

mysql_close($link);                
?>



That should work but i dont have a test serve to test it right now.

Obviously you will change your database settings through out the script that i have made in CPAITALS.

This is just a modified "bd_csmc" nuke to XOOPS SQL migration script.

Your will see i have modified it to migrate one user table from one XOOPS DB to another user table from your other XOOPS DB.
Im not entirely sure what you are doing, if it is just to migrate one user table to another then this should work but dont do it on a live site... i dont want to be held accountable for that one!.
The orgional script i had to modify to port my users table so i know that it worked for me, but with out testing this script, i dont know!.. i looks ok! as far as the code goes.

I hope that will help you or atleast give some one with way more knowledge than i have a better chance at getting you your users.
6
brash
Re: Need Help with MySQL syntax to merge two xoops_user tables

  • 2004/12/12 4:00

  • brash

  • Friend of XOOPS

  • Posts: 2206

  • Since: 2003/4/10


Thanks for the reply John,

I'm actually wanting to merge two xoops_user tables. The script you posted looks to be more for a migration where no data has to be merged. I assume this as it looks as if it deletes all user accounts on the target database prior to pulling any data across.
7
intel352
Re: Need Help with MySQL syntax to merge two xoops_user tables

  • 2004/12/12 7:55

  • intel352

  • Module Developer

  • Posts: 824

  • Since: 2003/11/23


sorry brash, i forgot about this
imma hop right onto it.

and yes, the modified code that ptp posted is not what you need, it's geared for replacing the existing user table with data from another user table. there's also some needless code in there (regarding the date field and whatnot, phpnuke had used string method to store the date, i had to convert that to xoops' int, but xoops->to->xoops doesn't require that conversion)

anywho, i'll post something in a whee bit.

EDIT: and for christian's query, you haveta make sure your MySQL database version is 4.0+
8
brash
Re: Need Help with MySQL syntax to merge two xoops_user tables

  • 2004/12/12 7:58

  • brash

  • Friend of XOOPS

  • Posts: 2206

  • Since: 2003/4/10


Cool, thanks bd_csmc
9
intel352
Re: Need Help with MySQL syntax to merge two xoops_user tables

  • 2004/12/12 9:18

  • intel352

  • Module Developer

  • Posts: 824

  • Since: 2003/11/23


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`;
10
brash
Re: Need Help with MySQL syntax to merge two xoops_user tables

  • 2004/12/12 9:59

  • brash

  • Friend of XOOPS

  • Posts: 2206

  • Since: 2003/4/10


thanks bd_cmsc

After I change the database names and try and run it I get this error;

#1103 - Incorrect table name 'ithq.ithq2_users'

Any ideas?
(1) 2 3 »

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

341 user(s) are online (282 user(s) are browsing Support Forums)

Members: 0

Guests: 341

more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits