[SOLVED] Email all addresses in a mysql table

nadz · May 13, 2007

I recently closed my site down for a few months, and now I'd like to invite all my members back. All addresses are in a mysql database and id like to email them all. is there a form i can make to email them a message. If possible id like to refer to them as their username. so an email would be sent like this

"Hi [username]"

any help would be appreciated.

cmgmyr · May 13, 2007

You can do something like this:

<?php

$sql = "SELECT username, email FROM users";
$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
$username = $row['username'];
$email = $row['email'];

$body = "
Hey $username,
Just wanted to let you know that our site is up and running again...
";

mail($email, "Site Re-Opening", $body, "From: YourEmail@domain.com");
}

?>

That should get you started atleast

Alith7 · May 13, 2007

I just put a similar idea in action on my site.

there's a great and easy to use freeware for php mailings here:

PHPMailer

and the creators of this great forum made a great tutorial here:

PHP Freaks tutorial

the last chunk of the tutorial is specifically for handling bulk mailings, but it's missing a few things, I had to alter my chunk of code to look like this:

<?php
// Grab our config settings
require_once("public_html/config.php");

// Grab the FreakMailer class
require_once("public_html/MailClass.inc");

// Body
$htmlBody = "<html>
<head>
<title>Registration Confirmation</title>
</head>
<body><h1>Thank you for Registering</h1>
<p>Your Registered information is:</p>
<p>Registration ID: {regid}<br />
First name: {firstname}<br />
Last name: {lastname}<br />
Email: {email}<br />
Phone: {phone}</p>
<p>Please print this page with your registration number and keep it in a safe place.<br />
  You will need to present it as confirmation of your registration if you win the camera.</p>
  </body>
  </html>"; 

$textBody = "Thank you for Registering/n/nYour Registered information is:/n/nRegistration ID: {regid}/nFirst name: {firstname}/nLast name: {lastname}/nEmail: {email}/nPhone: {phone}/n/nPlease print this page with your registration number and keep it in a safe place./nYou will need to present it as confirmation of your registration if you win the camera.";

// instantiate the class
$mailer = new RegMailer();

//Get the User's Email
mysql_select_db($database_Registration, $Registration);
$storetable = $_POST['store'];
$query_getRegId = sprintf("SELECT * FROM {$storetable} WHERE first_name=%s AND last_name=%s AND email=%s",
				GetSQLValueString($_POST['first_name'], "text"),
				GetSQLValueString($_POST['last_name'], "text"),
				GetSQLValueString($_POST['email'], "text"));
$getRegId = mysql_query($query_getRegId, $Registration) or die(mysql_error());
$row = mysql_fetch_assoc($getRegId);
$totalRows_getRegId = mysql_num_rows($getRegId);

//send the emails in this loop
//set the variables to replace in the mail text
$eregid = $row['regid'];
$efname = $row['first_name'];
$elname = $row['last_name'];
$eemail = $row['email'];
if(!empty($row['phone'])) {
	$ephone = $row['phone'];
	}
else {
	$ephone = 'No phone entered';
	}
$replaceval = array('{regid}', '{firstname}', '{lastname}', '{email}', '{phone}');
$replacevar = array($eregid, $efname, $elname, $eemail, $ephone);

// set the mail script
$mailer->Subject = 'Registration Confirmation';
$mailer->Body = str_replace($replaceval, $replacevar, $htmlBody);
$mailer->AddReplyTo('your email here');
$mailer->AddAddress($eemail);
$mailer->isHTML(true);
$mailer->AltBody = str_replace($replaceval, $replacevar, $textBody);

if(!$mailer->Send()) {
	$error['notSent'] = 'Sorry, there was a problem sending your registration. Please try again later.  If the problem persists, please contact the webmaster.';
	}
$mailer->ClearAddresses();
$mailer->ClearAttachments();
}
?>

this is refering to some dreamweaver created functions and what not so you might want to change your own mySQL query, but that should help.

nadz · May 13, 2007

thanks guys ill test both of these in the morning and see how i do. cheers.

nadz · May 14, 2007

ok this is how far i've got:

index.php:

<?php
include("config.php");
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);

include("form.php");
$formbody = $_POST['formbody'];

$sql = "SELECT $db_usernamefield, $db_emailfield FROM $db_table";
$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
$username = $row['username'];
$email = $row['email'];

$body = "$formbody";
$body = str_replace("[username]", $username, $body);
mail($email, $emailsubject, $body, "From: $fromname <$fromemail>");
}

?>

config.php:

<?php

$db_host = "[host]";
$db_user = "[username]";
$db_pwd = "[password]";
$db_name = "users";
$db_table = "members";
$db_usernamefield = "username";
$db_emailfield = "email";
$emailsubject = "[my subject]";
$fromemail = "[my email address]";
$fromname = "[site name]";

?>

form.php:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Emailer</title>
</head>

