Benchmarkchart – php example code – part 3 – admin page

|

Article describe admin page for removing unwanted score with remove.php script. Remove script is invoked by GET url link, then create verification form and submit data for deletion with POST method.

Admin page show all submited score in form of a table. But against way used in index page, there is every row displayed a second row with Manage content leading text and ling for a removal script.

Better way for understanding can be gain after looking on next picture

Code that read data from database table and create this table output is as follows

<?php // code showing all subscribers in form of a table at end of the page

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);

// Check connection

if($dbc === false){

die(„ERROR: Could not connect to database – stage of article listing. “ . mysqli_connect_error());

}

// read all rows (data) from guestbook table in „test“ database

$sql = „SELECT * FROM benchmark_chart ORDER BY score DESC„; // read in reverse order of score – highest score first

/**********************************************************************/

/* Output in Table – solution 1 – for debuging data from database */

/**********************************************************************/

// if data properly selected from guestbook database tabele

echo „<h4>Administration of benchmark result posts</h4>“;

echo „<br>“;

//echo ‚ <button class=“btn btn-secondary btn-lg “ onclick=“location.href=\’unsubscribe.php\'“ type=“button“> Unsubscribe by e-mail -> </button>‘;

echo „<br>“; echo „<br>“;

if($output = mysqli_query($dbc, $sql)){

if(mysqli_num_rows($output) > 0){ // if any record obtained from SELECT query

// create table output

echo „<table>„; //head of table

echo „<tr>„;

echo „<th>id</th>„;

echo „<th>score</th>„;

echo „<th>nickname</th>„;

echo „<th>date of post</th>„;

echo „<th>screenshot</th>„;

echo „</tr>„;

while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop

echo “ <div class=\“mailinglist\“> “ ;

echo „<tr>“;

echo „<td>“ . $row[‚id‘] . „</td>“;

echo „<td>“ . $row[‚score‘] . „</td>“;

echo „<td>“ . $row[‚nickname‘] . „</td>“;

echo „<td>“ . $row[‚write_date‘] . „</td>“;

$image_location = IMAGE_PATH.$row[‚screenshot‘];

echo „<td> <img src=\“$image_location\“ alt=\“ score image \“ height=\“95\“> <td>“;

echo „</tr>“;

// removal line with removing link line

echo „<tr>„;

echo „<td colspan=\„3\„> Manage content: </td>„;
// description on first line

echo ‚<td colspan=“2″>

<a id=“DEL“ href=“remove.php?id=‚.$row[‚id‘] .

‚&score=‚ . $row[‚score‘] .

‚&nickname=‚ . $row[‚nickname‘]

. ‚&write_date=‚ . $row[‚write_date‘]

. ‚&screenshot=‚ . $row[‚screenshot‘] .

‚„> DEL – Remove score </a></td></tr>‚;

//construction of GETable link

// for remove.php input

echo „</tr>“;

echo “ </div> “ ;

}

echo „</table>“;

// Free result set

mysqli_free_result($output);

} else{

echo „There is no benchmark result in chart. Please wirite one.“; // if no records in table

}

} else{

echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem

}

// Close connection

mysqli_close($dbc);

?>

Content of whole admin page can be obtained from github here.

remove.php script

Remove.php script gather data from GET post (link created by admin page pass GET data into a remove.php script). This script obtain GET data and create internal form for verification of a removal score. This form is next submited a with POST send on themself. But not as self script but calling script name without GET url link (no further GET data need to be obtained again).

After confirmation and setting Yes for deletion, POST data are used for database deletion and removing of submitd score pisture located in images folder.

GUI of confirmation looks like this

Confirmation dialog generated by remove.php script using POST method

Full content of code is shown next

<!– ****************************************************************** –>

<!– PHP „self“ code GET request for remove and POST delete data –>

<!– ****************************************************************** –>

<!– Vrsion: 1.0 Date: 27.9-3.10.2020 by CDesigner.eu –>

<!– ****************************************************************** –>

<?php // leading part of page for simple header securing and basic variable setup

require_once(‚appvars.php‘); // including variables for database

require_once(‚authorize.php‘); // authorization script for simple header authorization

// two variables for message and styling of the mesage with bootstrap

$msg = “;

$msgClass = “;

// default values of auxiliary variables

?>

<!– ******************************************* –>

<!– script for appropriate scode removal –>

<!– ******************************************* –>

<!– obtain GET data from admin.php and trough –>

<!– POST submit remove data from database –>

<!– ******************************************* –>

<!DOCTYPE html>

<html>

<head>

<title> Benchmark – admin </title>

</head>

<body>

<a class=“navbar-brand“ href=“admin.php“> 3dmark results chart v 1.0 – part for selected score removal </a>

<a class=“navbar-brand“ href=“index.php“> –> return to main score page</a>

</div>

</nav>

<?php if($msg != “): ?> <!– alert showing part –>

<?php endif; ?>

<h4>Confirmation of deletion selected score script.</h4>

<br>

<br>

<?php // code for GET info about what to remove and submit removing approval

