Creating a mailing list - HELP PLEASE???????

[Graham]

Ok I admit that I am a total newbie when it comes to this. but please bear with me - we all gotta be new sometime.... :confused:

I want to create a simple (?) mailing list that people can subscribe to by entering their name and email address on a web page. It is intended as a newsletter-type service where I can send out regular updates to subscribers. They will not need posting rights, other than to subscribe and unsubscribe.

The subscriber data is stored, and accessed whenever I need to send out an update to the list. I guess it would be good to have some kind of verification check to ensure that people are genuine subscribers?

Can anyone point me in the right direction so that I can figure out what will be the least complex way to achieve something like this please?

Any help to get started would be very greatly appreciated!! :D
thanks,
Graham

[scragar]

what sever side language do you have? PHP, ASP, CGI, Perl? what?(you will need at least one, and a database, a text file will work istead of a database, but let's keep things simple...)

[Graham]

My hosting service supports the following
PHP 4.3.3
Perl Version: 5.8.0
CGI
MySQL databases and phpMyAdmin are provided also
Cheers,
Graham

[scragar]

let's go for PHP, I know more of that.
first you'll have to create a mysql database, most host will let you do this, see if you can find the option. If you can then we'll continue, otherwise we'll have to try something different...


first you'll need a simple HTML form, something like this will surfice:

<form action="sub.php" method="post">
Name: <input type="text" name="Fname" value=""><br>
e-mail: <input type="text" name="Fmail" value=""><br>
<input type="submit" name="sub" value="submit">
</form>

[Graham]

i have mysql database creation access
thanks for the form code also.

I have the code in an html page ready to go

Graham

[scragar]

create a database and remember your databse name, username and password.
then need a table:
subscribers
-- id(big int, autoincrement)
-- Name(varchar, 255)
-- Fmail(varchar, 255)
if you have trouble setting this up then ask.
<?
$user="username";
$password="password";
$database="database";
$host="host name, usualy localhost, but you may need something different if your server says so...";
@mysql_connect($host,$user,$password) or die("unable to make connection.");
@mysql_select_db($database) or die("Unable to select database");
$query="INSERT INTO subscribers VALUES ('','".$_POST['Fname']."','".$_POST['Fmail'];
if(@mysql_query($query)){
echo "you are now added to the mailing list.";
}else{
echo "oops, somethings gone wrong. Refresh the page to try again.";
};
mysql_close();
?>

[Graham]

ok - got the database with the 3 fields
just confirming that they have all defaulted to NOT NULL ok? also the id field is set as the primary key - ok?

What do I do with the PHP code, I assume I paste it into a new php page?

In the PHP code you use Fname, but in the fields you use Name, should these be the same?
Graham

[scragar]

yes and yes.
sub.php, you can add HTML around it if you want.
they don't have to be, you can change the table if you want.

if that's set up you'll now be able to gather emails to your database.

list.php(for your use, if you wish to look through your info)<?
$user="username";
$password="password";
$database="database";
$host="host name, usualy localhost, but you may need something different if your server says so...";
@mysql_connect($host,$user,$password) or die("unable to make connection.");
@mysql_select_db($database) or die("Unable to select database");
$query="SELECT * FROM subscribers";
$rs = @mysql_query($query);
if($rs){
if(mysql_num_rows($rs) != 0){
echo "<table><tr><td>ID</td><td>Name</td><td>Email</td></tr>";
$i=0;
$num = mysql_num_rows($rs);
while ($i < $num){

$web=mysql_result($result,$i,"web");
echo "<tr><td>".mysql_result($rs,$i,"id")."</td><td>";
echo mysql_result($rs,$i,"Name")."</td><td>";
echo mysql_result($rs,$i,"Fmail")."</td></tr>";
$i++;
};
echo "</table>";
}else{
echo "oops, somethings gone wrong. Refresh the page to try again.";
};
mysql_close();
?>

[Graham]

Thanks so much - I really appreciate this help!!

Ok so now I have two pages, sub.php and list.php which I should now upload to the server I assume?
(Sorry that I am so thick! ;) )

[Graham]

Hey scragar,
Thanks for your help so far.

I have sub.php uploaded to the website, but when I try subscribe to the list I only get "oops, somethings gone wrong. Refresh the page to try again."

When I run list.php I get
Parse error: parse error, unexpected $ in /home/ellasite/public_html/list.php on line 39

Any idea what I may be doing wrong please??
Cheers,
Graham

[scragar]

try replacing "oops, somethings gone wrong" with mysql_error()

this should give us an idea...

[Graham]

when i subscribe now I get
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''graham@grahammercer.com.au' at line 1

that line of the code in sub.php is
$query="INSERT INTO subscribers VALUES ('','".$_POST['Fname']."','".$_POST['Fmail'];

should the ' after the ( be a " ?
should there be a ) somewhere in the code to close the brackets?
As you can tell I know very little about the programming language, these 2 points just seem odd to me????

