xoops forums

tedsmith

Home away from home
Posted on: 2005/11/15 19:09
tedsmith
tedsmith (Show more)
Home away from home
Posts: 1151
Since: 2004/6/2 1
#1

SQL Syntax for this...

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

Peekay

XOOPS is my life!
Posted on: 2005/11/15 20:38
Peekay
Peekay (Show more)
XOOPS is my life!
Posts: 2335
Since: 2004/11/20
#2

Re: SQL Syntax for this...

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.
Posted on: 2005/11/15 21:09
smdcom
smdcom (Show more)
Posts: 901
Since: 2004/4/27
#3

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
Posted on: 2005/11/15 22:43
ackbarr
ackbarr (Show more)
Posts: 1449
Since: 2002/10/2
#4

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

Peekay

XOOPS is my life!
Posted on: 2005/11/15 22:57
Peekay
Peekay (Show more)
XOOPS is my life!
Posts: 2335
Since: 2004/11/20
#5

Re: SQL Syntax for this...

That's a valuable resource. Many thx ackbarr.
A thread is for life. Not just for Christmas.

tedsmith

Home away from home
Posted on: 2005/11/17 18:41
tedsmith
tedsmith (Show more)
Home away from home
Posts: 1151
Since: 2004/6/2 1
#6

Re: SQL Syntax for this...

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

tedsmith

Home away from home
Posted on: 2005/11/17 19:18
tedsmith
tedsmith (Show more)
Home away from home
Posts: 1151
Since: 2004/6/2 1
#7

Re: SQL Syntax for this...

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!