Verify email with code – PHP & MySQL

In this tutorial, we will learn how to verify email with code. When someone registers on your website, send him/her an email with a verification code and show a form to enter the code on your website. The user must enter the verification code in that form to verify his email. Only verified users will be allowed to log in.

Create users table

First, you need to create a “users” table in your database. Your table must have a “verification_code” field that will hold the code sent in the email. And the “email_verified_at” field tells the time the email was verified. This field will also be used to check if the user has verified his email or not.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `password` text NOT NULL,
  `verification_code` text NOT NULL,
  `email_verified_at` datetime DEFAULT NULL
);

User registration

Usually, the registration form has a name, email, and password field for the user.

<form method="POST">
    <input type="text" name="name" placeholder="Enter name" required />
    <input type="email" name="email" placeholder="Enter email" required />
    <input type="password" name="password" placeholder="Enter password" required />

    <input type="submit" name="register" value="Register">
</form>

When this form submits, we need to generate a verification code and send it to the user. To send an email, we are going to use a library called “PHPMailer”. Make sure you have “composer” downloaded and installed in your system. Run the following command at the root of your project:

composer require phpmailer/phpmailer

You can also download and include the PHPMailer library manually from Github. The following code will send a verification code to the user’s email address and save the user’s data in the database:

<?php
    //Import PHPMailer classes into the global namespace
    //These must be at the top of your script, not inside a function
    use PHPMailer\PHPMailer\PHPMailer;
    use PHPMailer\PHPMailer\SMTP;
    use PHPMailer\PHPMailer\Exception;

    //Load Composer's autoloader
    require 'vendor/autoload.php';

    if (isset($_POST["register"]))
    {
        $name = $_POST["name"];
        $email = $_POST["email"];
        $password = $_POST["password"];

        //Instantiation and passing `true` enables exceptions
        $mail = new PHPMailer(true);

        try {
            //Enable verbose debug output
            $mail->SMTPDebug = 0;//SMTP::DEBUG_SERVER;

            //Send using SMTP
            $mail->isSMTP();

            //Set the SMTP server to send through
            $mail->Host = 'smtp.gmail.com';

            //Enable SMTP authentication
            $mail->SMTPAuth = true;

            //SMTP username
            $mail->Username = 'your_email@gmail.com';

            //SMTP password
            $mail->Password = 'your_password';

            //Enable TLS encryption;
            $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;

            //TCP port to connect to, use 465 for `PHPMailer::ENCRYPTION_SMTPS` above
            $mail->Port = 587;

            //Recipients
            $mail->setFrom('your_email@gmail.com', 'your_website_name');

            //Add a recipient
            $mail->addAddress($email, $name);

            //Set email format to HTML
            $mail->isHTML(true);

            $verification_code = substr(number_format(time() * rand(), 0, '', ''), 0, 6);

            $mail->Subject = 'Email verification';
            $mail->Body    = '<p>Your verification code is: <b style="font-size: 30px;">' . $verification_code . '</b></p>';

            $mail->send();
            // echo 'Message has been sent';

            $encrypted_password = password_hash($password, PASSWORD_DEFAULT);

            // connect with database
            $conn = mysqli_connect("localhost:8889", "root", "root", "test");

            // insert in users table
            $sql = "INSERT INTO users(name, email, password, verification_code, email_verified_at) VALUES ('" . $name . "', '" . $email . "', '" . $encrypted_password . "', '" . $verification_code . "', NULL)";
            mysqli_query($conn, $sql);

            header("Location: email-verification.php?email=" . $email);
            exit();
        } catch (Exception $e) {
            echo "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";
        }
    }
?>

User login

When users try to log in, we must check if the user’s email is verified or not. Usually, login form has 2 fields, email, and password:

<form method="POST">
    <input type="email" name="email" placeholder="Enter email" required />
    <input type="password" name="password" placeholder="Enter password" required />

    <input type="submit" name="login" value="Login">
</form>

When this form submits, we will check if the user’s credentials are okay. And also if his/her email is verified.

<?php
    
    if (isset($_POST["login"]))
    {
        $email = $_POST["email"];
        $password = $_POST["password"];

        // connect with database
        $conn = mysqli_connect("localhost:8889", "root", "root", "test");

        // check if credentials are okay, and email is verified
        $sql = "SELECT * FROM users WHERE email = '" . $email . "'";
        $result = mysqli_query($conn, $sql);

        if (mysqli_num_rows($result) == 0)
        {
            die("Email not found.");
        }

        $user = mysqli_fetch_object($result);

        if (!password_verify($password, $user->password))
        {
            die("Password is not correct");
        }

        if ($user->email_verified_at == null)
        {
            die("Please verify your email <a href='email-verification.php?email=" . $email . "'>from here</a>");
        }

        echo "<p>Your login logic here</p>";
        exit();
    }
?>

Email verification

Create a file named “email-verification.php” and create a hidden input field for email and a text field for verification code:

<form method="POST">
    <input type="hidden" name="email" value="<?php echo $_GET['email']; ?>" required>
    <input type="text" name="verification_code" placeholder="Enter verification code" required />

    <input type="submit" name="verify_email" value="Verify Email">
</form>

When this form submits, we will check if the verification code matches the one in the database. If the code does not match then it will show an error, otherwise, it will mark the user as verified.

<?php

    if (isset($_POST["verify_email"]))
    {
        $email = $_POST["email"];
        $verification_code = $_POST["verification_code"];

        // connect with database
        $conn = mysqli_connect("localhost:8889", "root", "root", "test");

        // mark email as verified
        $sql = "UPDATE users SET email_verified_at = NOW() WHERE email = '" . $email . "' AND verification_code = '" . $verification_code . "'";
        $result  = mysqli_query($conn, $sql);

        if (mysqli_affected_rows($conn) == 0)
        {
            die("Verification code failed.");
        }

        echo "<p>You can login now.</p>";
        exit();
    }

?>

Conclusion

Adding this feature to your website helps you to separate real and fake email addresses in your database. It will greatly help you in your marketing and you will be satisfied that your emails are going to real email accounts. If you want to know if your email is being read by the receiver or not, please follow this.

How to check if email is read by user – PHP, Gmail, Outlook

Problem:

If you are developing a web project that includes sending emails to users, then it is very important for you to know if those emails are being read or delivered to those users. For example, if you are sending marketing emails to your subscriber’s list, then it is essential for you to know that your email is read by which users. And by which users the email is not read yet.

This will greatly help you to make decisions if you are sending the right emails or not. Because if the user can read your marketing email, but didn’t buy anything from you, it clearly means there was something wrong with the email. But how do you know if the email sent from PHP, Laravel, or whatever framework you are using, is being opened by the user?

Solution:

Follow this tutorial, and you will be able to add such functionality in your web project.

Sending the email:

For sending the email, you can use the popular “PHPMailer” library. You can also use built-in PHP “mail()” function. Open command prompt or Terminal in your project root folder and run the following command (make sure you have composer installed in your system):

composer require phpmailer/phpmailer

This will create a “vendor” folder in your project root folder. Create a table in your MySQL database named “emails” and it will have 4 columns:

  1. ID (INTEGER) (primary key, auto_increment, not null)
  2. email (TEXT) (not null)
  3. content (TEXT) (not null)
  4. read_at (DATETIME) (null)
How to check if email is read by user - PHP, Gmail, Outlook - Emails table
How to check if email is read by user – PHP, Gmail, Outlook – Emails table

Send email using the following code in your PHP file:

<?php

// import PHPMailer classes into the global namespace
// these must be at the top of your script, not inside a function
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;

