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.  / SQL Syntax for this...
Register
1
tedsmith
SQL Syntax for this...

  • 2005/11/15 19:09

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


Hi

Using XOOPS 2.0.13.2, I want to change the e-mail address of all users where the address ends (for example) 'likethis.org.uk' to 'likethis.com'. So in other words, lets say I had 20 users who all had e-mail addresses like 'ted@likethis.org.uk' they would become 'ted@likethis.com'.

Obviously I've been able to use the 'Find USer' feature to list all of these users. But what would the SQL syntax be to change it using phpMyAdmin? Presumebly it would have to query and then alter the 'e-mail' column of the 'users' table. So, the syntax needs to say "Where e-mail ends likethis.org.uk replace with likethis.com". Can anyone tell me? I'm not SQL expert.

Thanks
2
Peekay
Re: SQL Syntax for this...

  • 2005/11/15 20:38

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


In addition to answering tedsmith's question, any chance of someone compiling a FAQ on useful SQL commands? I have often wondered how to find and change specific data in a XOOPS database using PHPMyAdmin.
A thread is for life. Not just for Christmas.
3
smdcom
Re: SQL Syntax for this...

SQL Command:

UPDATE xoops_users SET email = replace(email,'@likethis.org.uk','@likethis.com');


Change xoops_users ('xoops_' prefix) to your prefix (if needed).

-SMD
4
ackbarr
Re: SQL Syntax for this...

Peekay - the MySQL reference manual has a whole section for built-in functions... keep it handy:

http://dev.mysql.com/doc/refman/4.1/en/functions.html
Site Hosting - PlanetXoops
5
Peekay
Re: SQL Syntax for this...

  • 2005/11/15 22:57

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


That's a valuable resource. Many thx ackbarr.
A thread is for life. Not just for Christmas.
6
tedsmith
Re: SQL Syntax for this...

  • 2005/11/17 18:41

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


Hey, thanks for this. I just want to check something though, before I press 'Go'!!

I didn't explain myself fully earlier and forgot to mention that my reason for this is due to subdomains of a certain organisations setup. So, for example, the end of all their e-mail addressed has to be changed from ted@domain1.org.uk to ted@domain1.domain2.org.uk.

The domain1 bit (after the '@') needs to stay as it is, but then domain2 has to be inserted between it and the .org.uk bit.

So, to achive this, do I just have to do this :

UPDATE DBprefix_users SET email = replace(email,'.domain1.org.uk','.domain1.domain2.org.uk');


or will that result in their e-mail addresses just reading ted.domain1.doman2.org.uk with no '@'?

Thanks, and sorry for been a bit of an idiot!

Ted
7
tedsmith
Re: SQL Syntax for this...

  • 2005/11/17 19:18

  • tedsmith

  • Home away from home

  • Posts: 1151

  • Since: 2004/6/2 1


No worries - I risked it for a biscuit and just went for it (after backing up first of course!). Looking at it logically it had to work, and I also looked up the REPLACE function at the SQL site as suggested by ackbar. It worked a treat! Thank you so much smdcom and others. That's saved me quite some time!

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

469 user(s) are online (407 user(s) are browsing Support Forums)

Members: 0

Guests: 469

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