if(isset($_GET[‚id‘]) && isset($_GET[‚score‘]) && isset($_GET[‚nickname‘]) && isset($_GET[‚write_date‘]) && isset($_GET[‚screenshot‘]) ){

// take a data from GET link generated by adminscript

$id = htmlspecialchars($_GET[‚id‘]);

$score = htmlspecialchars($_GET[‚score‘]);

$nickname = htmlspecialchars($_GET[‚nickname‘]);

$write_date = htmlspecialchars($_GET[‚write_date‘]);

$screenshot = htmlspecialchars($_GET[‚screenshot‘]);

} else if (isset($_POST[‚id‘]) && isset($_GET[‚score‘]) && isset($_GET[‚nickname‘])) { //grab score from POST – different behavior for removal

$id = htmlspecialchars($_POST[‚id‘]);

$score = htmlspecialchars($_POST[‚score‘]);

$nickname = htmlspecialchars($_POST[‚nickname‘]);

} else { //error info message

echo ‚<p class=“alert alert-danger“> Please specify any highscore for removal. </p>‘;

};

if(isset($_POST[‚submit‘])){

if($_POST[‚confirm‘] == ‚Yes‘ ){ // delete appropriate score post with imagescreenshot

//delete the screenshotimage from the

$id = htmlspecialchars($_POST[‚id‘]);

$score = htmlspecialchars($_POST[‚score‘]);

$nickname = htmlspecialchars($_POST[‚nickname‘]);

$write_date = htmlspecialchars($_POST[‚write_date‘]);

$screenshot = htmlspecialchars($_POST[‚screenshot‘]);

@unlink(IMAGE_PATH . $screenshot); // delete image file from the storage

// conect to the database

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);

//Delete score data from the database

$sql = „DELETE FROM benchmark_chart WHERE id = $id LIMIT 1″;

// execute SQL

mysqli_query($dbc, $sql);

// close database connection

mysqli_close($dbc);

// confirm executed command

echo ‚<p> The highscore of <strong>‘ . $score . ‚</strong> for <strong>‘ . $nickname . ‚</strong> was succesfully removed. </p>‘;

} else {

echo ‚<p class=“alert alert-danger“ > The highscore was not removed. </p>‘;

}

} else if (isset($id) && isset($nickname) && isset($write_date) && isset($score) && isset($screenshot)) {

echo ‚<h5>Are you sure to delete the next highscore? </h5>‘;

// show short describtion of score for deletion

echo ‚<p> <strong> ID: </strong> ‚ . $id . ‚<br> <strong> Nickname: </strong>‘ . $nickname .

‚<br> <strong> Date: </strong>‘ . $write_date .

‚<br> <strong> Score: </strong>‘ . $score .'</p>‘;

//show submited score image for deletion

$image_location = IMAGE_PATH.$screenshot; // supplementary construct of image path location

echo „<img src=\“$image_location\“ alt=\“ score image to delete \“ height=\“95\“>“;

echo ‚<br><br>‘;

echo ‚<form method=“POST“ action=“remove.php“>‘; //not self but direct this script remove.php – we dont want include any GET data tahat previously send

echo ‚<input type=“radio“ name=“confirm“ value=“Yes“ /> Yes ‚;

echo ‚<input type=“radio“ name=“confirm“ value=“No“ checked=“checked“ /> No <br><br>‘;

echo ‚<input type=“hidden“ name=“id“ value=“‚.$id.'“ />‘;

echo ‚<input type=“hidden“ name=“nickname“ value=“‚.$nickname.'“ />‘;

echo ‚<input type=“hidden“ name=“score“ value=“‚.$score.'“ />‘;

echo ‚<input type=“hidden“ name=“screenshot“ value=“‚.$screenshot.'“ />‘;

echo ‚<input type=“hidden“ name=“write_date“ value=“‚.$write_date.'“ />‘;

echo ‚<input type=“submit“ class=“btn btn-danger“ value=“submit“ name=“submit“ />‘;

echo ‚</form>‘;

};

echo ‚<br><br>‘;

echo ‚<p> <a href = „admin.php“> <&lt Back to admin page. </a></p>‘;

?>

</div>

<a class=“navbar-brand“ href=“https://cdesigner.eu“> Visit us on CDesigner.eu </a>

</div>

</body>

</html>

After sending Yes or No for deletion, there will be shown two different outputs.

Output after chosing No in confirmation page

Output after chosing Yes in confirmation page – with further verification data of succesfull operation

All output pages contain link for return on a admin.php.

Full code location

Full code of application with all updates can be obtained from github here.

Benchmarkchart – php example code – part 2 – submiting score

|

Article describe part of final app responsible for submiting particular score with picture verification. Uploaded image is moved from temporary server location into a images folder.

Behavior of index.php app

Our index.php is responsible for submiting score, nickname, e-mail and verification photo. Location of the photo is stored in a database table but photo is uploaded into a server temp folder and next moved into a images folder.

Handling of display location of uploaded picture take simple javascript as follows:

<label class=“custom-file-label“ for=“customFile“> Screenshot:</label>

</div>

// javascript handling chaging filename of selected file

$(‚input[type=“file“]‘).change(function(e){

var fileName = e.target.files[0].name;

$(‚.custom-file-label‘).html(fileName);

});

