Page 1 of 1

Problem with Mailing List script I made...

Posted: Wed Feb 01, 2006 4:36 am
by invision
Hello,

Can someone please help me out with a very basic mailing list concept I have.

In a profiles table which stores the user_id, I have an ENUM which states 'Y' or 'N' if a user is on the mailing list. I also have the users table, which stores the users email address.

How easy is it to combine all of the email addresses into one go and send a message out to users who have 'Y' in the maillist field. I'd heard about a 'rowdata' type.

Code: Select all

<?php # - mail_out.php 
// This is the mail_out page for Pictures of Scotland admin to email members if they are 'subscribed' to the mailing list. 

// Inclyde the configuration file for error management again 
require_once ('../includes/config.inc.php'); 

// Set the page title and include the HTML header. 
$page_title = 'Mailing List Admin'; 
include ('../includes/aheader.html'); 

if (isset($_POST['submitted'])) { // Handle the form. 

require_once ('../../mysql_connect.php'); // Connect to the database 

$query = "SELECT u.username, u.email, p.user_id FROM users u, profiles p WHERE p.maillist='Y'"; 
$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error()); 
    if (mysql_affected_rows() == 1) { 
    // if it ran OK 

    // Send the email. 
    $body = $_POST['mail_out']; 
    while ($row = mysql_fetch_array($result))  { 
    mail($row['email'], 'Pictures of Scotland Newsletter', $body, 'From:Pictures of Scotland<bla2@bla.com>'); 
    } 

    // Finish the page 
    echo '<H3>Your mail-out has been sent successfully to those on the Mailing List.</h3>'; 
    include ('../includes/afooter.html'); // Include the HTML footer. 
    exit(); 

} 

mysql_close(); // Close the connection with the Database 

} // End of the main Submit conditional. 

?> <h2>Mailing List</h2><p>Type in a message below to be sent to everyone on the 
mailing list.</p><form action="mail_out.php" method="post"> <fieldset> <p><b>Enter Mail Out here :</b><br><textarea name="mail_out" COLS="60" ROWS="8"></textarea></p></fieldset> 
<div align="center"><input type="submit" name="submit" value="Submit Mail Out" /></div><input type="hidden" name="submitted" value="TRUE" /> 
</form> 
<?php 
include ('../includes/afooter.html'); 
?>
profiles table

Code: Select all

CREATE TABLE `profiles` (
  `pid` tinyint(4) NOT NULL auto_increment,
  `nickname` varchar(15) NOT NULL default '',
  `sex` enum('M','F') NOT NULL default 'M',
  `age` tinyint(3) NOT NULL default '0',
  `interests` text NOT NULL,
  `country` varchar(50) NOT NULL default '',
  `maillist` enum('Y','N') NOT NULL default 'Y',
  `user_id` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`pid`)
)
users table

Code: Select all

CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(16) NOT NULL default '',
  `email` varchar(40) NOT NULL default '',
  `pass` varchar(40) NOT NULL default '',
  `first_name` varchar(15) NOT NULL default '',
  `last_name` varchar(30) NOT NULL default '',
  `active` varchar(32) default NULL,
  `registration_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `username` (`username`),
  )


After completing the textarea field and submit, it just returns to 'mail_out.php'.

Nothing is sent. I have myself as 'Y' in the maillist column for testing purposes.

Can anyone spot an error or a reason why it isn't working ??

Thanks for anyone taking a look.

Posted: Wed Feb 01, 2006 5:48 am
by JayBird
insert some debugging, see if the email is actually being sent

change

Code: Select all

<?php
mail($row['email'], 'Pictures of Scotland Newsletter', $body, 'From:Pictures of Scotland<bla2@bla.com>');  ?>
to

Code: Select all

<?php
if(mail($row['email'], 'Pictures of Scotland Newsletter', $body, 'From:Pictures of Scotland<bla2@bla.com>')) {
    echo "Email sent";
} else {
    echo "Error sending email to ".$row['email'];
?>

Posted: Wed Feb 01, 2006 5:56 am
by invision
Thanks for the reply.

Here's a snippet of the new code :

Code: Select all

$query = "SELECT u.username, u.email, p.user_id FROM users u, profiles p WHERE p.maillist='Y'";  
$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());  
    if (mysql_affected_rows() == 1) {  
    // if it ran OK  

    // Send the email.  
    $body = $_POST['mail_out'];  
      
    if(mail($row['email'], 'Pictures of Scotland Newsletter', $body, 'From:Pictures of Scotland<bla2@bla.com>')) { 
    echo "Email sent"; 
} else { 
    echo "Error sending email to ".$row['email']; 
    }  

    // Finish the page  
    echo '<H3>Your mail-out has been sent successfully to those on the Mailing List.</h3>';  
    include ('../includes/afooter.html'); // Include the HTML footer.  
    exit();  

}
It does nothing at all, which is very strange.

I click 'submit mail out' and it seems to just refresh the page, and removes any text I had in the <textarea>

Posted: Wed Feb 01, 2006 6:00 am
by JayBird
put..

Code: Select all

<?php

echo "<pre>";
print_r($_POST);
echo "</pre>";

?>
before...

Code: Select all

<?php

if (isset($_POST['submitted'])) { // Handle the form.

?>
Submit the form and post the results

Posted: Wed Feb 01, 2006 6:05 am
by invision
Thanks for your patience with this.


Array
(
[mail_out] => Tester
[submit] => Submit Mail Out
[submitted] => TRUE
)

Posted: Wed Feb 01, 2006 6:08 am
by JayBird
Think i got it

change

Code: Select all

<?php

if (mysql_affected_rows() == 1) { 

?>
to

Code: Select all

<?php

if (mysql_num_rows() != 0) { 

?>
mysql_affected_rows is only used for INSERT, UPDATE or DELETE queries

Posted: Wed Feb 01, 2006 6:11 am
by invision
I wanna know what love is
I want you to show me
I wanna feel what love is
I know you can show me
Hurray! It works!!!!!!

Wow am I delighted. Thank you thank you thank you! :D Yipee!

Posted: Wed Feb 01, 2006 6:12 am
by JayBird
no problem....what you wanna do, wire the money into my account :lol: 8) :wink:

Posted: Wed Feb 01, 2006 6:13 am
by invision
Heh, I'm very close to doing that :D

Thanks again, my scripts always have one ruddy error in them. :D

Posted: Wed Feb 01, 2006 6:24 am
by invision
It worked great, but when I tried using a WHILE loop with it, it sent to everyone on the mailing list, not even considering the WHERE clause.

Here's how it looks just now :

Code: Select all

$query = "SELECT u.username, u.email, p.user_id FROM users u, profiles p WHERE p.maillist='Y'";  
$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());  
    if (mysql_num_rows($result) != 0) {   
    // if it ran OK  

    // Send the email.  
    while ($row = mysql_fetch_array($result))  {
    $body = $_POST['mail_out'];
    if(mail($row['email'], 'Pictures of Scotland Newsletter', $body, 'From:Pictures of Scotland<bla2@bla.com>')) { 
    echo "Email sent"; 
} else { 
    echo "Error sending email to ".$row['email']; 
    }  
}
    // Finish the page  
    echo '<H3>Your mail-out has been sent successfully to those on the Mailing List.</h3>';  
    include ('../includes/afooter.html'); // Include the HTML footer.  
    exit();  

}

Posted: Wed Feb 01, 2006 8:53 am
by feyd
You've got a logic error in your JOIN: you haven't specified how the tables are to attach to each other.

Posted: Wed Feb 01, 2006 10:38 am
by invision
Thanks for the reply.

So should I have a

Code: Select all

AND  u.user_id = p.user_id
??