Page 1 of 1

change e-mails in db

Posted: Mon Oct 11, 2004 11:16 am
by ddragas
hi all

I've got db of users e-mail addresses. What I have to do is change part of privider name in these e-mail addresses.

Eg.

name. surname@pu.provider.hr

change to

name.surname@pu.t-com.hr

all that should be changed is name of provider, becouse provider has joined to t-com group, and all e-mail addresses have changed.

regards ddragas

Posted: Mon Oct 11, 2004 11:22 am
by jabbaonthedais
So the e-mail addresses in your database are like:
email1@pu.provider.hr
email2@pu.provider.hr

etc.?

Or do they already have a "something".email1@pu.provider.hr ?

Posted: Mon Oct 11, 2004 11:37 am
by ddragas
they already have a "something", and it is usualy name. It also could be just "something@pu.provider.hr".

Posted: Mon Oct 11, 2004 1:42 pm
by John Cartwright

Code: Select all

//assuming you are looping through the users

while ($row = mysql_fetch_assoc($result))
{

$email = $row['name'] . $row['surname'] .'@pu.t-com.hr';

//assuming you have an id, name and surname column

$sql = "UPDATE `users` SET `email`='$email' WHERE `id`='".$row['id']."'";

$update = mysql_query($sql) or die(mysql_error());

mysql_free_result($update);

}
As shown above, I would loop through each user gathering information about them and updating their row.

Posted: Mon Oct 11, 2004 3:34 pm
by ddragas
Thank you for quick reply.

This could work, but what with these e-mails where first part of email is not "name.surname". As I've metion it abowe - It also could be just "something@pu.provider.hr".

"something" can be anything.

can it be done somehow with %LIKE% in SQL

Posted: Mon Oct 11, 2004 3:38 pm
by John Cartwright

Code: Select all

<?php
//assuming you are looping through the users

while ($row = mysql_fetch_assoc($result))
{

$prevemail = explode('@',$row['email'];

if ($prevemail[1] == 'provider.hr')
{

$email = $row['name'] . $row['surname'] . '@pu.t-com.hr ';

//assuming you have an id, name and surname column

$sql = "UPDATE `users` SET `email`='$email' WHERE `id`='".$row['id']."'";

$update = mysql_query($sql) or die(mysql_error());

mysql_free_result($update);

}
}
?>

Posted: Mon Oct 11, 2004 3:44 pm
by ddragas
or to make function to determine first part of e-mail (infront of @) and last part of email (behind @), and if in last part of email are words ".provider.hr" update record with "first part of email" . @ . "changed last part of email with ".t-com.hr" "

Posted: Mon Oct 11, 2004 4:00 pm
by ddragas
one more thing

pu from '@pu.t-com.hr' also could be anything

Posted: Mon Oct 11, 2004 4:11 pm
by John Cartwright
So?

Posted: Tue Oct 12, 2004 4:44 am
by twigletmac
You could just use MySQL's REPLACE() function and LIKE:

Code: Select all

UPDATE table set email = REPLACE(email, 'provider.hr', 't-com.hr') WHERE email LIKE ='%provider.hr'
Mac