</script>

Form for obtaining score data follow

<label>* Please provide Your score:</label>

<input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚score‘]) ? $score : “; ?>'“ name=“score“ class=“form-control“ value=“<?php echo isset($_POST[‚score‘]) ? $score : ‚Your becnhmark score‘; ?>“>

<label>* Please provide Your nickname:</label>

<input type=“text“ onfocus=“this.value='<?php echo isset($_POST[‚nickname‘]) ? $nickname : “; ?>'“ name=“nickname“ class=“form-control“ value=“<?php echo isset($_POST[‚nickname‘]) ? $nickname : ‚Your nickname‘; ?>“>

</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>* Please select location of your score screenshot from drive – max 5MB!</label>

<label class=“custom-file-label“ for=“customFile“>Screenshot:</label>

</div>

$(‚input[type=“file“]‘).change(function(e){

var fileName = e.target.files[0].name;

$(‚.custom-file-label‘).html(fileName);

});

</script>

<label>Optionally – Your score comment:</label> <!– textera for input large text –>

<textarea id=“message_from_submitter“ onfocus=“this.value='<?php echo isset($_POST[‚message_from_submitter‘]) ? $message_from_submitter : ‚Your score escribing text goes here …‘; ?>'“ name=“message_from_submitter“ class=“form-control“ rows=“3″ cols=“50″><?php echo isset($_POST[‚message_from_submitter‘]) ? $message_from_submitter : ‚Your score escribing text goes here …‘; ?></textarea>

</div>

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

</div>

<button type=“submit“ name=“submit“ class=“btn btn-warning“> Submitt score </button>

<button type=“submit“ name=“delete“ class=“btn btn-danger“> Delete recently posted score </button>

<button type=“submit“ name=“reset“ class=“btn btn-info“> Reset form </button>

<?php

echo ‚ <button class=“btn btn-secondary btn-lg “ onclick=“location.href=\’chart.php\'“ type=“button“> Take a look at actual chart -> </button>‘;

?>

<br>

<?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> $score from $nickname </h5> <h5> has been succesfully added to becnhmark chart </h5> „;

$image_location = IMAGE_PATH.$screenshot;

echo “ <img src=\“$image_location\“ alt=\“ score image \“ height=\“150\“> „;

if ($gdpr == true ) { echo „<h5> GDPR accepted </h5>“; } ; //if GDPR rights granted

echo “ <td> </tr> „;

echo “ </table> „;

//echo “ <input type=“text“ id=“result_field“ name=“result_field“ value=“$result“ > <br>“ ;

} ;

?>

<br>

</form>

Last part is responsible for showing uploaded data into a database.

Script responsible for inserting data into database

Next part is responsible for get data from $_POSTasociative array, inserting them into a variable and sending e-mail about succesfull post. Next inserting data into a databasetable and moving uploaded photo.

<?php

require_once(‚appvars.php‘); // including variables for database

// two variables for message and styling of the mesage with bootstrap

$msg = “;

$msgClass = “;

// default values of auxiliary variables

$email = „“;

$nickname = „“;

$screenshot = „“;

$gdpr = false;

$score = ‚0‘;

$message_from_submitter = “;

$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

$nickname = htmlspecialchars($_POST[‚nickname‘]);

$screenshot = htmlspecialchars($_FILES[‚screenshot‘][‚name‘]);

$email = htmlspecialchars($_POST[‚email‘]);

$gdpr = isset($_POST[‚gdpr‘]); // checkbox doesnot send post data, they must be checked for its set state !!!

$score = htmlspecialchars($_POST[‚score‘]);

$message_from_submitter = htmlspecialchars($_POST[‚message_from_submitter‘]);

// Controll if all required fields was written

if(!empty($email) && !empty($nickname) && !empty($score) && !empty($screenshot) && $gdpr ){

// 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 = ‚New submitted score ‚.$nickname.‘ ‚.$score;

$body = ‚<h2>To your becnhmark chart was added new score from:</h2>

<h4>Name</h4><p>‘.$nickname.'</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: “ .$nickname. „<„.$email.“>“. „\r\n“;

// move image to /images final folder from demporary download location

$target = IMAGE_PATH . $screenshot;

// !!! Add entry to the database and redraw all score in chart list descending from highest score

// insert into databse

if (move_uploaded_file($_FILES[‚screenshot‘][‚tmp_name‘], $target)) {

// make database connection

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);

// Check connection

if($dbc === false){

die(„ERROR: Could not connect to database. “ . mysqli_connect_error());

}

// INSERT new entry

$date = date(‚Y-m-d H:i:s‘); // get current date to log into databse along postmessage written

$sql = „INSERT INTO benchmark_chart (nickname, write_date, email, GDPR_accept, screenshot, message_from_submitter, score)

VALUES (‚$nickname‘, now() , ‚$email‘ , ‚$gdpr‘ , ‚$screenshot‘, ‚$message_from_submitter‘, ‚$score‘)“;

if(mysqli_query($dbc, $sql)){

$msg = ‚New score ‚.$score. ‚ from ‚. $nickname. ‚ succesfully added to chart.‘;

$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 benchmark score was sucessfully send via e-mail to page admin.‘;

$msgClass = ‚alert-success‘;

} else {

// Failed

$msg = ‚ Your benchmark was not sucessfully send via e-mail to page admin.‘;

$msgClass = ‚alert-danger‘;

}

} else {

// Failed – if not all fields are fullfiled

$msg = ‚Please fill in all * marked contactform fields‘;

$msgClass = ‚alert-danger‘; // bootstrap format for allert message with red color

}

};

