Want SQL Query to Fix Bad Usernames

[TheAllusionist]

I am bridging/integrating vBulletin with Joomla CMS and I have a lot of vBulletin users with names with spaces and special characters that Joomla doesn't accept. Is there some SQL Query verbiage or a mod that I can use to elliminate spaces and special characters? I know that I need to adjust vBulletin now to not allow them, I am asking about modifying existing ones.

Thanks in advance.

[Elite_360_]

I am bridging/integrating vBulletin with Joomla CMS and I have a lot of vBulletin users with names with spaces and special characters that Joomla doesn't accept. Is there some SQL Query verbiage or a mod that I can use to elliminate spaces and special characters? I know that I need to adjust vBulletin now to not allow them, I am asking about modifying existing ones.

Thanks in advance.

there a free mod at http://www.jfusion.org/ its a Component for joomla and can bridge vbulletin 3x or 4x, phpBB 3, SMF 1.1.x, MyBB 1.4, Magento, Moodle, DokuWiki, Gallery 2, osCommerce & Clones, eFront, Wordpress and a few others i think you can bridge 2 joomla site too it will bridge all of this together and you can set vbulletin as the master login or any other software that it supports.

so you will not have to worry about the special characters you can just set vbulletin as the master login

[TheAllusionist]

Thanks, that is a very good suggestion and I use jFusion, but because of the community components I use, I need Joomla as the Master. Hence I was looking for a way to fix usernames etc.. in vBulletin. Thanks for taking the time to offer the suggestion.

[Elite_360_]

Thanks, that is a very good suggestion and I use jFusion, but because of the community components I use, I need Joomla as the Master. Hence I was looking for a way to fix usernames etc.. in vBulletin. Thanks for taking the time to offer the suggestion.

Yeah Jfusion is the best and it's free
well ask over on jfusion there pretty good at giving support or did they tell you to come over here i can look at my site later and try joomla as the master and see if i can figure it out for you

[TheAllusionist]

I am a lurker here and ask after I have exhausted my search efforts. I may just live with it, I have 26,700 approx vBulletin members and around 26,400 Joomla, I can't get the last few in due to name conflicts and redundant emails, but I am close. Just like to have things 100%.

Thanks and yes, I use support over at jFusion, I actually paid one of them when I first set things up. My server died recently and I am rebuilding my sites from different backups as I was hit hard this year by hack attacks etc.

I will figure it all out eventually, thanks!

Russell

[Elite_360_]

what is your setting for Joomla Options ->User -> Username Filter ?

i have vbullletin set there and there no problem with special characters

--------------- Added 1384468172 at 1384468172 ---------------

I am a lurker here and ask after I have exhausted my search efforts. I may just live with it, I have 26,700 approx vBulletin members and around 26,400 Joomla, I can't get the last few in due to name conflicts and redundant emails, but I am close. Just like to have things 100%.

Thanks and yes, I use support over at jFusion, I actually paid one of them when I first set things up. My server died recently and I am rebuilding my sites from different backups as I was hit hard this year by hack attacks etc.

I will figure it all out eventually, thanks!

Russell

are you getting conflicts synchronizing

[TheAllusionist]

OK smarty pants, I think you are onto something, I am trying it now along with a plugin to allow multiple users with the same email address. I will let you know, thanks for your help.

rt

[Elite_360_]

OK smarty pants, I think you are onto something, I am trying it now along with a plugin to allow multiple users with the same email address. I will let you know, thanks for your help.

rt

Well if you have problems with synchronizing i would ask for help over on jfusion. Fanno usually answer the post and is most helpful it should not mater if your vbulletin users have special characters in there username i would not want to mess with my data base with a query if i had that many users i know if you change your username in joomla or vbulletin it will have problems synchronizing i don't usually synchronizing because when they login on the joomla side it creates a account for them

[TheAllusionist]

Yes, I am just dealing with a legacy site a dozen years old, and trying to get it all done to start with and move forward.

Thanks again!

[Elite_360_]

Yes, I am just dealing with a legacy site a dozen years old, and trying to get it all done to start with and move forward.

Thanks again!

O so you got Joomla! 1.0.x or 1.5.x as for a query i can't help you on that it should be a simple query that you could run in the back end of vbulletin i just don't know what to do for the query i will search in the coder's forums and see if anything there to strip characters from multiple username's

[TheAllusionist]

My site started out on Mambo and some of the programmers went off on their own and created their own branch of it and called it Joomla. It became more popular than the original and has evolved. I am now running Joomla 2.5 on all my sites.

I will figure it all out or accept where I get it, just thought there might be some simple queries I could run and thought I would ask.

Cheers!

[Elite_360_]

My site started out on Mambo and some of the programmers went off on their own and created their own branch of it and called it Joomla. It became more popular than the original and has evolved. I am now running Joomla 2.5 on all my sites.

I will figure it all out or accept where I get it, just thought there might be some simple queries I could run and thought I would ask.

Cheers!

yeah i know joomla is a better version of Mambo


ok i have a fix please do a backup of your database first

Old-Character/s = the special character/s you want to replace

New-Character/s = the new character/s you want to replace the olds one's

i try it on my forum and it work i edit like ten usernames and added a "$" and some with 3,4 or 5 "$" to the username i used code2 for that you got to change the dollar sign to the character you want to get rid of you can only do 1 character at a time and i was replacing it with nothing but you could replace the character with another character if you need to


go to Maintenance > Execute SQL Query
Don't select anything for Automatic Query add the code to Manual Query
code1

UPDATE " . TABLE_PREFIX . "user
SET username = REPLACE(username,'Old-Character/s','New-Character/s')


code2

UPDATE " . TABLE_PREFIX . "user
SET username = REPLACE(username,'$','')



after running the code and replacing all the characters you got to go to Maintenance > General Update Tools then run in this order
1. "Rebuild Thread Information"
2. "Rebuild Forum Information"
3. "Rebuild Search Index"
4. "Rebuild Post Cache"
5. "Update User Names"

not sure if you need to do 3 and 4 but do it anyway your user name is in other tables and this will rebuild them maybe do "Clear System Cache" and "Clear Auto-save Table " for 7 days too

[TheAllusionist]

I think it works, thanks "Brainiac"! ;) Have great weekend!