// load Composer's autoloader
require 'vendor/autoload.php';

// instantiation and passing `true` enables exceptions
$mail = new PHPMailer(true);

// the person who will receive the email
$recipient = "recipient_email";

// content of email
$content = "This is the HTML message body <b>in bold!</b>";

// connect with database
$conn = mysqli_connect("localhost:8889", "root", "root", "test");

// insert in mails table
$sql = "INSERT INTO emails (email, content) VALUES ('" . $recipient . "', '" . $content . "')";
mysqli_query($conn, $sql);

// get inserted mail ID
$email_id = mysqli_insert_id($conn);

// append empty image tag with email content
// this will help to know when user read that email
$base_url = "http://" . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
$content .= "<img src='" . $base_url . "email_read.php?email_id=" . $email_id . "' style='display: none;' />";

try {
    // disable verbose debug output
    $mail->SMTPDebug = 0;

    // send using SMTP
    $mail->isSMTP();

    // set the SMTP server to send through
    $mail->Host = 'smtp.gmail.com';

    // enable SMTP authentication
    $mail->SMTPAuth = true;

    // SMTP username
    $mail->Username = 'your_email@gmail.com';

    // SMTP password
    $mail->Password = 'your_password';

    // enable TLS encryption
    $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;

    // TCP port to connect to, use 465 for `PHPMailer::ENCRYPTION_SMTPS` above
    $mail->Port = 587;

    $mail->setFrom('your_email@gmail.com', 'Your name');

    // add a recipient
    $mail->addAddress($recipient);

    // set email format to HTML
    $mail->isHTML(true);
    $mail->Subject = 'Here is the subject';
    $mail->Body = $content;

    $mail->send();
    echo 'Message has been sent';
} catch (Exception $e) {
    echo "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";
}

You just need to change the email and password with your Gmail address and password. To learn more about configuring an SMTP server, read this.

[do_widget id=custom_html-4]

This is how the email will be received in the user’s inbox (image will not be visible):

A sample mail
How to check if email is read by user – PHP, Gmail, Outlook – Inbox

View emails and time they are read:

Now, we will create a separate page for the admin where he/she can see a list of all emails sent from the website along with the time they are read by the user. Create a file named “view_emails.php” and paste the following code in it:

<?php

// connect with database
$conn = mysqli_connect("localhost:8889", "root", "root", "test");

// get all emails
$sql = "SELECT * FROM emails";
$result = mysqli_query($conn, $sql);

?>

<!-- basic style of table -->
<style>
    table, td {
        border: 1px solid black;
        border-collapse: collapse;
        padding: 25px;
    }
</style>

<table>

    <?php

    // show all emails
    while ($row = mysqli_fetch_object($result))
    {
        ?>
        <tr>
            <td><?= $row->id; ?></td>
            <td><?= $row->email; ?></td>
            <td><?= $row->content; ?></td>
            <td><?= $row->read_at == null ? "Not read" : $row->read_at; ?></td>
        </tr>
        <?php
    }

    ?>

</table>

Access this page directly and you will see a list of all emails with the time they are read (next step).

How to check if email is read by user - PHP, Gmail, Outlook - Not read
How to check if email is read by user – PHP, Gmail, Outlook – Not read

Mark the time when email is read:

Create a file named “email_read.php” and paste the following code in it:

<?php

// this page will be executed only when the email is opened by user

// connect with database
$conn = mysqli_connect("localhost:8889", "root", "root", "test");

// get email ID
$email_id = $_GET["email_id"];

// update read_at value in emails table
$sql = "UPDATE emails SET read_at = NOW() WHERE id = '" . $email_id . "'";
mysqli_query($conn, $sql);

[do_widget id=custom_html-4]

You will never see the output from this file but it will mark the time in database when the email is read.

How to check if email is read by user – PHP, Gmail, Outlook – Read

Let me know if you had any problem in following this tutorial.

[wpdm_package id=’1170′]

Laravel Blog (Website + Android app) with Admin Panel

A Laravel blog website along with an Android app is created with an admin panel. It uses the design template from https://bootstrapmade.com/. It has the following key features:

Google Adsense approved

The project is tested with Google Adsense and it was approved by Google for monetization. You just have to link with your Google account and you will start receiving money once you reach the Google payment threshold.

User side

  1. 70 built-in blog posts.
  2. Random quotations.
  3. Total users display.
  4. Custom advertisement to generate revenue.
  5. Share posts on Twitter and Facebook.
  6. Limit access to some features for registered users only.
  7. Registration with Email Verification.
  8. Secure Login.
  9. Comment on Post.
  10. Reply to the comment.
  11. Related Posts.
  12. Subscribe to the newsletter.
  13. Social Links.
  14. A section to sell items directly.
  15. Amazon affiliate links.
  16. Realtime Chat with admin (Firebase).
  17. Manage Profile.
  18. Change Password.
  19. Custom Advertisement.

Admin panel

  1. Dashboard Statistics.
  2. Add/Edit blog posts.
  3. Add/Edit items that sell directly.
  4. Manage Inbox.
  5. Manage Comments.
  6. Realtime Chat with users (Firebase).

Android app

We also developed an Android App for this project which your users can download from Google Play Store and read your blog posts from that app. Here is the demo of the Laravel blog android app:

Our TrustPilot reviews

TrustPilot-reviews
TrustPilot-reviews

Realtime customer support chat widget – PHP, Javascript, MySQL, Node JS

In this article, we are going to teach you how you can create a real-time customer support chat widget on your website. Users will be able to chat with the admin and get instant replies. All messages will be stored in the MySQL database.

By real-time, we mean that users or admins do not have to refresh the page to see new messages. Also, the admin will be able to view the navigation history of the user. For example, how many and which pages the user has visited, what was the last page the user has visited etc.

Tutorial:

Source code:

After buying this project, you will get the complete source code and use it in as many websites as you want.

Our Trustpilot reviews

TrustPilot-reviews
TrustPilot-reviews

Feedback pop-up bootstrap modal – Javascript, PHP & MySQL

In this article, we will teach you how to create a feedback pop-up bootstrap modal for users on the bottom right of the screen when the page is fully loaded. The pop-up modal will be created in bootstrap and will display a star rating and an input field to get the user’s feedback. Once submitted, it will display a thank you message and the user’s feedback will be saved in a database along with his IP address and browser information. If you are working on localhost, you might see the IP address as ::1. Don’t worry about it, on the live server it will save the actual IP address of the user.

Once the feedback is sent, the user will not see that pop-up again. If the user changes the browser or visits the site after a long time, then he will see that pop-up again.

Download jQuery, bootstrap, and font-awesome

First, you need to download jquery, bootstrap, and font-awesome. You can download all of them from the attachment below. Paste the CSS and JS files of bootstrap into your project. You may also need to copy-paste the jQuery JS file as well. Make sure to copy the font awesome fonts and CSS file as well.

Now you need to include these files in your project. Create <link> tag for adding CSS files and <script> tag for adding JS files.

<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css" />
<link rel="stylesheet" type="text/css" href="css/font-awesome.min.css" />

<script src="js/jquery-3.3.1.min.js"></script>
<script src="js/bootstrap.min.js"></script>

Refresh the page, and if you did not see any error in the browser’s console window, then it means that it is included correctly.

Feedback modal/pop-up

Now, create a bootstrap modal for feedback. Give your modal a unique ID that will be used to show and hide the modal programmatically. The modal will have a heading, and one button to close the modal. And a body, where we will create the form to get the feedback.