php>

Showing submited scores in a table

Last interesting part of index.php page show resulting score in form a table. Thic code is reused from mailer app but add output for uploaded picture. Database contains name of picture. Global variable contains location of image folder where uploaded image is moved from temporary upload folder.

<?php // code showing all subscribers in form of a table at end of the page

/* Attempt MySQL server connection.*/

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);

// Check connection

if($dbc === false){

die(„ERROR: Could not connect to database – stage of article listing. “ . mysqli_connect_error());

}

// read all rows (data) from guestbook table in „test“ database

$sql = „SELECT * FROM benchmark_chart ORDER BY score DESC“; // read in reverse order of score – highest score first

/***********************************************************************/

/* Output in Table – solution 1 – for debuging data from database */

/***********************************************************************/

echo „<h4>Chart of benchmark results</h4>“;

echo „<br>“;

//echo ‚ <button class=“btn btn-secondary btn-lg “ onclick=“location.href=\’unsubscribe.php\'“ type=“button“> Unsubscribe by e-mail -> </button>‘;

echo „<br>“; echo „<br>“;

if($output = mysqli_query($dbc, $sql)){

if(mysqli_num_rows($output) > 0){ // if any record obtained from SELECT query

// create table output

echo „<table>„; //head of table

echo „<tr>„;

echo „<th>id</th>“;

echo „<th>score</th>„;

echo „<th>nickname</th>„;

echo „<th>date of post</th>“;

echo „<th>screenshot</th>„;

echo „</tr>„;

while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop

echo “ <div class=\“mailinglist\“> “ ;

echo „<tr>„;

echo „<td>“ . $row[‚id‘] . „</td>„;

echo „<td>“ . $row[‚score‘] . „</td>„;

echo „<td>“ . $row[‚nickname‘] . „</td>„;

echo „<td>“ . $row[‚write_date‘] . „</td>„;

$image_location = IMAGE_PATH.$row[‚screenshot‘];

echo „<td> <img src=\“$image_location\“ alt=\“ score image \“ height=\“95\“> <td>„;

echo „</tr>„;

echo “ </div> “ ;

}

echo „</table>„;

// Free result set

mysqli_free_result($output);

} else{

echo „There is no benchmark result in chart. Please wirite one.“; // if no records in table

}

} else{

echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem

}

// Close connection

mysqli_close($dbc);

?>

Final code for study

Complete code for further study with database table creational script can be obtained from github here.

Benchmarkchart – php example code – part 1

|

Article describe basic decomposition of problem for benchmark chart aplication. User can submit benchmark score with photo validation. Admin can remove unvanted or misleading posts. In final stage simple access restricton for access in admin page will be introduced.

Description of behavior of app

Our Benchmark chart app will enable:

visiting user post their own benchmark result obtained from 3dmar timespy benchmark
user provide skore as number, nickname, e-mail (not publick visible for others) and for validation purpose picture from benchmark result
administrator can remove any particular post with admin page containing links leading to remove.php script
remove script will second validate expectation for post removal
separate page shows benchmark chart with highlight for three highest score in database
for security reason simple hardening will be introduced as access verification (username and password) using HTML header verification

Frontend of the APP

Next pictures will show separate pages of application

admin.php page with remove score link leading to remove.php script

remove.php dialog box with confirmation of deletion

output of succesfull removal generated by remove.php script

In our next articles

Following articles will introduce solution for separate pages: index.php – submit score page, admin.php and remove.php for administration posts and removing appropriate unwanted score, authorize.php – restricting access into admin and remove script by header authentification.

Full code for further study and personal implementation can be obtained from github here.

Mailinglist – php example code – part 6 – further small improvements

|

Article focus on small code improvements that can be extended by time. Our firs improvement is separation of database access constants into a appvars.php file. This file is included into a main code with require_once(); PHP function.

Better maintainability of of code is gained by separating all constants on one place. Then they can be invoked by include or require_once() PHP function. For further reading about diferences between these function, please visit as example this page.

File with defined constants, in our case database server access parameters is implemented in all .php files with require_once() statement.

appvars.php code

Next photo show content of mentioned file.

Example of changed parts of other pages follow.

<?php // script for accessing database and first table structure establishement

require_once(‚appvars.php‚); // including variables for database

/* Attempt MySQL server connection. Assuming you are running MySQL

server with (user ‚admin‘ with password test*555) */

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);

… next part omitted

Full application code of mailinglist can be obtained from github here.

Mailinglist – php example code – part 5 – unsubscribe by e-mail for users

|

Article focus on improvement mailinglist app for enabling access for common users only on by e-mail unsubscription without ability to see list of all subscribed users.

Goal of unsubscribe by user app extension

