Solved

Change Mysql Database then Send Mail to database users

Posted on 2011-02-17
15
432 Views
Last Modified: 2013-12-13
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
Comment
Question by:NeoAshura
  • 10
  • 4
15 Comments
 
LVL 4

Expert Comment

by:Binkers
ID: 34916072
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
 
LVL 5

Expert Comment

by:JermTheWorm
ID: 34916075
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
 
LVL 6

Author Comment

by:NeoAshura
ID: 34916121
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
 
LVL 6

Author Comment

by:NeoAshura
ID: 34916137
@ 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
 
LVL 6

Author Comment

by:NeoAshura
ID: 34916188
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
 
LVL 4

Expert Comment

by:Binkers
ID: 34916504
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
 
LVL 6

Author Comment

by:NeoAshura
ID: 34916796
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 4

Accepted Solution

by:
Binkers earned 500 total points
ID: 34916869
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
 
LVL 6

Author Comment

by:NeoAshura
ID: 34916925
Your code returns unexpected { in line 19

When  i take it out i get unexpect T_string on line 20.
0
 
LVL 4

Expert Comment

by:Binkers
ID: 34916978
I missed a paragraph closing on line 18.  It should be:

while($rows=mysql_fetch_array($result1)){
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34917003
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
 
LVL 6

Author Comment

by:NeoAshura
ID: 34917017
the undefined variable on line 60 was confirm code.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34917153
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
 
LVL 6

Author Comment

by:NeoAshura
ID: 34917738
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
 
LVL 6

Author Closing Comment

by:NeoAshura
ID: 34917756
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now