[scragar]

$query="INSERT INTO subscribers VALUES ('','".$_POST['Fname']."','".$_POST['Fmail']."')";

that should fix that page.

[Graham]

right on!! that has fixed the subs page and it is now accepting entries

[scragar]

<? //list.php
$user="username";
$password="password";
$database="database";
$host="host name...";
@mysql_connect($host,$user,$password) or die("unable to make connection.");
@mysql_select_db($database) or die("Unable to select database");
$query="SELECT * FROM subscribers";
$rs = @mysql_query($query);
if($rs){
if(mysql_num_rows($rs) != 0){
echo "<table><tr><td>ID</td><td>Name</td><td>Email</td></tr>";
$i=0;
$num = mysql_num_rows($rs);
while ($i < $num){
echo "<tr><td>".mysql_result($rs,$i,"id")."</td><td>";
echo mysql_result($rs,$i,"Name")."</td><td>";
echo mysql_result($rs,$i,"Fmail")."</td></tr>";
$i++;
};
echo "</table>";
}else{
echo "no people in the list";
};
}else{
echo "oops, somethings gone wrong. Refresh the page to try again.";
};
mysql_close();
?>

[Graham]

Ok you are a legend!
Initially it was returning this after your latest suggestion
ID Name Email
1
Warning: mysql_result(): Name not found in MySQL result index 3 in /home/ellasite/public_html/list.php on line 25
graham@grahammercer.com.au
2
Warning: mysql_result(): Name not found in MySQL result index 3 in /home/ellasite/public_html/list.php on line 25
rose.mercer@optusnet.com.au

so I changed the "Name" to "Fname" as per the field name and it is listing the records nicely!!

One more thing if I may be so bold.......

Now that the entries are being written to the database records from the webpage (thanks to you) how do I access those records to create and send out my mailing lists???? (I said I was thick!!) Also is there an easy way to add an UNSUBSCRIBE option?
thanks heaps

[scragar]

to add a unsubscribe option just create a page to unsubscribe:

unsub.php<?
if(isset($_GET['id'])){
if(preg_match("/^[0-9]+$/", ($_GET['id'].""))){
if(mysql_query("DELETE FROM subscibers WHERE id=".$_GET['id'])){
echo "you have been removed";
}else{
echo "error, you could not be found or sublied an invalid ID";
};
}else{
echo "that id is invalid, it is not a number"
};
}else{
echo "please use the links in your e-mails to unsubscribe";
};
?>
send.html<form action="sendMail.php" method=post>
title: <input type="text" name="subject"><br>
content: <textarea name="message"></textarea>
<input type="submit" name="sub" value="send emails">
</form>
sendMail.php<?
$user="username";
$password="password";
$database="database";
$host="host name...";
@mysql_connect($host,$user,$password) or die("unable to make connection.");
@mysql_select_db($database) or die("Unable to select database");
$query="SELECT * FROM subscribers";
$rs = mysql_quesy($query);
if($rs){
if(mysql_num_rows($rs) != 0){
$i=0;
$num = mysql_num_rows($rs);
while ($i < $num){
mail(mysql_result($rs,$i,"Fmail"), $_POST['title'], $_POST['message']."\n\r
to unsubscribe please follow the link below:
http://www.yourDomain.com/unsub.php?id=".mysql_result($rs,$i,"id"));
$i++;
};
echo "mail sent";
}else{
echo "no people in the list";
};
}else{
echo "oops, somethings gone wrong. Refresh the page to try again.";
};
mysql_close();
?>

[Graham]

Once more thank you so much for your time and patience!
I will look through the stuff you just sent me and try to analyse how it all works etc so that I can benefit from your kindness.
A few questions to clarify the most recent of your posts.....
Anybody wishing to unsubscribe will do so from a received mailing list message by clicking on the unsubscribe link that is automatically added to the 'message' field. The script will extract the person's id and include it when they unsubscribe.
Is that correct?

Given that I have to hardcode the username and password into the php pages does that pose any security isssues for the site?

I can't thank you enough for all your help, you have been fantastic!!
Cheers
Graham

[scragar]

yes, and usualy you would create a page somewhere in the root directory that would hold your username and password and such, although it's not a security flaw, it is common for updates(you change your password you have a lot of pages to change, if you only have 1 include then that's just one file).