<div class="modal custom" id="feedbackModal">
    <div class="modal-dialog">
        <div class="modal-content">

            <div class="modal-header">
                <h3>Rate your feedback</h3>
                <button type="button" class="close" data-dismiss="modal">
                    <span>×</span>
                </button>
            </div>

            <div class="modal-body">
                <form onsubmit="return saveFeedback(this);">
                    <div class='starrr'></div>

                    <div class="form-group" style="margin-top: 10px;">
                        <input type="text" name="feedback" class="form-control" />
                    </div>

                    <input type="submit" class="btn btn-primary pull-right" value="Submit" />
                </form>
            </div>
        </div>
    </div>
</div>

On form submit we will call an AJAX to save the feedback. A <div> to show stars, an input field to enter feedback in text, and a submit button. Right now, you will not see the stars, it will be done in the next section.

We need to show the modal on the bottom right, so apply some CSS styles to it.

#feedbackModal.modal.custom .modal-dialog {
    width: 50%;
    position: fixed;
    bottom: 0;
    right: 0;
    margin: 0;
}

This will give it some width for the form, set the position on the bottom right, and remove the margin from the bottom and right of the screen.

Star ratings (starrr)

Now, to create stars, we are going to use a library called starrr. Goto, this GitHub link to download the library and download it in your system. Inside this library, go to the distribution folder named dist and copy the CSS and JS files in your project’s CSS and JS folder separately. Now include its CSS and JS files and make sure to include the JS file after jQuery. I am putting it even after bootstrap JS.

<link rel="stylesheet" type="text/css" href="css/starrr.css" />
<script src="js/starrr.js"></script>

If you still see the stars missing, even though you have included font-awesome in your project, it is because you need to initiate this library using Javascript.

var ratings = 0;

window.addEventListener("load", function () {
    $(".starrr").starrr().on("starrr:change", function (event, value) {
        ratings = value;
    });

    if (localStorage.getItem("feedback_1") == null) {
        $("#feedbackModal").modal("show").on('hidden.bs.modal', function (e) {
            localStorage.setItem("feedback_1", "1");
        });
    }
});

Setting the default value of ratings to 0. We will initiate the library when the page is fully loaded. When the user selects any star, we are going to save its value in the ratings variable. Now I need to show the modal automatically but only if the user has not given his feedback. So I will use local storage for this purpose. In line #8, I am checking if the local storage has feedback_1 value. If not, then I am going to show the modal, and when this modal is closed by the user, I am going to save the feedback_1 value in local storage. So next time the user visits this page, the feedback_1 value will be found in local storage, thus it will not show the modal pop-up.

Refresh the page and now you will see that a feedback pop-up, that is created via a bootstrap modal, will be displayed automatically.

Submit form using AJAX

Now, we need to create a Javascript function named saveFeedback() which will be called when the form is submitted. This function will create an AJAX object, set its method to POST, and URL to the page that will save the feedback, and make the request asynchronous.

Then attach an event that will be called whenever the state of request is changed. The ready state will be 4 when the request is completed and a response is received. The status will be 200 if the response was OK and there was no error. You can simply show the response sent from the server using the responseText property. Then show the thank you message in the modal pop-up, and save the value in local storage.

If the request’s status is 500, it means there is an internal server error. In that case, you can view the error using the responseText property. To send the AJAX request, you need to create a FormData object using your form and append the ratings variable value in it, and then send the request and attach the form data object to it. return false at the end of the function will prevent the form from submitting and refreshing the page.

function saveFeedback(form) {
    var ajax = new XMLHttpRequest();
    ajax.open("POST", "save-feedback.php", true);

    ajax.onreadystatechange = function () {
        if (this.readyState == 4) {
            if (this.status == 200) {
                console.log(this.responseText);

                document.querySelector("#feedbackModal .modal-body").innerHTML = "Thank you for your feedback.";
                localStorage.setItem("feedback_1", "1");
            }

            if (this.status == 500) {
                console.log(this.responseText);
            }
        }
    };

    var formData = new FormData(form);
    formData.append("ratings", ratings);
    ajax.send(formData);

    return false;
}

Handle AJAX request

Now create a server file named save-feedback.php that will handle this request. As the requests need to be saved in the database, so we need to create a table in our database. I am creating a table named feedbacks. It will have 6 columns (id, IP, browser, ratings, feedback, created_at). The first will be auto increment ID. The second is an IP address whose data type is TEXT. Third is browser information, also TEXT. The fourth is ratings” data type DOUBLE. Next is feedback, also TEXT. And finally, created_at will store the date and time when the feedback is sent and its data type will be DATETIME.

You can find the SQL file in the attachment below.

Save feedback in MySQL

First, connect with the database. My database name is “tests”. Then get ratings and feedback from the request. You can get the user’s IP address using the PHP built-in global $_SERVER variable, and the associative index will be REMOTE_ADDR. You can get the user’s browser information by calling a PHP built-in function named get_browser() and it has a property named browser_name_pattern. Then run the INSERT query to save the record in the database. MySQL has a built-in function named NOW() that will return the current date and time of the server. PHP echo will send the response back to the client.

<?php

    $conn = mysqli_connect("localhost:8889", "root", "root", "test");

    $ratings = $_POST["ratings"];
    $feedback = $_POST["feedback"];
    $ip = $_SERVER["REMOTE_ADDR"];

    $browser = get_browser()->browser_name_pattern;

    mysqli_query($conn, "INSERT INTO `feedbacks`(`ip`, `browser`, `ratings`, `feedback`, `created_at`) VALUES ('" . $ip . "', '" . $browser . "', '" . $ratings . "', '" . $feedback . "', NOW())");

    echo "Done";

?>

Explanation

Initially, the feedback table will be empty. When you refresh the page, give ratings using stars, enter feedback and hit submit button, then a “Thank you” message will be displayed. When the response is successfully received from the server, close the modal and check your “feedbacks” table using phpMyAdmin. You will see a new row created in the database. If you refresh the page again, you won’t be able to see this modal because you have already given your feedback. You can try it in another browser. The first time you will see the pop-up on the bottom right corner. Once feedback is given, then you will not see that pop-up again. And your ratings will be saved in the database.

That’s how you can create a bootstrap modal that will be used to collect feedback from the users using a pop-up.

[wpdm_package id=’1030′]

PayPal and Stripe – Javascript

Whether you are creating an E-commerce website or simply wanted to receive payments from your website using PayPal and Stripe, this script already contains all the source code you need to show Stripe and PayPal payment buttons and receive payments directly in your Stripe and PayPal accounts.

It also has a full shopping cart implementation, where you can add products to the cart, update quantities, remove products from the cart and move to checkout.

Demo

Home page

Here you can add products to the shopping cart and also you can remove products from the shopping cart.

When you add or remove a product from the shopping cart, you will see the shopping cart badge on top updating its values in real-time.

Shopping cart

On clicking the above “Cart” button you will be redirected to the shopping cart page where you can change the number of products and can also remove the product from the shopping cart.

You can also see the grand total at the bottom right that changes in real-time as you change the quantity of product or when you remove a product from the shopping cart.

Checkout

Here you can make the payment using Stripe or Paypal.

Fully documented code

Comments have been added with each line of code for explanation.

Separate file for each function

To manage the code, each payment method is separated in its own file. So you can easily change and modify the code as per your needs.

Logged in devices management – PHP & MySQL

In this article, we will be creating logged in devices management feature that will allow the users to check how many devices they have been logged in. You might have seen this feature in Facebook where you can see a list of all devices where you have been logged in, and you also have the capability to remove device. The devices which has been removed will no longer be logged in and had to be logged in by entering the email and password again.