For further security hardening (not main improvement but first partialy update) we separate page for unsubscribing for admin (somebody who knows name of that page – no improvement in this way is done) and for unsubscribing for common user.

Users cannot see list of all subscribers names and email. But there is no way for refering any changes in the table. For better user experience, we expanded messaging output for information about:

that e-mail was found in database table – select query search database for appropriate e-mail
that e-mail was succesfully deleted from databse
or warning message that e-mail was not found (user with this e-mail is not subscribed for mailing)

Frontend of the page after inserting wrong e-mail looks like this

or succesfull e-mail removed output

Main logic of script

Next code snipet contains logic for finding appropriate-mail and show message about succesfull search. Next deleting selected e-mail from subscribtion list.

<?php

// two variables for message and styling of the mesage with bootstrap

require_once(‚appvars.php‘); // including variables for database

$msg = “;

$msgClass = “;

$msg_about_contains_email = “;

$msgClass_email = “;

// default values of auxiliary variables

$email =““;

$is_removed = false; //before hitting submit button no result is available

$is_present = false; // email is not in the table – default before slecting against user submitted email for deletion

if(filter_has_var(INPUT_POST, ‚submit‘)){

// Data obtained from $_postmessage are assigned to local variables

$email = htmlspecialchars($_POST[‚email‘]);

// Controll if all required fields was written

if(!empty($email) ) {

// If check passed – all needed fields are written

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 walid – now delete row with matching e-mail

// make database connection

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);

// Check connection

if($dbc === false){

die(„ERROR: Could not connect to database. “ . mysqli_connect_error());

}

// get info if appropriate e-mail is in mailinglist

// create SELECT query

$sql = „SELECT email FROM mailinglist WHERE email = „.“‚$email'“;

if(($row[‚email‘] = mysqli_fetch_array($result = mysqli_query($dbc, $sql))) != ‚‚){

$msg_about_contains_email = ‚Subscriber with e-mail: ‚.$email. ‚ was found in database for deletion.‘;

$msgClass_email = ‚alert-success‘;

$is_present = true;

// create DELETE query

$sql = „DELETE FROM mailinglist WHERE email = „.“‚$email'“.“ LIMIT 1″;

if(mysqli_query($dbc, $sql)){

$msg = ‚Subscriber with e-mail: ‚.$email. ‚ has been succesfully removed from mailinglist.‘;

$msgClass = ‚alert-success‘;

$is_removed = true;

};

} else{

$msg_about_contains_email = ‚Subscriber with e-mail: ‚.$email. ‚ was not found in database for deletion. Probably was not subscribed for mailing.‘;

$msgClass_email = ‚alert-warning‚;

$msg = „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);

$msgClass = ‚alert-danger‘;

$is_present = false;

};

// end connection

mysqli_close($dbc);

};

} else {

// Failed – if not all fields are fullfiled

$msg = ‚Please fill in all fields‘;

$msgClass = ‚alert-danger‘; // bootstrap format for allert message with red color

};

// if reset button clicked

if(filter_has_var(INPUT_POST, ‚reset‘)){

$msg = “;

$msgClass = “; // bootstrap format for allert message with red color

$subject =“;

$email =“;

$msg_about_contains_email = “;

};

?>

Full code of page usrunsub.php can be obtained from github here.

Mailinglist – php example code – part 4 – unsubscribe by e-mail

|

Article focus on mechanism for unsubscribing users from mailinglist by their e-mails. Because this part is meant to by available for admin, full list of subscribers are shown after all removing action for further look.

Form part

Form part is simplest ever, because sonsist only from one inputfiled gaining e-mail address to usubscribe from mailinglist.

<label>e-mail to unsubscribe:</label>

</div>

<button type=“submit“ name=“submit“ class=“btn btn-warning“> Unsubscribe </button>

Unsubscribe by e-mail php code

For finding and removing unwanted subscribers from a database table is used simple matching e-mail adress.

<?php

// two variables for message and styling of the mesage with bootstrap

$msg = “;

$msgClass = “;

// default values of auxiliary variables

$email =““;

$is_removed = false; //before hitting submit button no result is available

if(filter_has_var(INPUT_POST, ‚submit‘)){

// Data obtained from $_postmessage are assigned to local variables

$email = htmlspecialchars($_POST[‚email‘]);

// Controll if all required fields was written

if(!empty($email) ) {

// If check passed – all needed fields are written

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 valid – now delete row with matching e-mail

// 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());

}

// create DELETE query

$sql = „DELETE FROM mailinglist WHERE email = „.“‚$email'“ ;

if(mysqli_query($dbc, $sql)){

$msg = ‚Subscriber with e-mail: ‚.$email. ‚ has been succesfully removed from mailinglist.‘;

$msgClass = ‚alert-success‘;

$is_removed = true;

// clear entry fields after sucessfull deleting from database

} else {

$msg = „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);

$msgClass = ‚alert-danger‘;

$is_removed = false;

}

// end connection

mysqli_close($dbc);

};

} else {

// Failed – if not all fields are fullfiled

$msg = ‚Please fill in all fields‘;

$msgClass = ‚alert-danger‘; // bootstrap format for allert message with red color

};