xxx.php<?
$user="username";
$password="password";
$database="database";
$host="host name...";
@mysql_connect($host,$user,$password) or die("unable to make connection.");
@mysql_select_db($database) or die("Unable to select database");
?>

then on all your pages you can just begin with
<?
include "xxx.php";

as opposed to the current <?
$user="username";
$password="password";
$database="database";
$host="host name...";
@mysql_connect($host,$user,$password) or die("unable to make connection.");
@mysql_select_db($database) or die("Unable to select database");

oh and add that bit at the top of unsub.php as well, I forgot it.


Next up, security and how to prevent injection probelms.

[Graham]

Ok, now to plug all this latest stuff into the site and test it all out!
I REALLY do appreciate the help.
thanks a million,
Graham

[scragar]

security lesson 1. checking an email.

when you recive an email how can you be sure it has an @ symbol, or that it's even at a site? that's what regular expressions are for.
<?
include "xxx.php";



if(preg_match("/^[a-z0-9]+([_\\.-][a-z0-9]+)*@([a-z0-9]+([\.-][a-z0-9]+)*)+\\.[a-z]{2,}$/i", $_POST['Fmail'])){//an invalid email.
echo "that email is not valid. please check it and try again.";
exit;//do not continue with the rest of the page.
};



$query="INSERT INTO subscribers VALUES ('','".$_POST['Fname']."','".$_POST['Fmail'];
if(@mysql_query($query)){
echo "you are now added to the mailing list.";
}else{
echo "oops, somethings gone wrong. Refresh the page to try again.";
};
mysql_close();
?>

[Graham]

ok I have implemented the code from the previous couple of posts and it is sending out email messages. However, the messages do not include a FROM in the outgoing message and it is not including the Subject field.

If I try to unsubscribe a recipient I get
Parse error: parse error, unexpected '}', expecting ',' or ';' in /home/ellasite/public_html/unsub.php on line 20

I will look at the security stuff you sent, thanks very much for the ongoing help

[scragar]

