Mailinglist – php example code – part 2 – subscribtion page
Article describe database table design and appropriate parts of index.php responsible for subsribtion of user into a mailinglist.
Before preparation of php code for our subscribtion page, we must do some consideration about data that will by stored from users subsribing into mailinglist.
Establishment of database table
In our mailinglist database table will hold ifo about:
- first name of subscriber
- lastname of subscriber
- current date of subscribtion (now() function produce current timestamp)
- e-mail of subcriber – UNIQUE value allowed only!!
- GDPR true/ flase hold in tiny INT filed
- Newsletter subscribed info – true/false hold in tiny INT field
- ID
Next picture shows structure of table mailinglist in PHPmyadmin
For simplified perparation of database table is prepared creational script createdatabase.php with content:
<!– ****************************************************************** –>
<!– PHP code for automation of preparation databasetable for mailinglist app –>
<!– ********************************************************************* –>
<!– Vrsion: 1.0 Date: 8.9.2020 by CDesigner.eu –>
<!– ********************************************************************* –>
<?php // script for accessing database and first table structure establishement
/* Attempt MySQL server connection. Assuming you are running MySQL
server with (user ‚admin‘ with password test*555) */
$dbc = mysqli_connect(„localhost“, „admin“, „test*555“, „test“);
// Check connection
if($dbc === false){
die(„ERROR: Could not connect to database. “ . mysqli_connect_error());
}
// Attempt create table query execution
$sql = „CREATE TABLE mailinglist(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
firstname_of_subscriber VARCHAR(40) NOT NULL,
secondname_of_subscriber VARCHAR(40) NOT NULL,
write_date DATETIME NOT NULL,
email VARCHAR(70) NOT NULL UNIQUE, /* UNIQUE e-mails enabled only as security befor sending duplicite messages */
/* message_text TEXT */ /* optionally add boolean fields for subscription */
GDPR_accept BOOLEAN, /* BOOLEAN value if user accepted GDPR */
news_accept BOOLEAN /* BOOLEAN value if user accepted newsletter */
)“;
if(mysqli_query($dbc, $sql)){
echo „Table created successfully.“;
} else{
echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);
}
// Close connection
mysqli_close($dbc);
?>
Form part of php code
Our form code looks like
<form method=“post“ action=“<?php echo $_SERVER[‚PHP_SELF‘]; ?>„>
<div class=“form-group“>
<label>Please provide Your first name:</label>
<input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚firstname‘]) ? $firstname : “; ?>'“ name=“firstname“ class=“form-control“ value=“<?php echo isset($_POST[‚firstname‘]) ? $firstname : ‚Your Firstname‘; ?>“>
<label>Please provide Your last name:</label>
<input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚firstname‘]) ? $lastname : “; ?>'“ name=“lastname“ class=“form-control“ value=“<?php echo isset($_POST[‚lastname‘]) ? $lastname : ‚Your Lastname‘; ?>“>
</div>
<div class=“form-group“>
<label>E-mail:</label>
<input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚email‘]) ? $email : ‚@‘; ?>'“name=“email“ class=“form-control“ value=“<?php echo isset($_POST[‚email‘]) ? $email : ‚@‘; ?>“>
</div>
<div class=“form-group“>
<input type=“checkbox“ name=“gdpr“ class=“form-control“ value=“<?php echo isset($_POST[‚gdpr‘]) ? $gdpr : ‚gdpr‘; ?>“>
<label>I agree with GDPR regulations</label>
<input type=“checkbox“ name=“newsletter“ class=“form-control“ value=“<?php echo isset($_POST[‚newsletter‘]) ? $newsletter : ‚newsletter‘; ?>“>
<label>I subscribe to Newsletter:</label>
</div>
<button type=“submit“ name=“submit“ class=“btn btn-warning“> Subscribe to mailinglist </button>
<button type=“submit“ name=“delete“ class=“btn btn-danger“> Unsubscribe now </button>
<button type=“submit“ name=“reset“ class=“btn btn-info“> Reset form </button>
<br>
For outputting of succesfull message after adding e-mail into a list is used
<?php //part displaying info after succesfull added subscriber into a mailinglist
if ($is_result ) {
echo „<br> <br>„;
echo „ <table class=\“table table-success\“> „;
echo „ <tr>
<td><h5> <em> E-mail: </em> $email </h5> <h5> succesfully added to mailinglist and granted these privileges </h5> „;
if ($gdpr == true ) { echo „<h5> GDPR accepted </h5>„; } ; //if GDPR rights granted
if ($newsletter == true ) { echo „<h5> Newsletter subscribed </h5>„; } ; //if subscribed to a newsletter
echo “ <td> </tr> „;
echo “ </table> „;
//echo “ <input type=“text“ id=“result_field“ name=“result_field“ value=“$result“ > <br>“ ;
} ;
?>
Main script on index.php page
Main sript is located on upper part of index.php page. This code is responsible for obtaining POST submitted data (self submission). Next make validation and injection preventing by simple htmlspecialchar(). Only valid e-mails can pass to next stage.
Next parts make solution for database subscriber inserting, deletion of current unwanted subscriber (at time of current opened subsribe form, user can make quick remove decision).
If user will remove next time, must contact admin or in future code will by expaned about separate page for removing by e-mail but without listening table of currently subscribed user (GDPR data lost prevention). But keep in mind our apps are only for demonstration, before proper ussage must be security hardened in a much deeper way (use it on your own risk).
<?php
// two variables for message and styling of the mesage with bootstrap
$msg = “;
$msgClass = “;
// default values of auxiliary variables
$email = „“;
$firstname = „“;
$lastname = „“;
$gdpr = ‚0‘;
$newsletter = ‚0‘;
$is_result = false; //before hitting submit button no result is available
// Control if data was submitted
if(filter_has_var(INPUT_POST, ‚submit‘)){
// Data obtained from $_postmessage are assigned to local variables
$firstname = htmlspecialchars($_POST[‚firstname‘]);
$lastname = htmlspecialchars($_POST[‚lastname‘]);
$email = htmlspecialchars($_POST[‚email‘]);
$gdpr = isset($_POST[‚gdpr‘]); // checkbox doesnot send post data, they must be checked for its set state !!!
$newsletter = isset($_POST[‚newsletter‘]);
// Controll if all required fields was written
if(!empty($email) && !empty($firstname) && !empty($lastname)){
// If check passed – all needed fields are written
// Check if E-mail is valid
if(filter_var($email, FILTER_VALIDATE_EMAIL) === false){
// E-mail is not walid
$msg = ‚Please use a valid email‘;
$msgClass = ‚alert-danger‘;
} else {
// E-mail is ok
$is_result = true;
$toEmail = ‚ciljak@localhost.org‘; //!!! e-mail address to send to – change for your needs!!!
$subject = ‚Guestbook entry from ‚.$firstname.‘ ‚.$lastname;
$body = ‚<h2>To your Guestbook submitted:</h2>
<h4>Name</h4><p>‘.$firstname.'</p>
<h4>Email</h4><p>‘.$email.'</p>
‚;
// Email Headers
$headers = „MIME-Version: 1.0″ .“\r\n“;
$headers .=“Content-Type:text/html;charset=UTF-8″ . „\r\n“;
// Additional Headers
$headers .= „From: “ .$lastname. „<„.$email.“>“. „\r\n“;
// insert into databse
// make database connection
$dbc = mysqli_connect(„localhost“, „admin“, „test*555“, „test“);
// Check connection
if($dbc === false){
die(„ERROR: Could not connect to database. “ . mysqli_connect_error());
}
// INSERT new entry
$sql = „INSERT INTO mailinglist (firstname_of_subscriber, secondname_of_subscriber, write_date, email, GDPR_accept, news_accept)
VALUES (‚$firstname‚, ‚$lastname‚, now() , ‚$email‚ , ‚$gdpr‚ , ‚$newsletter‚)“;
if(mysqli_query($dbc, $sql)){
$msg = ‚new subscriber‘.$email.‘ succesfully added‘;
$msgClass = ‚alert-success‘;
} else{
$msg = „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);
$msgClass = ‚alert-danger‘;
}
// end connection
mysqli_close($dbc);
if(mail($toEmail, $subject, $body, $headers)){
// Email Sent
$msg .= ‚Your postmessage was sucessfully send via e-mail‘;
$msgClass = ‚alert-success‘;
} else {
// Failed
$msg = ‚Your postmessage was not sucessfully send via e-mail‘;
$msgClass = ‚alert-danger‘;
}
}
} else {
// Failed – if not all fields are fullfiled
$msg = ‚Please fill in all contactform fields‘;
$msgClass = ‚alert-danger‘; // bootstrap format for allert message with red color
}
};
// if delete button clicked
if(filter_has_var(INPUT_POST, ‚delete‘)){
if(filter_var($email, FILTER_VALIDATE_EMAIL) === false){
// E-mail is not walid
$msg = ‚Please use a valid email‘;
$msgClass = ‚alert-danger‘;
} else {
$msg = ‚Delete last mesage hit‘;
$msgClass = ‚alert-danger‘; // bootstrap format for allert message with red color
// delete from database
// make database connection
$dbc = mysqli_connect(„localhost“, „admin“, „test*555“, „test“);
// Check connection
if($dbc === false){
die(„ERROR: Could not connect to database. “ . mysqli_connect_error());
}
// DELETE last input by matching your written message
// obtain message string for comparison
$email = htmlspecialchars($_POST[‚email‘]);
$postmessage = trim($postmessage);
// create DELETE query
$sql = „DELETE FROM mailinglist WHERE email = „.“‚$email‚“ ;
if(mysqli_query($dbc, $sql)){
$msg = ‚Last subscriber sucessfully removed from database.‘;
$msgClass = ‚alert-success‘;
// clear entry fileds after sucessfull deleting from database
$firstname =“;
$lastname =“;
$email =“;
$gdpr = false;
$newsletter = false;
} else{
$msg = „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);
$msgClass = ‚alert-danger‘;
}
// end connection
mysqli_close($dbc);
}
};
// if reset button clicked
if(filter_has_var(INPUT_POST, ‚reset‘)){
$msg = “;
$msgClass = “; // bootstrap format for allert message with red color
$firstname =“;
$lastname =“;
$email =“;
$gdpr = false;
$newsletter = false;
};
?>
Current version of mailingapp can be obtained from github here.