// if reset button clicked

if(filter_has_var(INPUT_POST, ‚reset‘)){

$msg = “;

$msgClass = “; // bootstrap format for allert message with red color

$subject =“;

$email =“;

};

?>

Full code of mailinglist app can be obtained from here.

Mailinglist – php example code – part 3 – mailer page

|

Article focus on part responsible for creating a post and resending them to a subscribers. List of subscribers is also shown.

Form part

Sending of separate information messages to subscribers is enabled by mailer.php page. Form part of the page consist from two filed. Simple input text filed for subject. Second much bigger textarea for gaining text of message from page admin.

<label>Subject of send message:</label>

<label>Message to send:</label>

</div>

<button type=“submit“ name=“submit“ class=“btn btn-warning“> Send to subscribers </button>

<button type=“submit“ name=“reset“ class=“btn btn-info“> Reset form </button>

Interesting part of code is inserted in input tag onfocus=“this.value=““ that enable clearing information „value“ text inserted into a form field.

Sending e-mail-s

Part for sending a e-mails is inserted into a HTML body because we will produce messages after all succesfully sent e-mails. Full code can be obtainted for further reference and study from github here.

<?php // if message to send was submitted then emails are sent mail by mail

// Control if data was submitted

if(filter_has_var(INPUT_POST, ‚submit‘)){

// $subject and $message was aded to variables in scrit on upper part of page, because we expect outpu about sending email

// in body of page thic code is inserted in html body part of code

// Controll if all required fields was written

if(!empty($subject) && !empty($message)) {

// If check passed – all needed fields are written

$is_result = true;

// send e-mail to all subscribers

// connect to database

$dbc = mysqli_connect(„localhost“, „admin“, „test*555“, „test“);

// Check connection

if($dbc === false){

die(„ERROR: Could not connect to database. “ . mysqli_connect_error());

}

// read all e-mails from database – create query and pass it to database server

$sql = „SELECT DISTINCT email FROM mailinglist“;

if($output = mysqli_query($dbc, $sql)){

if(mysqli_num_rows($output) > 0){ // if any record obtained from SELECT query

// create and send email one by one

echo „<h4>Sending e-mails</h4>“;

echo „<br>“;

while($row = mysqli_fetch_array($output)){ //send email by email and output message

// create email structure

// E-mail is ok

$fromEmail = ‚ciljak@localhost.org‘; //!!! e-mail address from message is send – change for your needs!!!

$toEmail = $row[‚email‘];

$body = $message;

// Email Headers

$headers = „MIME-Version: 1.0″ .“\r\n“;

$headers .=“Content-Type:text/html;charset=UTF-8″ . „\r\n“;

// Additional Headers

$headers .= „From: CDesigner.eu <„.$fromEmail.“>“. „\r\n“;

if(mail($toEmail, $subject, $body, $headers)){

// Email Sent

echo „<p> Email to: „;

echo “ „ . $row[‚email‘] . “ „;

echo “ has been sent … </p>„;

} else {

// Failed

echo „<p> Email to: „;

echo “ “ . $row[‚email‘] . „ „;

echo “ cannot be send, please examine your email server connection! </p>„;

}

echo „<br>“;

// Free result set – free the memory associated with the result

mysqli_free_result($output);

} else{

echo „There is no subscriber in mailinglist. Please add them.“; // if no records in table

}

} else{

echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem

}

// Close connection

mysqli_close($dbc);

} 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

};

?>

Listener of subscribers part

Our next php code part is responsible for showing list of subscribers in form of a table. For styling of the output, some css was added to style.css file (github link is here).

<?php // code showing all subscribers in form of a table at end of the page

/* Attempt MySQL server connection. Assuming you are running MySQL

server with default setting (user ‚root‘ with no password) */

$dbc = mysqli_connect(„localhost“, „admin“, „test*555“, „test“);

// Check connection

if($dbc === false){

die(„ERROR: Could not connect to database – stage of article listing. “ . mysqli_connect_error());

}

// read all rows (data) from guestbook table in „test“ database

$sql = „SELECT * FROM mailinglist ORDER BY id DESC„; // read in reverse order – newest article first

/*****************************************************************/

/* Output in Table – solution 1 – for debuging data from database */

/*****************************************************************/

// if data properly selected from mailinglist database tabele

echo „<h4>Our subscribers mailinglist</h4>„;

echo „<br>„;

echo ‚ <button class=“btn btn-secondary btn-lg “ onclick=“location.href=\’unsubscribe.php\'“ type=“button“> Unsubscribe by e-mail -> </button>‚;

echo „<br>“; echo „<br>„;

if($output = mysqli_query($dbc, $sql)){

if(mysqli_num_rows($output) > 0){ // if any record obtained from SELECT query

// create table output

echo „<table>“; //head of table

echo „<tr>„;

echo „<th>id</th>„;

echo „<th>firstname</th>„;

echo „<th>lastname</th>„;

echo „<th>date</th>„;

echo „<th>email</th>„;

echo „</tr>„;

while($row = mysqli_fetch_array($output)){ //next rows outputed in while loop

echo “ <div class=\“mailinglist\“> “ ;

echo „<tr>„;

echo „<td>“ . $row[‚id‘] . „</td>„;

echo „<td>“ . $row[‚firstname_of_subscriber‘] . „</td>„;

echo „<td>“ . $row[‚secondname_of_subscriber‘] . „</td>„;

echo „<td>“ . $row[‚write_date‘] . „</td>„;

echo „<td>“ . $row[‚email‘] . „</td>„;

echo „</tr>„;

echo “ </div> “ ;

}

echo „</table>„;

// Free result set

mysqli_free_result($output);

} else{

echo „There is no postmessage in Guestbook. Please wirite one.„; // if no records in table

}

} else{

echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem

}

// Close connection

mysqli_close($dbc);

?>

Full mailinglist app code is available from here.

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.

Mailinglist – php example code – part 1 – app decomposition

|

Article descreibes decomposition of problem for mailinglist app. Users can subscribe for newsletter, grant GDPR. Admin can send mass emails to subscreibers an remove them by e-mail.

Our goals are:

Users of app can subscribe into a mailinglist. Optionaly current subscribtion can be removed just in time. If user will be removed from mailinglist. Then only admin can remove them. (our first demonstration does not solve security at login level – admins only know names of files on server, that is not wery hard solution).
Admin use separate page for writing subject and main message. After wiriting these parts, e-mail are send one by one. For consideration is how to prevent to send duplicate e-mails. Two sulution can be used – UNIQUE keyword for email database field and during queriing database for result DISTINCT for emails.
For better insight in what is goin on are outputed infos about sending e-mails and also list of subscreibers is on bottom part of page. For unsubscribing users is on bottom of the page available button referencing on thirt page of app.
App for unsubscribing users by e-mail for administrator of mailinglist. One field gain email that must be removed from subscription. After submitting appropriate e-mail is removed and new listing of subscribed user is showed for further verification.
Optionaly will by added simplified verion page for unsubscribe user by e-mail. This page does not show list of all subscreibers, only say that appropriate email was found on database and was succesfully removed from them.

Next pictures shows GUI of appropriate page from final mailinglist app:

Subscribtion into mailinglist page

Admin for sending e-mails into a subscreibers

Page for unsubscreibing by an e-mail

In a further articles we will take a closer look at appropriate pages. Current version of mailingapp can be obtained from github here.

Guestbook – php example code

|

This article show php code of simple guestbook with adding post, remove latest post and form reset functionality. All content of article is saved in database.

Guestbook is a simple php application with ability:

Post user commit into guestbook – data are stored in mariadb/ mysql database
Remove latest user post – latest message in form is used for matching database row in DELETE sql query
Reset button reinitialize all displayed messages in space of submit form (upper part of page)

Next picture show final state of our aplication

Basic prerequisities

Before creating our application, we must consider all requirements for data stored in database.

Our database table Guestbook will store:

id (uniqe self incrementing number)
name_of_writer – text up to 30 chars,
write_date – date/ time type generated by script along current time
email – text up to 70 chars,
message_text – large text with minimal 65 535 chars.

For firstime database and table creation was used phpMyAdmin in XAMPP environment.

Setup data for database access are:

server: localhost or 127.0.0.1

database: test

name: admin

password: test*555

Database and user account is created in phpMyAdmin and first result is shown on next picture.

For quick database table creation we prepared php script with name createdatabase.php with content:

<?php // script for accessing database and first table structure establishement

/* Attempt MySQL server connection. Assuming you are running MySQL

server with default setting (user ‚root‘ with no password) */

$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 guestbook(

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

name_of_writer VARCHAR(30) NOT NULL,

write_date DATETIME NOT NULL,

email VARCHAR(70) NOT NULL, /* UNIQUE removed because posts with same e-mails must be anabled */

message_text TEXT

)“;

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

?>

After sucessfull run of script can be obtained message:

Form part of the main application page

Form part consist from input fields and one big textarea for input of text message.

<label>Please provide Your name:</label>

</div>

</div>

<label>Your message for Guestbook:</label> <!– textera for input large text –>

</div>

<button type=“submit“ name=“delete“ class=“btn btn-danger“> Delete latest message </button>

<button type=“submit“ name=“reset“ class=“btn btn-info“> Reset form </button>

<?php //($is_result == „true“) ? {

if ($is_result ) {

echo „<br> <br>“;

echo “ <table class=\“table table-success\“> „;

echo “ <tr>

<td><h5> <em> Yours currently written text is: </em>$postmessage</h5> <td>

</tr> „;

echo “ </table> „;

} ;

?>

<br>

</form>

PHP code for submitted data

After submitting of form data take place these operations:

check of presence data in all fields of form – name, e-mail and message
validation of e-mail
preparation of e-mail to page admin about adding post in to a guestbook
inserting data with INSERT query in to a Gusetbook table of test database
messaging about success or failure during above mentioned operations

This code follow:

// Control if data was submitted

if(filter_has_var(INPUT_POST, ‚submit‘)){

// Data obtained from $_postmessage are assigned to local variables

$name = htmlspecialchars($_POST[‚name‘]);

$email = htmlspecialchars($_POST[‚email‘]);

$postmessage = htmlspecialchars($_POST[‚postmessage‘]);

$is_result = „true“;

// Controll if all required fields was written

if(!empty($email) && !empty($name) && !empty($postmessage)){

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

$toEmail = ‚ciljak@localhost.org‘; //!!! e-mail address to send to – change for your needs!!!

$subject = ‚Guestbook entry from ‚.$name;

$body = ‚<h2>To your Guestbook submitted:</h2>

<h4>Email</h4><p>‘.$email.'</p>

<h4>Message</h4><p>‘.$postmessage.'</p>

‚;

// Email Headers

$headers = „MIME-Version: 1.0″ .“\r\n“;

$headers .=“Content-Type:text/html;charset=UTF-8″ . „\r\n“;

// Additional Headers

$headers .= „From: “ .$name. „<„.$email.“>“. „\r\n“;

// !!! Add entry to the database

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

$date = date(‚Y-m- H:i:s‘); // get current date to log into databse along postmessage written

$sql = „INSERT INTO guestbook (name_of_writer, write_date, email, message_text) VALUES (‚$name‘, ‚$date‘, ‚$email‘ , ‚$postmessage‘)“;

if(mysqli_query($dbc, $sql)){

$msg = ‚postmessage sucessfully added to database.‘;

$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

}

};

PHP code for last entry data delete

Simple delete functionality for current post is creted by removing row witch matching message asi in current submitted article. Solution follow

// if delete button clicked

if(filter_has_var(INPUT_POST, ‚delete‘)){

$msg = ‚Delete last mesage hit‘;

$msgClass = ‚alert-danger‘; // bootstrap format for allert message with red color

//delete from 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());

}

// DELETE last input by matching your written message

// obtain message string for comparison

$postmessage = htmlspecialchars($_POST[‚postmessage‘]);

$postmessage = trim($postmessage); // trim possible leading whitespaces

// create DELETE query

$sql = „DELETE FROM guestbook WHERE message_text = „.“‚$postmessage'“ ;

if(mysqli_query($dbc, $sql)){

$msg = ‚Last message sucessfully removed from database.‘;

$msgClass = ‚alert-success‘;

// clear entry fileds after sucessfull deleting from database

$name =“;

$email =“;

$postmessage = “;

} else {

$msg = „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);

$msgClass = ‚alert-danger‘;

}

// end connection

mysqli_close($dbc);

};