Following are the steps to manage multiple devices/browser logins:

  • When user enter correct email and password during login, we will check if current device and browser is trusted by that user.
  • If it is not trusted, then it will send an email with a verification code to user’s email address and an input field is displayed.
  • User have to enter the verification code in that input field.
  • Once the code is verified, then the user will be asked to trust this device/browser or not.
  • If user trusts the device, then the next time he tried to login, he won’t be asked for a verification code.
  • A separate page is created to show a list of all devices where user is logged in.
  • From that page, user can remove the device he wants.
  • When the device/browser is removed, then if the user tried to login from that device/browser, then a new verification code will be sent again on his email address.

Table of content:

  1. Database structure
  2. Login form
  3. Login form submission
  4. Verification code
  5. Trust device
  6. Show all logged in devices
  7. Remove device

1. Database structure

First we will create 2 tables, one for users and one for devices. You might already have a table for users, if you do, make sure to add a new column verification_code in it. If you already have a table for users, you can add the verification_code column by running the following query:

/* if you already have the users table */
ALTER TABLE users ADD COLUMN verification_code TEXT NOT NULL

If you don’t have the users table, then run the following query in your phpMyAdmin or you can create the tables manually.

CREATE TABLE IF NOT EXISTS users(
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    email TEXT NOT NULL,
    password TEXT NOT NULL,
    verification_code TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS devices(
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    user_id INTEGER NOT NULL,
    browser_info TEXT NOT NULL,
    browser_token TEXT NOT NULL,
    last_login DATETIME NOT NULL,
    last_login_location TEXT NOT NULL,
    
    CONSTRAINT fk_devices_user_id FOREIGN KEY (user_id) REFERENCES users(id)
);
  • user_id will be a foreign key from users table.
  • browser_info will store the browser and operating system name e.g. Safari Mac OS X.
  • browser_token this will be stored as a cookie in browser to identify the browser.
  • last_login this will tell the time when that device was last logged in.
  • last_login_location this will tell the user’s location from where the login happened.
  • fk_devices_user_id is the name of constraint we use to set the primary key of ID from users table as a foreign key in devices table.

2. Login form

<form method="POST">

    <table>
        <tr>
            <td>
                Email
            </td>
            <td>
                <input type="email" name="email">
            </td>
        </tr>

        <tr>
            <td>
                Password
            </td>

            <td>
                <input type="password" name="password">
            </td>
        </tr>
    </table>
 
    <input type="submit" value="Login" name="login">
</form>

This will create an email and password field along with a submit button labeled as “Login”. You might already have one similar to this, actual change will be in the next step.

3. Login form submission

When the form is submitted, we will check it’s credentials and make sure they are right. Then we will check if this device or browser is trusted by user, if not, then we will send an email to the user to verify this device. If yes, then he will be redirected to devices page where he can see all his devices.

To send an email, we are going to use PHPMailer, you can learn how to integrate PHPMailer by following this.

<?php

// session start is required for login
session_start();

// connecting with database
$conn = mysqli_connect("localhost", "db_username", "db_userpassword", "db_name");

// check if the form is submitted
if (isset($_POST["login"]))
{
    // get input field values, preventing from SQL injection
    $email = mysqli_real_escape_string($conn, $_POST["email"]);
    $password = mysqli_real_escape_string($conn, $_POST["password"]);
      
    // check if the email exists in database
    $sql = "SELECT * FROM users WHERE email = '" . $email . "'";
    $result = mysqli_query($conn, $sql);
 
    if (mysqli_num_rows($result) == 0)
    {
        echo "In-correct email";
        exit();
    }
    else
    {
        // check if the password is correct, we are using hashed password
        $row = mysqli_fetch_object($result);
        if (password_verify($password, $row->password))
        {
            // store the user in session
            $_SESSION["user"] = $row;

            // check if the device or browser is trusted or not
            $sql = "SELECT * FROM devices WHERE browser_token = '" . $_COOKIE["browser_token"] . "' AND user_id = '" . $row->id . "'";
            $result = mysqli_query($conn, $sql);

            // device/browser is trusted
            if (mysqli_num_rows($result) > 0)
            {
                header("Location: devices.php");
            }
            else
            {

                // not trusted, send an email.
                // generate a unique verification code
                $verification_code = uniqid();

                // Instantiation and passing `true` enables exceptions
                $mail = new PHPMailer(true);

                try {
                    //Server settings
                    $mail->SMTPDebug = 0;                      // Enable verbose debug output
                    $mail->isSMTP();                                            // Send using SMTP
                    $mail->Host       = 'smtp.gmail.com';                    // Set the SMTP server to send through
                    $mail->SMTPAuth   = true;                                   // Enable SMTP authentication
                    $mail->Username   = 'your_email';                     // SMTP username
                    $mail->Password   = 'your_password';                               // SMTP password
                    $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;         // Enable TLS encryption; `PHPMailer::ENCRYPTION_SMTPS` encouraged
                    $mail->Port       = 587;                                    // TCP port to connect to, use 465 for `PHPMailer::ENCRYPTION_SMTPS` above

                    //Recipients
                    $mail->setFrom('your_email', 'your_name');
                    $mail->addAddress($email);

                    // Content
                    $mail->isHTML(true);                                  // Set email format to HTML
                    $mail->Subject = 'Verify this browser';
                    $mail->Body    = 'Your verification code is <b style="font-size: 30px;">' . $verification_code . '</b>';

                    $mail->send();
                    // echo 'Message has been sent';
                } catch (Exception $e) {
                    die("Message could not be sent. Mailer Error: {$mail->ErrorInfo}");
                }

                // save the verification code in users table
                $sql = "UPDATE users SET verification_code = '" . $verification_code . "' WHERE id = '" . $_SESSION["user"]->id . "'";
                mysqli_query($conn, $sql);

                ?>

                <!-- show a form to enter verification code from email -->

                <h1>New device detected. Email has been sent with a verification code.</h1>

                <form method="POST">

                    <table>
                        <tr>
                            <td>
                                Verification code
                            </td>
                            <td>
                                <!-- verification code input field -->
                                <input type="text" name="verification_code">
                            </td>
                        </tr>
                    </table>
                 
                    <!-- submit button -->
                    <input type="submit" value="Verify" name="verify">
                </form>

                <?php
            }
        }
        else
        {
            echo "Invalid password";
            exit();
        }
    }
}

Once the email is sent, you will see the verification code field value in that user’s row in database. You will also see an input field labeled as “Enter verification code”, here you need to enter the coode received in your email address.

Note: If you do not receive an email, make sure you have entered correct email and password in PHPMailer and also your Gmail’s account less secure apps option should be enabled. You can enable it from here.

4. Verification code

Now when the verification form is submitted, we will do the following:

  • Check if the verification code entered in form matches with the one in database.
  • If matches, then we will empty the verification code field from users table.
  • And show a form to trust this device/browser or not.
<?php

if (isset($_POST["verify"]))
{
    $user_id = isset($_SESSION["user"]) ? $_SESSION["user"]->id : 0;
    $verification_code = $_POST["verification_code"];

    $sql = "SELECT * FROM users WHERE id = '" . $user_id . "' AND verification_code = '" . $verification_code . "'";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) == 0)
    {
        die("Verification code has been expired.");
    }

    $sql = "UPDATE users SET verification_code = '' WHERE id = '" . $user_id . "' AND verification_code = '" . $verification_code . "'";
    mysqli_query($conn, $sql);

