Link to home
Start Free TrialLog in
Avatar of NeoAshura
NeoAshuraFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Change Mysql Database then Send Mail to database users

Hi experts,

I was wondering how would i move all members in one table to a temp members table using php?

It needs to move all users from one table to another. the table they are in currently is called "member" and they need moving to "temp_member"

After this i plan on sending out a mass email with a confirmation code for them to reactivate their account. Which im guessing would need to consist of some while loop to pull every member (NOW IN THE THE temp_member) table and send them an email with the activation code to reactivate if you could help with that too that would be great.

Just to summarise need help with:
1. Moving "member" to "member_temp" - on mysql using php
2. Once moved emailing the members in "member_temp" with a new activation code. - I have the code for sending the mail just need help with the while loop. im guessing it would be something like:

$result = mysql_query("SELECT email FROM temp_member");
if($result)
while
($r = mysql_fetch_object($result)) {
$send _to = $result;
echo "User: $r=result";
mail ($send to = $result;
}
then my mail
$to = $email
$subject
etc etc etc

All help is appreciated like always thank you.



Avatar of Binkers
Binkers
Flag of United States of America image

Your example code for you loop is close but see below for an example of how to do it.
 
$result = mysql_query("SELECT email FROM temp_member");
if($result) {
	while($r = mysql_fetch_object($result)) {
		$send_to = $r->email;
		echo "User: $send_to";
                // create email message
                $subject = 'Subject Goes Here';
                $message = 'Message Goes Here';
                mail($send_to, $subject, $message);
	}
}

Open in new window


 Also, you can probably move your members into temp_members by running two SQL statements.  The first would be a insert into select from type statement and second would be a delete of the members table.
I thought I could answer this but was then so shocked at the syntax errors in your 'while loop' code I lost my train of thought. So if this comes out weird sorry.
1. How do you want to do the copy - have you created the temp table in which case just do
  INSERT INTO temp_member [(col_name,...)]
    SELECT [col_name,...] from member
2. almost right but you would need $result->fldname or $result['fldname'] on the rhs of those  '=' you might have a problem sending one email to 100's of recipients though. so do a mail for each recipient.

$subject = 'xxxxxxxx';
$body='xxxxxx xxxxx xxxxx';
$result = mysql_query("SELECT email FROM temp_member");
if($result)
while ($r = mysql_fetch_object($result)) {
    $to = $result['email'];
    // Whatever your email code needs to be e.g.
   mail($to,$subject,$body);
}


   
Avatar of NeoAshura

ASKER

The table is already created. where i want to move them to i just need to move them without them remaining in that table. for example

Move ALL fields from one table to another..

And leave the orgional table empty.. How would you do this?
@ Binkers thanks for the code, I will try it and combine it with the code to move my data from one table to another.
for example would this work to move it??

would this remove them completely from one and insert into the other? thats what im trying to achieve.. and was my first stab at it.
// Retrieve data from table where row that match this passkey 
$sql1="SELECT * FROM $tbl_name1 WHERE deleted ='no'";
$result1=mysql_query($sql1);

// If successfully queried 
if($result1){


$count=mysql_num_rows($result1);

 "temp_members_db"
if($count==1){

$rows=mysql_fetch_array($result1);
$myusername=$rows['username'];
$mypassword=$rows['password'];
$address=$rows['address']; 
$email=$rows['email'];
$phonenumber=$rows['phonenumber'];
$altphonenumber=$rows['altphonenumber'];

$tbl_name2="details";

// Insert data that retrieves from "temp_members_db" into table "registered_members" 
 $sql2 = "INSERT INTO $tbl_name2 (username,password,address,email,phonenumber,altphonenumber)
              VALUES('".$myusername."', '".$mypassword."', '".$address."', '".$email."', '".$phonenumber."', '".$altphonenumber."')";

$result2=mysql_query($sql2);
}

Open in new window

You can run the following two SQL command to do this and you won't need to deal with the data row by row in PHP which could take longer to run.

 
$sql = "INSERT INTO $tbl_name2 (username,password,address,email,phonenumber,altphonenumber)
              SELECT username,password,address,email,phonenumber,altphonenumber  FROM $tbl_name1 WHERE deleted ='no'";
if (!mysql_query($sql)) {
  echo mysql_error();
}

$sql = "DELETE FROM $tbl_name1 WHERE deleted ='no'";
if (!mysql_query($sql)) {
  echo mysql_error();
}

Open in new window

I have to deal with it row by row as i have data to insert that is not in the other table.

Here is what ive got so far by combineing them however i get errors...

failedUser: Resource id #6
Notice: Undefined variable: confirm_code in test.php on line 61

Warning: mail() expects parameter 1 to be string, resource given in test.php on line 64
Cannot send Confirmation link to your e-mail address

As you can see from line 19 i have delcared the confirm code variable...

I dont have a clue about the warning tho.
<?php
session_start();
include 'myphp.php';


// Retrieve data from table where row that match this passkey 
$sql1="SELECT * FROM details WHERE userdeleted ='no'";
$result1=mysql_query($sql1);

// If successfully queried 
if($result1){

// Count how many row has this passkey
$count=mysql_num_rows($result1);

// if found this passkey in our database, retrieve data from table "temp_members_db"
if($count==1){

$confirm_code=md5(uniqid(rand()));
$rows=mysql_fetch_array($result1);
$myusername=$rows['username'];
$mypassword=$rows['password'];
$address=$rows['address']; 
$email=$rows['email'];
$phonenumber=$rows['phonenumber'];
$altphonenumber=$rows['altphonenumber'];


// Insert data that retrieves from "temp_members_db" into table "registered_members" 
 $sql2 = "INSERT INTO temp_details (confirm_code,username,password,address,email,phonenumber,altphonenumber,messreply)
              VALUES('".$confirm_code."','".$myusername."', '".$mypassword."', '".$address."', '".$email."', '".$phonenumber."', '".$altphonenumber."','no')";

$result2=mysql_query($sql2);
$sql3="DELETE FROM details WHERE userdeleted = 'no'";
$result3=mysql_query($sql3);
}
else {
echo "failed";
}

//SEND EMAIL//
$result4 = mysql_query("SELECT email FROM temp_details");
if($result4) {
	while($r = mysql_fetch_row($result4)) {
                // create email message
				
				// ---------------- SEND MAIL FORM ----------------

// send e-mail to ...
$to=$result4;
	echo "User: $to";
// Your subject
$subject="Your confirmation link here";

// From
$header="from: Admin@StudentClothing <admin@support.com>";

// Your message
$message="Your Comfirmation link \r\n";
$message.="Click on this link to activate your account \r\n";
$message.="http:confirmation.php?passkey=$confirm_code";

// send email
$sentmail = mail($to,$subject,$message,$header);

}
}
// if not found 
else {
echo "Not found your email in our database";
}

// if your email succesfully sent
if($sentmail){
echo "Your Confirmation link Has Been Sent To Your Email Address.  <a href='index.php'>Back to main page</a>";
}
else {
echo " Cannot send Confirmation link to your e-mail address";
}
}
?>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Binkers
Binkers
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Your code returns unexpected { in line 19

When  i take it out i get unexpect T_string on line 20.
I missed a paragraph closing on line 18.  It should be:

while($rows=mysql_fetch_array($result1)){
still get same errors as before...

User: Resource id #6
Notice: Undefined variable: /test.php on line 60

Warning: mail() expects parameter 1 to be string, resource given in test.php on line 63
Cannot send Confirmation link to your e-mail address
the undefined variable on line 60 was confirm code.
The problem is getting the email address from the table... it doesnt seem to be returning each result and sending it to the user.
Problem Solved,

Changed statement on line 43 to a SQL query rather than a php one.
Also added a count for the num_rows
then in the while loop declared email rows as 'email'
and confrim_code

Solved own problem with Blinkers Help.
Grade B awarded for non-full answer but helped to achieve the full answer.

Grade A's awarded for Accurate and easy to follow.