PHP code for form reset

In some case is good way to reset all error messages displayed in form area. Following code is handy

// if reset button clicked

if(filter_has_var(INPUT_POST, ‚reset‘)){

$msg = “;

$msgClass = “; // bootstrap format for allert message with red color

$name = “;

$email = “;

$postmessage = “;

};

Outputting article stored in the database in to a Guestbook

Solution for displaying all post messages stored in a database is this. Use SELECT query SELECT * FROM guestbook ORDER BY id DESC. Last part order data in descending manner for showing latest article as first.

Then store result in output variable and fetch them row by row with while loop as it show next code:

<?php // script for accessing database for all records and then output them in page

/* Attempt MySQL server connection. Assuming you are running MySQL

server with default setting (user ‚root‘ with no password) */

$dbc = mysqli_connect(„localhost“, „admin“, „test*555“, „test“);

// Check connection

if($dbc === false){

die(„ERROR: Could not connect to database – stage of article listing. “ . mysqli_connect_error());

}

// read all rows (data) from guestbook table in test database

$sql = „SELECT * FROM guestbook ORDER BY id DESC„; // read in reverse order – newest article first

/*******************************************************************/

/* Output in form of Article – solution 2 – for Guestbook functionality */ /*******************************************************************/

// if data properly selected from guestbook database table