?>

    <form method="POST">
        <input type="button" onclick="window.location.href = 'devices.php';" value="Don't trust this device">
        <input type="submit" name="trust_device" value="Trust device">
    </form>

<?php
}

If user presses “Don’t trust this device”, then user will again receive the verification code next time he tried to login in this browser. We are simply redirecting the user to devices page where he will see all his logged in devices.

5. Trust device

Now if the user presses the button to “Trust device”, then we will do the following:

  • Generate a unique ID, store it in browser’s cookies.
  • Get browser info (browser name, device type and platform). You need to uncomment browscap line in your php.ini to use this feature.
  • Get user’s IP address to get his location using Geo plugin. If it is not working from localhost, then you need to place your IP address manually, you can get your IP address from Google.
  • From Geo plugin, we are getting country and city name.
  • Finally we will insert that data in devices table, thus next time you will login with same device, it will not ask for verification code.

So our logged in devices management feature will keep track of all trusted devices.

<?php

if (isset($_POST["trust_device"]))
{
    $browser_token = uniqid();
    setcookie("browser_token", $browser_token);

    $browser = get_browser(null, true);
    $browser_info = $browser["browser"] . " " . $browser["device_type"] . " " . $browser["platform"];

    $user_ip = getenv('REMOTE_ADDR');

    $geo = unserialize(file_get_contents("http://www.geoplugin.net/php.gp?ip=$user_ip"));
    $country = $geo["geoplugin_countryName"];
    $city = $geo["geoplugin_city"];
    $last_login_location = $country . ", " . $city;

    $sql = "INSERT INTO devices (user_id, browser_info, browser_token, last_login, last_login_location) VALUES ('" . $_SESSION["user"]->id . "', '" . $browser_info . "', '" . $browser_token . "', NOW(), '" . $last_login_location . "')";
    mysqli_query($conn, $sql);

    header("Location: devices.php");
}

?>

You will be redirected to file named “devices.php“. Now we need to show all logged in devices to user so he can remove if he want.

6. Show all devices

Create a file named devices.php and paste the following code in it:

<?php

// devices.php

// session start is required for login
session_start();

// connecting with database
$conn = mysqli_connect("localhost", "db_username", "db_userpassword", "db_name");

// check if the user is logged in
if (!isset($_SESSION["user"]))
{
    die("Not logged in");
}

// paste the remove device code here from next step

// get all devices of logged in user
$sql = "SELECT * FROM devices WHERE user_id = '" . $_SESSION["user"]->id . "'";
$result = mysqli_query($conn, $sql);

?>

Apply some CSS to make the table look good.

table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
th, td {
    padding: 15px;
}

Show all devices data in tabular form:

<!-- table to show all devices data -->
<table>
    <tr>
        <th>Device info</th>
        <th>Last login</th>
        <th>Last location</th>
        <th>Actions</th>
    </tr>

    <!--  table row for each device -->
    <?php while ($row = mysqli_fetch_object($result)): ?>
        <tr>
            <td><?php echo $row->browser_info; ?></td>

            <!-- last login date in readable format -->
            <td><?php echo date("d M, Y H:i:s A", strtotime($row->last_login)); ?></td>
            <td><?php echo $row->last_login_location; ?></td>
            <td>
                <!-- form to remove the device -->
                <form method="POST">
                    <input type="hidden" name="id" value="<?php echo $row->id; ?>">
                    <input type="submit" name="remove_device" value="Remove device">
                </form>
            </td>
        </tr>
    <?php endwhile; ?>
</table>

The code is self-explanatory in comments. Now we need to add a function to remove device. We have already displayed a form with a submit button which when clicked should remove the device from user’s logged in devices list and should not allow that device to login without verification.

7. Remove device

Now we will simply remove the selected device from devices table for logged in user:

<?php

// check if form is submitted
if (isset($_POST["remove_device"]))
{
    // get device ID
    $id = $_POST["id"];

    // remove from database
    $sql = "DELETE FROM devices WHERE user_id = '" . $_SESSION["user"]->id . "' AND id = '" . $id . "'";
    mysqli_query($conn, $sql);

    // success message
    echo "Device has been removed.";
}

?>

So you have successfully created your logged in devices management feature. Try integrating it in one of your existing project. And let us know if you face any problem.

[wpdm_package id=’832′]

Admin roles in admin panel – PHP & MySQL

Let’s say you have an admin panel of your website where you can manage your website’s data. Now you want to have a functionality where you can create sub-admins with access to limited features. For example, one admin can manage posts (add, edit and delete), another admin can manage customers, another admin can manage employees and so on. And they all will be managed by super admin. So we need to assign different admin roles to each admin.

In this article, we will be creating a sub admin to manage posts.

Create database table

Create a table for admins where we will have a column named “roles”, it’s type will be ENUM so you can specify the roles. No roles other than specified in ENUM will be accepted.

CREATE TABLE `admins` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `email` text NOT NULL,
  `password` text NOT NULL,
  `role` enum('all','manage_posts') NOT NULL
);

CREATE TABLE `posts` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `title` text NOT NULL,
  `created_by` int(11) NOT NULL,
  CONSTRAINT `fk_created_by_posts` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
);

Insert data in MySQL database

Super admin will be created manually and for once only. Give the role “all” to super admin:

INSERT INTO `admins` (`id`, `email`, `password`, `role`) VALUES
(1, 'admin@gmail.com', '$2y$10$e0qHrQw8irU1TPxjzfB2OOAQ/uUH/xq5jAP58f796jMAOLwEv2d9i', 'all')

Admin login form

You can generate password hash from here. First we will create a login form for all admins:

<?php
    // start session and connect with database
    session_start();
    $conn = mysqli_connect("localhost", "root", "root", "tutorials");
?>

<!-- check if admin is logged in -->
<?php if (isset($_SESSION["admin"])): ?>
    <!-- button to logout -->
    <p>
        <a href="?logout">Logout</a>
    </p>
<?php else: ?>
    <!-- form to login -->
    <form method="POST">
        <p>
            <input type="email" name="email" placeholder="Enter email" required>
        </p>

        <p>
            <input type="password" name="password" placeholder="Enter password" required>
        </p>

        <p>
            <input type="submit" name="login" value="Login">
        </p>
    </form>
<?php endif; ?>

This will show a button to logout if the admin is logged in and a login form if an admin is not logged in.

Log in the admin and start his session

Now we will write the code to login the admin and start his session:

// check if request is for login
if (isset($_POST["login"]))
{
    // get email and password
    $email = $_POST["email"];
    $password = $_POST["password"];

    // check if email exists
    $result = mysqli_query($conn, "SELECT * FROM admins WHERE email = '" . $email . "'");
    if (mysqli_num_rows($result) > 0)
    {
        // check if password is correct
        $admin = mysqli_fetch_object($result);
        if (password_verify($password, $admin->password))
        {
            // start session
            $_SESSION["admin"] = $admin;
            echo "<p>Logged in.</p>";
        }
        else
        {
            echo "<p>Wrong password.</p>";
        }
    }
    else
    {
        echo "<p>Email not found.</p>";
    }
}

This will first check if the email exists in the database. Then it will compare the hashed password with plain text from input field. If credentials are okay then it will save the admin object in session variable.

Logout admin

Now you will see the logout button.

// check if request is for logout
if (isset($_GET["logout"]))
{
    // remove from session and redirect back
    unset($_SESSION["admin"]);
    header("Location: " . $_SERVER["HTTP_REFERER"]);
}