<body>
<div align="center" id="content" style="padding:5">
<form name="form1" method="post" action="">
<table style="border:solid thin black; background-color: #FFFFFF">
   	<tr>
   	  <td align="center" colspan="5">Email content:</td>
	  </tr>
   	<tr> 
      <p>
      <td><label>
        <textarea name="formbody" rows="20" type="formbody" align="center" class="text" id="formbody"></textarea>
      </label></td>
   	</tr>
    <tr> 
      <td colspan="4" align="center" style="padding:6"><input type="submit" name="Submit" value="Email your members" class="button"></td>
    </tr>
  </table>
</form>
</div>
</body>
</html>

everything works fine except for one thing - it constantly sends out emails to the addresses, even when i haven't submitted anything. i think i need to tell it only to send the emails when i have pushed the submit button. any ideas?

PS, im a noob and am very very proud of what ive got so far

nadz · May 15, 2007

ok, i tried using "else" and "if" like so:

<?php
include("config.php");
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
if (!$_POST) {
  include("form.php");
} else {
$formbody = $_POST['formbody'];

$sql = "SELECT $db_usernamefield, $db_emailfield FROM $db_table";
$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
$username = $row['username'];
$email = $row['email'];

$body = "$formbody";
$body = str_replace("[username]", $username, $body);
mail($email, $emailsubject, $body, "From: $fromname <$fromemail>");
if(mail ($email, $emailsubject, $body, "From: $fromname <$fromemail>"))
echo "Messages sent";
}

?>

but i get this php error on the page:

Parse error: syntax error, unexpected $end in /home/.loekie/nexman/*******.co.uk/email/index.php on line 24

john010117 · May 15, 2007

<?php
include("config.php");
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
if (!$_POST) {
  include("form.php");
} else {
$formbody = $_POST['formbody'];

$sql = "SELECT $db_usernamefield, $db_emailfield FROM $db_table";
$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
$username = $row['username'];
$email = $row['email'];

$body = "$formbody";
$body = str_replace("[username]", $username, $body);
mail($email, $emailsubject, $body, "From: $fromname <$fromemail>");
if(mail ($email, $emailsubject, $body, "From: $fromname <$fromemail>"))
echo "Messages sent";
}
}

?>

nadz · May 15, 2007

you must be joking, all it took was one "}".

Coding is frustrating. thanks alot to all of you, youve been a great help.

nadz · May 15, 2007

oops, ive just found one more problem. Eveything works perfect now except for one "glitch". The recipient receives my email TWICE. Any ideas why this is happening? im using this in index.php:

<?php
include("config.php");
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
if (!$_POST) {
  include("form.php");
} else {
$formbody = $_POST['formbody'];

$sql = "SELECT $db_usernamefield, $db_emailfield FROM $db_table";
$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
$username = $row['username'];
$email = $row['email'];

$body = "$formbody";
$body = str_replace("[username]", $username, $body);
mail($email, $emailsubject, $body, "From: $fromname <$fromemail>");
if(mail ($email, $emailsubject, $body, "From: $fromname <$fromemail>"))
echo "Messages sent";
}
}

?>

cmgmyr · May 15, 2007

Take out:

mail($email, $emailsubject, $body, "From: $fromname <$fromemail>");

the if statement sends the email

nadz · May 15, 2007

wow, that was quick. suppose thats it, im gonna remember this :'(

Thankyou again.

Err, wheres the "solved" button?

j5uh · March 28, 2008

This works amazingly well. My question is, how can I tweak this so that I can have the "body" of the email be pulled from a set of tables and be emailed automatically at a certain time?

cmgmyr · March 28, 2008

you can have a table "mail_messages" and have a couple columns "morning_message", "afternoon_message", and "night_message". Then you can set up a cron job to run the mail script and just grab the column you want out of the database.

j5uh · March 31, 2008

ok i've modified this code to auto execute when you open the page in a browser.

<?php
include("config.php");
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);

$sql = "SELECT $db_usernamefield, $db_emailfield FROM $db_table";
$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
$username = $row['username'];
$email = $row['email'];

$message = file_get_contents("../includes/trader.html")

$mailhead = 'MIME-Version: 1.0' . "\n";
$mailhead .= 'Content-type: text/html; charset=UTF-8' . "\n";
$mailhead .= "From: $fromname\n";

if(mail ($email, $emailsubject, $message, $mailhead))
echo "Messages sent";
}
?>

But could you elaborate on how to pull the database information into the email body?

cmgmyr · March 31, 2008

you can do something like this:

<?php 

$current_hour = date('h');

if($current_hour >= 17 && $current_hour <= 4){
	//between 5pm and 4am
	$col = 'night';
}elseif($current_hour >= 5 && $current_hour <= 12){
	//between 5am and 12 noon
	$col = 'morning';
}else{
	$col = 'afternoon';
}

?>

Where $col would be whatever you named your columns, then just put that into your query

Sign In

[SOLVED] Email all addresses in a mysql table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information