if($output = mysqli_query($dbc, $sql)){

if(mysqli_num_rows($output) > 0)
{ // if any record obtained from SELECT query

// create Guestbook articles on page

echo „<h4>Our cutomers written into the Guestbook</h4>“;

echo „<br>“;

while($row = mysqli_fetch_array($output)) { //next rows outputed in while loop

// echo „<td>“ . $row[‚id‘] . „</td>“; //id is not important for common visitors

echo “ <div class=\“guestbook\“> “ ;

echo „<h4>“ .“<b>From: </b>“ . $row[‚name_of_writer‘] . „</h4>“;

echo „<h6>“ .“<b>Date of postmessage: </b>“ . $row[‚write_date‘] . „</h6>“;

echo „<h5>“ .“ <b>E-mail of sender: </b>“ . $row[‚email‘] . „</h5>“;

echo „<p id=\“guestbooktext\“>“ . “ <b>Text of the message: </b> <em>“ . $row[‚message_text‘] . „</em></p>“;

//echo „<br>“;

echo “ </div> “ ;

echo “ <div class=\“guestbookbreak\“> “ ;

echo „<br>“;

echo “ </div> “ ;

}

echo „<br>“;

// Free result set – free the memory associated with the result

mysqli_free_result($output);

} else {

echo „There is no postmessage in Guestbook. Please wirite one.“; // if no records in table

}

} else {

echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem

}

// Close connection

mysqli_close($dbc);

?>

Full code for further study can be obtained from github here.