Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

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.



0
NeoAshura
Asked:
NeoAshura
  • 10
  • 4
1 Solution
 
BinkersCommented:
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.
0
 
JermTheWormCommented:
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);
}


   
0
 
NeoAshuraAuthor Commented:
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NeoAshuraAuthor Commented:
@ Binkers thanks for the code, I will try it and combine it with the code to move my data from one table to another.
0
 
NeoAshuraAuthor Commented:
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

0
 
BinkersCommented:
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

0
 
NeoAshuraAuthor Commented:
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

0
 
BinkersCommented:
Why are you checking for a count of one? "if($count==1){"
I assume that you will be moving more than one row so you need to change this if statement to a while loop as follows.
<?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"
// CHANGE if to while
while($rows=mysql_fetch_array($result1){

$confirm_code=md5(uniqid(rand()));
// REMOVE THIS LINE $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

0
 
NeoAshuraAuthor Commented:
Your code returns unexpected { in line 19

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

while($rows=mysql_fetch_array($result1)){
0
 
NeoAshuraAuthor Commented:
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
0
 
NeoAshuraAuthor Commented:
the undefined variable on line 60 was confirm code.
0
 
NeoAshuraAuthor Commented:
The problem is getting the email address from the table... it doesnt seem to be returning each result and sending it to the user.
0
 
NeoAshuraAuthor Commented:
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.
0
 
NeoAshuraAuthor Commented:
Grade B awarded for non-full answer but helped to achieve the full answer.

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now