mail(mysql_result($rs,$i,"Fmail"), $_POST['title'], $_POST['message']."\n\r
to unsubscribe please follow the link below:
http://www.yourDomain.com/unsub.php?id=".mysql_result($rs,$i,"id")); tomail(mysql_result($rs,$i,"Fmail"), $_POST['subject'], $_POST['message']."\n\r
to unsubscribe please follow the link below:
http://www.yourDomain.com/unsub.php?id=".mysql_result($rs,$i,"id"));

should solve the subject problem.


I can't figure out why it throws out the unsubscribe error, I'll have a better look now.

[Graham]

Doh!!!! It is so obvious when you know what to look for isnt it???? Simply changing 'title' to 'subject' - but it would of taken me ages (if ever to track that down)
Thanks :o

[scragar]

I should have noticed it when I first wrote it.

which line of the code I posted is line 20?(I asume you've added stuff above/bellow, cos my code isn't that long).

[Graham]

echo "error, you could not be found or supplied an invalid ID";
};
}else{
echo "that id is invalid, it is not a number"
}; THIS IS LINE 20 IN MY CODE
}else{
echo "please use the links in your e-mails to unsubscribe";

[scragar]

found the error:if(isset($_GET['id'])){
if(preg_match("/^[0-9]+$/", ($_GET['id'].""))){
if(mysql_query("DELETE FROM subscibers WHERE id=".$_GET['id'])){
echo "you have been removed";
}else{
echo "error, you could not be found or sublied an invalid ID";
};
}else{
echo "that id is invalid, it is not a number";//semicolon missing here!
};
}else{
echo "please use the links in your e-mails to unsubscribe";
};
?>

[Graham]

thanks I will plug that code into the page
I have added your security code, but now the page is rejecting everything,
valid email addresses generate "that email is not valid. please check it and try again."
something without the @ symbol generates oops, something has gone wrong. Refresh the page to try again.

[scragar]

if(!preg_match("/^[a-z0-9]+([_\\.-][a-z0-9]+)*@([a-z0-9]+([\.-][a-z0-9]+)*)+\\.[a-z]{2,}$/i", $_POST['Fmail'])){//an invalid email.
echo "that email is not valid. please check it and try again.";
exit;//do not continue with the rest of the page.
};


why is it always the little things that make the big problems?

[Graham]

I feel bad taking up so much of your time here.
The addresses without a @ are now rejecting with the correct message, however valid email addresses are generating "oops, something has gone wrong. Refresh the page to try again"
The email messages are now going out with a subject, but how do I add a FROM line please?

[scragar]

mail(mysql_result($rs,$i,"Fmail"), $_POST['subject'], $_POST['message']."\n\r
to unsubscribe please follow the link below:
http://www.yourDomain.com/unsub.php?id=".mysql_result($rs,$i,"id"), "from: me@mysite.com");


I don't know why your getting errors...

I'm going to be ofline untill tuesday, so I'm not much help, sorry. bye.

[Graham]

You have been a great help, fabulous of you to give your time like you have.
Thanks and enjoy your break!!
Cheers,
Graham

[scragar]

<?
include "xxx.php";

if(preg_match("/^[a-z0-9]+([_\\.-][a-z0-9]+)*@([a-z0-9]+([\.-][a-z0-9]+)*)+\\.[a-z]{2,}$/i", $_POST['Fmail'])){//an invalid email.
$query="INSERT INTO subscribers VALUES ('','".$_POST['Fname']."','".$_POST['Fmail']."')";
if(@mysql_query($query)){
echo "you are now added to the mailing list.";
}else{
echo "oops, somethings gone wrong. Refresh the page to try again.";
};
}else{
echo "that email is not valid. please check it and try again.";
};

mysql_close();
?>

[Graham]

Hi, hope you had a good break.
The only problems I am experiencing now are
- the mailing list emails do not have a FROM: abc@def.com.au field included
and
- the unsubscribe command returns 'error, you could not be found or supplied an invalid ID'

If you get a chance to offer some last advice it would be fabulous.

Thanks so much,
Graham

[scragar]

to add headers use:mail(mysql_result($rs,$i,"Fmail"), $_POST['subject'], $_POST['message']."\n\r
to unsubscribe please follow the link below:
http://www.yourDomain.com/unsub.php?id=".mysql_result($rs,$i,"id"), "From: Your Name <News@MySite.com>");as opposed to the currentmail(mysql_result($rs,$i,"Fmail"), $_POST['subject'], $_POST['message']."\n\r
to unsubscribe please follow the link below:
http://www.yourDomain.com/unsub.php?id=".mysql_result($rs,$i,"id"));

and can try changing the:
"error, you could not be found or supplied an invalid ID"
text(including quotes) with
mysql_error()
in your unsubscribe page.

[Graham]

From field working perfectly THANKS
unsub form returns
Table 'ellasite_elladata.subscibers' doesn't exist

???
:-)

[scragar]

I missed the letter R in the "$query = " bit, just add it in so it says subscribers.

[Graham]

Like I said before - YOU ARE A LEGEND!!
It is fantastic and generous of you to provide such help and so much of your time.
Everything is now working perfectly thanks to your patience and guidance.

I dont want to sound like a sycophant but you really have been great.
I will use these messages that we have exchanged to help me try to understand more of how the code works so that I can be more self sufficient in the future.

THANKS AGAIN for your help
Graham

[scragar]

do you want me to edit them and add on comments to explain things, or will you be able to figure it out on your own using the php manual (http://www.php.net/manual/en/)?

[Graham]

It is so good to see that a 'newbie' can ask really stupid stuff here and not get howled down in derision.
I am not a programmer, just a 51 year old potter who is playing around and trying to learn something new.
Thanks for your patience and excellent advice
Graham
Where are you located??

[Graham]

Thanks, I will try to work it out on my own, I tend to learn better that way as you have given me an excellent, practical starting point.
I am sure you will be here from time to time if I ever need to clarify something that I cant sort out for myself.
Cheers,
Graham :-)

[scragar]

Where are you located??
Northwest England.It is so good to see that a 'newbie' can ask really stupid stuff here and not get howled down in derision.[quote]Asking questions is how we learn.[quote]I am not a programmer, just a 51 year old potter who is playing around and trying to learn something new.
Thanks for your patience and excellent adviceYou welcome.

And you've still not told me if you want me to comment my posts for you.

[Graham]

IF repeat IF it is not too much of a hassle the comments would be fantastic, but you have given so much of your time already.
Cheers,
Graham

[scragar]

list.php:<? //begin PHP code.
include "xxx.php";//include your conection page.
$query="SELECT * FROM subscribers";
//make a query to pull out everything(*) from the subscribers table.
$rs = @mysql_query($query);
//create a record set($rs) and hold the results of our query in it.
if($rs){
//if nothings gone wrong.
if(mysql_num_rows($rs) != 0){
//and we have some results
echo "<table><tr><td>ID</td><td>Name</td><td>Email</td></tr>";
// write out the start of a table

$i=0;
$num = mysql_num_rows($rs);
while ($i < $num){
// and while we still have unread results
echo "<tr><td>".mysql_result($rs,$i,"id")."</td><td>";
//write out a new row, containg the id from the recordset
echo mysql_result($rs,$i,"Name")."</td><td>";
// and the name
echo mysql_result($rs,$i,"Fmail")."</td></tr>";
// and email.
$i++;
// increment(add one to it) i so we loop
};
//the loop ends
echo "</table>";
// and close the table.
}else{
//we have no-one in the table.
echo "no people in the list";
//so write that out.
};
}else{
// we couldn't get the query to run.
echo "oops, somethings gone wrong. Refresh the page to try again.";
//inform the user somethings gone wrong.
};
mysql_close();
//close the connection so we don't get problems trying to open it again another time.
?> <?
include "xxx.php"; //include the connection page

if(preg_match("/^[a-z0-9]+([_\\.-][a-z0-9]+)*@([a-z0-9]+([\.-][a-z0-9]+)*)+\\.[a-z]{2,}$/i", $_POST['Fmail'])){
/*
preg_match( use a regular expression for comparison.
"/^ from the begining of the string
[a-z0-9]+ we have at least one letter/number
([_\\.-][a-z0-9]+)* and an limited number of _'s,
.'s or -'s so long as they have at
least one letter after them.
@ the @ symbol
([a-z0-9]+([\.-][a-z0-9]+)*)+ check for any number of letters/numbers
a . or - then the same again.
\\.[a-z]{2,} a dot then 2 or more letters
$/i and end, ignoring case(so A = a)
*/
$query="INSERT INTO subscribers VALUES ('','".$_POST['Fname']."','".$_POST['Fmail']."')";
//make a query.
if(@mysql_query($query)){
//if the query works
echo "you are now added to the mailing list.";
}else{ //query failed.
echo "oops, somethings gone wrong. Refresh the page to try again.";
};
}else{ //email was bad.
echo "that email is not valid. please check it and try again.";
};

mysql_close(); //end the databse connection.
?> <?
include "xxx.php";//include connection.
if(isset($_GET['id'])){ //if they have sent an ID.
if(preg_match("/^[0-9]+$/", ($_GET['id'].""))){//and it's a number.
if(mysql_query("DELETE FROM subscibers WHERE id=".$_GET['id'])){
/*
DELETE FROM subscibers
delete a result from the table of subscribers
WHERE id=".$_GET['id']
where the id is the same as suplied.
*/
echo "you have been removed";
}else{ //cannot be removed.
echo "error, you could not be found or sublied an invalid ID";
};
}else{ //not a number.
echo "that id is invalid, it is not a number";
};
}else{ //id not suplied.
echo "please use the links in your e-mails to unsubscribe";
};
mysql_close();//close the conection.
?>
I think that's all the pages, If I missed one just say. And I'm not doing anything at the moment, so feal free to ask.