When the logout button is clicked, we will remove this admin object from session variable and redirect the admin to the page where he came from. There are other methods to redirect the user to previous page and you will check those methods from here.

Add sub-admin form

Now if the logged-in admin is super admin then we will show him a form to add a new admin:

<!-- check if main admin -->
<?php if ($_SESSION["admin"]->role == "all"): ?>

    <!-- add admin form -->
    <h1>Add admin</h1>
    <form method="POST">
        <p>
            <input type="email" name="email" placeholder="Enter email" required>
        </p>

        <p>
            <input type="password" name="password" placeholder="Enter password" required>
        </p>

        <p>
            <label>Enter role</label>
            <select name="role" required>
                <option value="all">All</option>
                <option value="manage_posts">Manage posts</option>
            </select>
        </p>

        <p>
            <input type="submit" name="add_admin" value="Add admin">
        </p>
    </form>
<?php endif; ?>

This will ask for the admin’s email and password along with the role that you want to assign to him.

INSERT sub-admin in MySQL database

Now we will write the code to save his data in the database:

// check if request is for adding admin
if (isset($_POST["add_admin"]))
{
    // check if main admin
    if (isset($_SESSION["admin"]) && $_SESSION["admin"]->role == "all")
    {
        // get values
        $email = $_POST["email"];
        $password = password_hash($_POST["password"], PASSWORD_DEFAULT);
        $role = $_POST["role"];

        // check if email already exists
        $result = mysqli_query($conn, "SELECT * FROM admins WHERE email = '" . $email . "'");
        if (mysqli_num_rows($result) > 0)
        {
            echo "<p>Email already exists.</p>";
        }
        else
        {
            // save in database
            mysqli_query($conn, "INSERT INTO admins (email, password, role) VALUES ('" . $email . "', '" . $password . "', '" . $role . "')");
            echo "<p>Admin has been added.</p>";
        }
    }
    else
    {
        echo "<p>Sorry, you cannot perform this action.</p>";
    }
}

First, this will check that the logged-in admin must have an access to create sub-admin. Then it will get all the fields, it also converts the plain text password into hashed string. Then it checks if an admin with same email already exists, if not then it saves the data in database and display a success message.

Show all sub-admins to super admin

Now we need to show all sub-admins to super admin so he can know all his sub-admins along with their roles, and also an ability to delete any sub-admin. Below code should be written after the “Add admin” form:

<?php
    // show all admins
    $all_admins = mysqli_query($conn, "SELECT * FROM admins WHERE id != '" . $_SESSION["admin"]->id . "'");
    while ($admin = mysqli_fetch_object($all_admins)):
?>
    <p>
        <?php echo $admin->email . " - " . $admin->role; ?>

        <!-- button to delete admin -->
        <form method="POST" onsubmit="return confirm('Are you sure you want to delete ?');">
            <input type="hidden" name="id" value="<?php echo $admin->id; ?>">
            <input type="submit" name="delete_admin" value="Delete">
        </form>
    </p>
    <hr>
<?php endwhile; ?>

This will show all sub-admins to super admin only. When the delete button is clicked, it will first ask for confirmation. When confirm, it will submit the form. Now we need to handle the form submission in PHP:

// check if request is for deleting admin
if (isset($_POST["delete_admin"]))
{
    // check if main admin
    if (isset($_SESSION["admin"]) && $_SESSION["admin"]->role == "all")
    {
        // get value
        $id = $_POST["id"];

        // delete from database
        mysqli_query($conn, "DELETE FROM admins WHERE id = '" . $id . "'");
        echo "<p>Admin has been deleted.</p>";
    }
    else
    {
        echo "<p>Sorry, you cannot perform this action.</p>";
    }
}

This will simply check that the logged-in admin must be a super admin. Then it will delete the admin from database. When a sub-admin is deleted, all his created posts will also be deleted as well. If you want the sub-admin posts to stay after his removal, you need to remove the “ON DELETE CASCADE ON UPDATE CASCADE” clause from “posts” table during creation.

Sub-admins

Now we come to the sub-admin part. Sub admins can perform action based on their roles. For example, sub admin having role “manage_posts” can create, edit and delete posts. First we will create a form to add post:

<!-- check if admin has permission to manage posts -->
<?php if ($_SESSION["admin"]->role == "all" || $_SESSION["admin"]->role == "manage_posts"): ?>
    <!-- form to add new post -->
    <h1>Add post</h1>
    <form method="POST">
        <p>
            <input type="text" name="title" placeholder="Enter title" required>
        </p>

        <p>
            <input type="submit" name="add_post" value="Add post">
        </p>
    </form>
<?php endif; ?>

This will check that the logged-in admin must either be a super admin or admin having role “manage_posts”. Now we need to handle its request in PHP:

// check if request is for adding post
if (isset($_POST["add_post"]))
{
    // check if admin has permission to manage posts
    if (isset($_SESSION["admin"]) && ($_SESSION["admin"]->role == "all" || $_SESSION["admin"]->role == "manage_posts"))
    {
        // get values
        $title = $_POST["title"];
        $created_by = $_SESSION["admin"]->id;

        // save in database
        mysqli_query($conn, "INSERT INTO posts (title, created_by) VALUES ('" . $title . "', '" . $created_by . "')");
        echo "<p>Post has been added.</p>";
    }
    else
    {
        echo "<p>Sorry, you cannot perform this action.</p>";
    }
}

We need to validate the sub-admin role in server side as well. Get all fields from input fields, and logged in admin ID so we can know which sub-admin created that post. Then we will insert the data in database.

Now we need to show all posts of sub-admin created by him so he can perform further actions like updating or deleting post.

<?php
    // get all posts
    $all_posts = mysqli_query($conn, "SELECT * FROM posts WHERE created_by = '" . $_SESSION["admin"]->id . "'");
    while ($post = mysqli_fetch_object($all_posts)):
?>
    <p>
        <?php echo $post->title; ?>

        <!-- button to delete post -->
        <form method="POST" onsubmit="return confirm('Are you sure you want to delete ?');">
            <input type="hidden" name="id" value="<?php echo $post->id; ?>">
            <input type="submit" name="delete_post" value="Delete">
        </form>
    </p>
    <hr>
<?php endwhile; ?>

This will fetch all posts created by logged-in admin from database and display their titles along with a button to delete. When the delete form is submitted, it will ask for confirmation, once confirm it will submit the form. Now we need to handle the form request on server side:

// check if request is for deleting post
if (isset($_POST["delete_post"]))
{
    // check if admin has permission to manage posts
    if (isset($_SESSION["admin"]) && ($_SESSION["admin"]->role == "all" || $_SESSION["admin"]->role == "manage_posts"))
    {
        // get value
        $id = $_POST["id"];

        // check if post is created by logged in admin
        $result = mysqli_query($conn, "SELECT * FROM posts WHERE id = '" . $id . "' AND created_by = '" . $_SESSION["admin"]->id . "'");
        if (mysqli_num_rows($result) == 0)
        {
            echo "<p>Sorry you cannot perform this action.</p>";
        }
        else
        {
            // delete from database
            mysqli_query($conn, "DELETE FROM posts WHERE id = '" . $id . "' AND created_by = '" . $_SESSION["admin"]->id . "'");
            echo "<p>Post has been deleted.</p>";
        }
    }
    else
    {
        echo "<p>Sorry, you cannot perform this action.</p>";
    }
}

Again it will check if the logged-in admin is either super admin or has a role to manage posts. Additionally, it will also check if the post he is trying to delete is created by him. That’s how it will be secured. If all validations are passed then it will simply delete the post from database.

