MySQL question and a Mailing List question

[CrazyPunch]

I set up a mailing list, but I'd like to know how I can I have a visitor subscribe to it by simply filling their e-mail address in a form.

How can I alter and create tables in a MySQL database?

[leafypiggy]

well, first, you would create the SQL database and tables Via the PHPmyAdmin application found in the cPanel of your website. eg. yoursite.com:2082

I might have a table with the following rows.


Email varchar(225)
Validated varchar(1)
validkey varchar(15)
signupdate date

Email would be the user's email address
Validated would be the number 1, or blank, depending on if the user had validated (explained later)

validkey would be the key needed to validate the account

signup date would be the date that the user signed up (not nessacery)


I usually have the user validate, so we know that it is an actual person..not a bot.


You can find a newsletter/mailing list script @ http://www.chipmunk-scripts.com/boar...mID=43&ID=9987

if you want something custom...you can email me at hanlon.neil@gmail.com and I will hook you up with a custom script. (for a cost...(naaw jk))

[mikester]

Quite simply, Your HTML form would call a php script that would write the info into your database. After your database and tables have been created, you would not need to create any more tables...all you will need your script to do is to add/edit/remove info to one of your existing tables.

Php code similar to this is what you'll need:


// connect to the database server...
$connection = mysql_connect ($host, $username, $password) or die ('DB connection failed because: ' . mysql_error());
// select which database to use...
mysql_select_db($databasename,$connection) or die("Could not select database.");
// set up your MySql query...
$query="INSERT INTO YOUR_TABLE VALUES ($FIELD1,$FIELD2,$FIELD3)";
// send the query to the database...
$result = mysql_query($query);


Of course, you will need much more than that to have a finished, polished product - but I've explained the basic workings of getting the info into your database.

[c9311]

Not tested, quick mock up, remember yo change your MySQL details at the top, execute the SQL within phpmyadmin to make the table.

CREATE TABLE `mail_list`
(
ID int NOT NULL AUTO_INCREMENT,
email varchar(128),
);

<?php
$con = mysql_connect ($host, $username, $password)or die(mysql_error());
mysql_select_db($databasename,$con)or die(mysql_error());
$add=stripslashes(strip_tags($_GET['add']));
if($add==''){
echo '
<form action="index.php?add=do" method="post">
E-mail address: <input type="text" name="email" /><br />
Join: <input type="radio" name="join" value="join" CHECKED/><br />
Leave: <input type="radio" name="join" value="leave" /><br />
<input type="submit" value="Join/Leave" />
</form>
';
} elseif($add=='do'){
$email=stripslashes(strip_tags($_POST['email']));
$join=stripslashes(strip_tags($_POST['join']));

if(!preg_match('/^[_a-zA-Z0-9-]+(\.[_a-zA-Z0-9-]+)*@[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)+$/',$email)) {
echo "The email was not added/removed. The email address is invalid.";
die();
}

if($join=='join'){
$g=mysql_query("SELECT * FROM `mail_list` WHERE `email`='$email'");
if($y=mysql_fetch_array($g)){
echo 'This e-mail address exists.';
die();
}
$ins=mysql_query("INSERT INTO `mail_list` (`email`) VALUES ('$email')");
echo 'You have successfully joined our mailing list.';
} elseif($join=='leave'){
$g=mysql_query("SELECT * FROM `mail_list` WHERE `email`='$email'");
if(!$y=mysql_fetch_array($g)){
echo 'This e-mail address does not exists.';
die();
}
$del=mysql_query("DELETE FROM `mail_list` WHERE `email`='$email'");
echo 'You have left our mailing list.';
}
}
?>

[pabitraroy201062]

http://www.chipmunk-scripts.com/boar...mID=43&ID=9987

if you want something custom...you can email me at hanlon.neil@gmail.com and I will hook you up with a custom script. (for a cost...(naaw jk))[/QUOTE]

Thanks man..............