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

<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>

<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>

<label>I agree with GDPR regulations</label>

<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.