That’s how you can create multiple admin roles and assign the roles by admin capability.

[wpdm_package id=’833′]

Comments and replies – PHP & MySQL

Demo

In this article, we will teach you how you can implement a comments and replies feature in your website. If you are running a blog, or have a portfolio or a ticket booking website. The list goes on, you want to have an option to gather the opinions, reviews and suggestions from your users. For example, I upload computer programming related educational content. So I want to know if my users are following my articles ? Do they understand them well ? Which articles are difficult to understand, which are good, which can be improved etc.

We are going to create a table which can hold all the comments of any post, a post can be anything, blog post/product/movie/celebrity etc. There will be a single table which can save the comments as well as each comments replies. User can add a comment to a post and can also reply to a comment. When you add a reply to a comment, then an email is also sent to the person to whom you have replied.

Create comments table

Run the following query in your phpMyAdmin in your database to create a table named “posts” and “comments“:

CREATE TABLE IF NOT EXISTS posts(
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS comments (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    comment TEXT NOT NULL,
    post_id INTEGER NOT NULL,
    created_at DATETIME NOT NULL,
    reply_of INTEGER NOT NULL,
    CONSTRAINT fk_comments_post_id FOREIGN KEY (post_id) REFERENCES posts(id)
)

This will create a table named “comments” in your database if not exists. It will have unique ID, name, email and comment. post_id will be the ID of post where comment is added because user can comment on each post. created_at will be the date and time when the comment is added. reply_of will be the value of comment whom user has replied. When you add a comment directly on the post then it’s value will be 0, if you add a reply on any comment then it’s value will be the ID of comment whom you are replying. That is why it is not added as a foreign key because it’s value can be null. Finally, we are adding a foreign key constraint to our post_id field because it will be the ID of post whom comment is added.

Add comment form

Our comment form will have name, email, comment, a hidden post ID field and a submit button. You can design this as per your website color scheme. The hidden input field will be the post unique ID, make sure to enter your dynamic post ID in $post_id variable.

<?php
    // make sure you have a post ID 1 in your "posts table"
    $post_id = 1;
?>

<form action="index.php" method="post">

    <input type="hidden" name="post_id" value="<?php echo $post_id; ?>" required>

    <p>
        <label>Your name</label>
        <input type="text" name="name" required>
    </p>

    <p>
        <label>Your email address</label>
        <input type="email" name="email" required>
    </p>

    <p>
        <label>Comment</label>
        <textarea name="comment" required></textarea>
    </p>

    <p>
        <input type="submit" value="Add Comment" name="post_comment">
    </p>
</form>

Form method is POST, action will be the name of file where data will be sent to process. We are sending to index.php but you can change the name of file as per your need. Then we are creating all fields, make sure to give name attribute to all the fields including the submit button. It will help the server to check if the request is made from specific form and to get the values from these input fields.

Save comment in database

Following code will check if the form is submitted, validate all fields from SQL injection and save in database.

<?php

// index.php

$conn = mysqli_connect("localhost:8889", "root", "root", "yourdbname");

if (isset($_POST["post_comment"]))
{
    $name = mysqli_real_escape_string($conn, $_POST["name"]);
    $email = mysqli_real_escape_string($conn, $_POST["email"]);
    $comment = mysqli_real_escape_string($conn, $_POST["comment"]);
    $post_id = mysqli_real_escape_string($conn, $_POST["post_id"]);
    $reply_of = 0;

    mysqli_query($conn, "INSERT INTO comments(name, email, comment, post_id, created_at, reply_of) VALUES ('" . $name . "', '" . $email . "', '" . $comment . "', '" . $post_id . "', NOW(), '" . $reply_of . "')");
    echo "<p>Comment has been posted.</p>";
}

?>

First we are connected with database, you can change the user, password and database name as per your project. Then we are checking if the request is made from add comment’s form. Then we are validating all fields to prevent from SQL injection using PHP built-in mysqli_real_escape_string function. We are setting the reply_of value to 0 because as mentioned earlier, this field will have 0 value if the comment is added directly on the post. It will have value greater than zero only for replies.

Then we are running the query to insert the data in comments table. To set the value in created_at field we are using MySQL built-in NOW() function. This will return the current date and time in proper format Y-m-d H:i:s. Finally a success message is displayed that the comment has been posted.

Show all comments

Now we need to fetch all comments from database in following format:

[
    {
        "id": 1,
        "name": "ali ahmad",
        "email": "aliahmad@gmail.com",
        "comment": "nice post",
        "post_id": 3,
        "created_at": "2020-09-16 20:09:44",
        "reply_of": 0,
        "replies": [
            {
                "id": 2,
                "name": "ali ahmad",
                "email": "aliahmad@gmail.com",
                "comment": "thanks",
                "created_at": "2020-09-16 20:09:44",
                "post_id": 3,
                "reply_of": 1
            }
        ]
    }
]

Pay close attention to this format. We have an array of comments, each object has a unique ID, name, email, comment, post_id, created_at, reply_of and replies. Now if you explore the “replies” array you will see that it has the same object as comment’s except for the replies array. The reply_of value in replies array is same as the ID of comment. You will understand it better once we finish the replies feature.

Show all comments

The following code will generate the data structure as above, you can put that code where you want to display all comments of a post:

<?php

// connect with database
$conn = mysqli_connect("localhost:8889", "root", "root", "tutorials");

// get all comments of post
$result = mysqli_query($conn, "SELECT * FROM comments WHERE post_id = " . $post_id);

// save all records from database in an array
$comments = array();
while ($row = mysqli_fetch_object($result))
{
    array_push($comments, $row);
}

// loop through each comment
foreach ($comments as $comment_key => $comment)
{
    // initialize replies array for each comment
    $replies = array();

    // check if it is a comment to post, not a reply to comment
    if ($comment->reply_of == 0)
    {
        // loop through all comments again
        foreach ($comments as $reply_key => $reply)
        {
            // check if comment is a reply
            if ($reply->reply_of == $comment->id)
            {
                // add in replies array
                array_push($replies, $reply);

                // remove from comments array
                unset($comments[$reply_key]);
            }
        }
    }

    // assign replies to comments object
    $comment->replies = $replies;
}

?>

Do not forgot to replace the post_id in the SQL query. Rest of the code is self-explanatory. If you write the following command after the top foreach loop then it will show the same data structure as above:

print_r($comments);

But right now the replies array will be empty because right now we added comment to a post but we havn’t added any reply to a comment. Time to display all these comments, again you can design as per your desire but for the sake of simplicity we are creating basic layout.

<ul class="comments">
    <?php foreach ($comments as $comment): ?>
        <li>
            <p>
                <?php echo $comment->name; ?>
            </p>

            <p>
                <?php echo $comment->comment; ?>
            </p>

            <p>
                <?php echo date("F d, Y h:i a", strtotime($comment->created_at)); ?>
            </p>

            <div data-id="<?php echo $comment->id; ?>" onclick="showReplyForm(this);">Reply</div>

            <form action="index.php" method="post" id="form-<?php echo $comment->id; ?>" style="display: none;">
                
                <input type="hidden" name="reply_of" value="<?php echo $comment->id; ?>" required>
                <input type="hidden" name="post_id" value="<?php echo $post_id; ?>" required>

                <p>
                    <label>Your name</label>
                    <input type="text" name="name" required>
                </p>

                <p>
                    <label>Your email address</label>
                    <input type="email" name="email" required>
                </p>

                <p>
                    <label>Comment</label>
                    <textarea name="comment" required></textarea>
                </p>

                <p>
                    <input type="submit" value="Reply" name="do_reply">
                </p>
            </form>
        </li>
    <?php endforeach; ?>
</ul>

This will display all comments in an un-ordered list in such a way that you can see the name and comment of person, the date and time when the comment was posted, a button to reply. You see that we also created a form tag but immediately hides it using CSS style attribute. This form will only be visible when you click on the “Reply” button. You can see the reply button has a data-id attribute which has the value of comment ID, it is the same as the ID attribute of form. This will help us show the form only for that comment.

Hidden input fields

The form has 2 hidden input fields, reply_of means the ID of comment whom I am replying and second will be the post ID. Other fields are same as previous comment form (name, email, comment). Make sure your reply button inside the form has name attribute different to the comment’s form from previous section. For example, previously we had given name=”post_comment” but in-case of reply, we are setting name=”do_reply”. This will help us run separate code for comments and replies because in-case of reply, we have to send an email to the commenter so that he can know that someone has replied to his comment.

Add Reply to a comment

In the previous section, you can see that we added an onclick event listener to the reply button. Now we need to create it’s function in Javascript:

<script>

function showReplyForm(self) {
    var commentId = self.getAttribute("data-id");
    if (document.getElementById("form-" + commentId).style.display == "") {
        document.getElementById("form-" + commentId).style.display = "none";
    } else {
        document.getElementById("form-" + commentId).style.display = "";
    }
}

</script>

First it will get the comment ID using data-id attribute of reply button. Then it will check if the form is visible, if visible then it will hide the form and if hidden then it will make it visible. This will work like a toggle.

Now the form is displayed, time to process it. When the reply form is submitted, we will save it’s record in database as we are doing with normal comment but we will set it’s reply_of value to the ID of comment. Moreover, we will send an email to the person whom you are replying.

<?php

if (isset($_POST["do_reply"]))
{
    $name = mysqli_real_escape_string($conn, $_POST["name"]);
    $email = mysqli_real_escape_string($conn, $_POST["email"]);
    $comment = mysqli_real_escape_string($conn, $_POST["comment"]);
    $post_id = mysqli_real_escape_string($conn, $_POST["post_id"]);
    $reply_of = mysqli_real_escape_string($conn, $_POST["reply_of"]);

    $result = mysqli_query($conn, "SELECT * FROM comments WHERE id = " . $reply_of);
    if (mysqli_num_rows($result) > 0)
    {
        $row = mysqli_fetch_object($result);

        // sending email
        $headers = 'From: YourWebsite <no-reply@yourwebsite.com>' . "\r\n";
        $headers .= 'MIME-Version: 1.0' . "\r\n";
        $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
        
        $subject = "Reply on your comment";

        $body = "<h1>Reply from:</h1>";
        $body .= "<p>Name: " . $name . "</p>";
        $body .= "<p>Email: " . $email . "</p>";
        $body .= "<p>Reply: " . $comment . "</p>";

        mail($row->email, $subject, $body, $headers);
    }

    mysqli_query($conn, "INSERT INTO comments(name, email, comment, post_id, created_at, reply_of) VALUES ('" . $name . "', '" . $email . "', '" . $comment . "', '" . $post_id . "', NOW(), '" . $reply_of . "')");
    echo "<p>Reply has been posted.</p>";
}

?>

First we are checking if the request received is for adding reply. Then we are protecting all input fields from SQL injection including the hidden fields. Then we are getting the comment’s row whom reply is being adding. By fetching the comment’s row from database, we can get the email address of the commenter and thus can easily send an email using PHP built-in mail() function. If you are working on localhost then mail() function might not work, in this case you are use PHPMailer. A library that allows you to send emails even from localhost, learn how to use this.

Finally we are running the INSERT query to save the record in database. And a success message is displayed as well. Now you will see that your $comments array has a nested replies array as well. Time to display the replies for each comment.

Show replies of each comment

Inside the $comments array foreach loop, after the form tag is ended, paste the following code to show all replies:

<ul class="comments reply">
    <?php foreach ($comment->replies as $reply): ?>
        <li>
            <p>
                <?php echo $reply->name; ?>
            </p>

            <p>
                <?php echo $reply->comment; ?>
            </p>

            <p>
                <?php echo date("F d, Y h:i a", strtotime($reply->created_at)); ?>
            </p>

            <div onclick="showReplyForReplyForm(this);" data-name="<?php echo $reply->name; ?>" data-id="<?php echo $comment->id; ?>"> Reply</div>
        </li>
    <?php endforeach; ?>
</ul>

This will show all replies for each comment in same format as we are displaying top-level comments. It will again have a reply button but this will not be a “reply of a reply“, because it might go on to an infinite level and will not look good in design either. That is why we will again show a form and his reply will be appended in the list.

Show reply form

The reply button has data-id and data-name attributes that represents the ID of comment and name of person whom you are replying. This is useful if you want to show the name of person with @ sign in the reply textarea field.

You will notice that the function called in onclick event is different than the previous, this is because we are going to show @ sign in reply textarea field. Create the following function in your javascript:

<script>

function showReplyForReplyForm(self) {
    var commentId = self.getAttribute("data-id");
    var name = self.getAttribute("data-name");

    if (document.getElementById("form-" + commentId).style.display == "") {
        document.getElementById("form-" + commentId).style.display = "none";
    } else {
        document.getElementById("form-" + commentId).style.display = "";
    }

    document.querySelector("#form-" + commentId + " textarea[name=comment]").value = "@" + name;
    document.getElementById("form-" + commentId).scrollIntoView();
}

</script>

First we are getting comment ID and name of person using data attributes. Then we are displaying the form if hidden and hiding the form if already visible. Then we are prepending @ sign in textarea field and writing commenter’s name in it. The last line scrolls the page to the form, this helps because the form is created at the top of each comment and if you try to reply to a 1000th person then it will automatically scroll to the form tag.

That’s how you can create a comments and replies section in your PHP website.

The template used in the demo is a premium template named “porto”. So it’s source code is not included in the code below. But all the functional code is in there.

Movie ticket booking website – PHP and MySQL

A movie ticket booking website is created in PHP and MySQL following the principles of Object-Oriented Programming (OOP) and Model-View-Controller (MVC). Google Adsense supported. CSRF (Cross-site Request Forgery) is protected.

FeaturesFreePremium
User authenticationYesYes
Admin loginYesYes
Add, edit, and delete categoriesYesYes
Add, edit, and delete moviesYesYes
Add, edit, and delete cinemasYesYes
Add, edit, and delete celebritiesYesYes
Set cast of each movieYesYes
Show currently playing movies in all cinemasYesYes
Show biography and filmography of celebritiesYesYes
Book tickets onlineNoYes
SeasonsNoYes
Receive payments from Stripe & PayPalNoYes

Demo

Free version:

https://drive.google.com/file/d/16fmMA5tDe6VqzhDmJ_KvwvaDvrojKscJ/view?usp=sharing

Feature list:

  1. Admin login.
  2. Add, Edit, and Delete categories.
  3. CRUD operation on movies.
  4. Add, Edit, Delete cinemas.
  5. Play movies in cinemas.
  6. Add, Edit, Delete celebrities.
  7. Add cast in movies.
  8. Show currently playing movies in all cinemas.
  9. Login and registration.
  10. User home.
  11. Movie detail.
  12. Show biography and filmography of celebrities.
  13. Seasons
  14. Receive payments online (Stripe & PayPal)

How to setup:

Our TrustPilot reviews

TrustPilot-reviews
TrustPilot-reviews