PHP CRUD with Authentication

In last couple of posts, we discussed about CRUD and authentication using PHP and MySQL. Our example was an Address Book application. You can check back those posts here and here. Now its the time to combine the two and create a practical example.

CRUD and authentication are very common tasks in web development. Most of web applications today use some form of CRUD and also need a login or authentication system.

We will not discuss the code in detail here, as we did it already in previous blogs. You can however read the comments for basic understanding or revert back to above linked previous blogs for detail explanation. The system requirements are as under:

A user should register to use the system. Once logged in, every user can have their own address book. The addition, updating and deletion of records are independent of other users.

Our main steps to develop the system will be as under:

Create Database and Table
Create Config and CSS Files
Create User Registration
Create Login and Logout
Create Home Page
Create New Addresses
Update Addresses
Delete Addresses
Password Change

Create Database and Table

CREATE DATABASE crud_auth_db;
USE crud-auth_db;
CREATE TABLE auth_table (
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username varchar(50) NOT NULL,
    password varchar(255) NOT NULL,
    email VARCHAR(100),
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE address_table (
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    uid int NOT NULL,
    name varchar(100) NOT NULL,
    address varchar(255),
    phone VARCHAR(50),
    email VARCHAR(50)
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

Create Config and CSS Files

<?php
// config.php
$mysql_server = "localhost";
$mysql_user = "root";
$mysql_password = "";
$mysql_db_name = "crud_auth_db";
// Create connection with mysql
$conn = mysqli_connect($mysql_server, $mysql_user, $mysql_password, $mysql_db_name);
// Check the connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
  }

// menu.php
<ul>
    <li><a class="active" href="index.php">Home</a></li>
    <li><a href="create.php">Add New Record</a></li>
    <li style="float:right"><a href="logout.php">Logout</a></li>
    <li style="float:right"><a href="pass_reset.php">Settings</a></li>
</ul>

?>

/* styles.css  */
body {
    font-family: Arial, Helvetica, sans-serif;
}
h1, h2, h3   {
    text-align:center;
}
#h1left   {
    text-align:left;
}
table{
    width: 80%;
    margin: 30px auto;
    border-collapse: collapse;
    text-align: left;
}
tr {
    border-bottom: 1px solid #cbcbcb;
}
th, td{
    border: 1px solid #ddd;
    padding: 8px;
}
tr:hover {
    background: #F5F5F5;
}
th {
    padding-top: 12px;
    padding-bottom: 12px;
    text-align: left;
    background-color: #04AA6D;
    color: white;
}
form {
    width: 50%;
    margin: auto auto;
    padding: 24px; 
    border: 2px solid #ccc;
    border-radius: 4px;
}
#loginform {
    width: 30%;
    margin: auto auto;
    padding: 24px; 
    border: 1px solid #ccc;
    border-radius: 4px;
}
input, textarea {
    width: 100%;
    padding: 10px;
    box-sizing: border-box;
    border: 2px solid #ccc;
    border-radius: 4px;
    background-color: #f8f8f8;
}
label {
    padding: 5px 5px 5px 0;
    display: inline-block;
}
input[type=submit] {
    background-color: #04AA6D;
    color: white;
    padding: 12px 20px;
    border: none;
    border-radius: 4px;
    cursor: pointer;
    width: 100px;
    margin-top: 15px;
}
input[type=submit]:hover {
    background-color: #45a049;
}
button {
    background-color: #04AA6D;
    color: white;
    padding: 5px 5px;
    border: none;
    border-radius: 4px;
    cursor: pointer;
}
button:hover {
    background-color: #45a049;
}
.alert {
    width: 30%;
    padding: 10px;
    background-color: #f44336;
    color: white;
    border: none;
    border-radius: 4px;
}
.alert.success {background-color: #04AA6D;}
.alert.info {background-color: #2196F3;}
.alert.warning {background-color: #ff9800;}
ul {
    list-style-type: none;
    margin: 0;
    padding: 0;
    overflow: hidden;
    border: 1px solid #e7e7e7;
    background-color: #f3f3f3;
}
li {
    float: left;
}
li a {
    display: block;
    color: #666;
    text-align: center;
    padding: 14px 16px;
    text-decoration: none;
}
li a:hover:not(.active) {
    background-color: #ddd;
}
li a.active {
    color: white;
    background-color: #04AA6D;
}

Create User Registration

<html lang="en">
<head>
    <link rel="stylesheet" href="styles.css">
    <title>User Registration</title>
</head>
<body>
    <!-- HTML form for user input. Form submits to itself to execute PHP code bellow -->
    <form id="loginform" action="<?php ($_SERVER["PHP_SELF"]); ?>" method="post">
        <div class="container">
            <h1 id="h1left">Register</h1><hr>
            <label for="form_username">User Name</label>
            <input type="text" id="form_username" name="form_username" required>
            <label for="form_password">Password</label>
            <input type="password" id="form_password" name="form_password" required>
            <label for="form_password_repeat">Repeat Password</label>
            <input type="password" id="form_password_repeat" name="form_password_repeat" required>
            <label for="form_email">Email Address</label>
            <input type="email" id="form_email" name="form_email" required>
            <input type="submit" value="Register">
            <p>Already have Account? <a href=login.php>Login</a></p>
        </div>
    </form>
</body>
</html>
<?php  
    // Include the config file
    include('config.php');
    // If form submits and method is POST
    if($_SERVER["REQUEST_METHOD"] == "POST"){
        // Get form session variables
        $input_username = $_POST["form_username"];
        //$input_password = $_POST["form_password"];
        $input_password = password_hash($_POST["form_password"], PASSWORD_DEFAULT);
        $input_email = $_POST["form_email"];
        $sql = "Select * from auth_table where username='$input_username'";
        $result = mysqli_query($conn, $sql);
         
        if (mysqli_num_rows($result) == 1) {
            echo "<center><div class=alert>This username already exists. Please choose another username</div></center>";
        } else {
            if ($_POST["form_password"] == $_POST["form_password_repeat"]) {
                // Insert the variables's data into table
                $sql = "INSERT INTO auth_table (username, password, email)
                VALUES ('$input_username', '$input_password', '$input_email')";
                // Execute the query
                if(mysqli_query($conn, $sql)){
                    // On success, redirect to landing page
                    header("location: login.php");
                    //echo "Done.";
                    exit();
                } else{
                    // If faild, throw an error
                    echo "<center><div class=alert>Something went wrong. Please try again later</div></center>";
                }
            }
            else {
                echo "<center><div class=alert>Password and Repeat Password don't match</div></center>";
            }
        }
    }
// Close mysql connection
mysqli_close($conn);
?>

Create Login and Logout

<?php
// login.php
session_start();
if(isset($_SESSION['loggedin']) && $_SESSION['loggedin'] == true){
    header("location: index.php");
    exit;
}
?>
<html lang="en">
<head>
    <link rel="stylesheet" href="styles.css">
    <title>User Login</title>
</head>
<body>
    <!-- HTML form for user input. Form submits to itself to execute PHP code bellow -->
    <form id="loginform" action="<?php ($_SERVER["PHP_SELF"]); ?>" method="post">
        <div class="container">
            <h1 id="h1left">Login</h1><hr>
            <label for="form_username">User Name</label>
            <input type="text" id="form_username" name="form_username">
            <label for="form_password">Password</label>
            <input type="password" id="form_password" name="form_password">
            <input type="submit" value="Login">
            <p>No Account? <a href="register.php">Signup for an Account</a></p>
        </div>
    </form>
</body>
</html>
<?php  
    // Include the config file
    include('config.php');
    // If form submits and method is POST
    if($_SERVER["REQUEST_METHOD"] == "POST"){
        // Get form session variables
        $input_username = $_POST["form_username"];
        $input_password = $_POST["form_password"];
        $sql = "Select * from auth_table where username='$input_username'";
        $result = mysqli_query($conn, $sql);
         
        if (mysqli_num_rows($result) == 1) {
            while($row=mysqli_fetch_assoc($result)){
                if (password_verify($input_password, $row['password'])){ 
                    session_start();
                    $_SESSION['loggedin'] = true;
                    $_SESSION['userid'] = $row['id'];
                    $_SESSION['username'] = $input_username;
                    header("location: index.php"); 
                } 
                else{
                    echo "<center><div class=alert>Invalid Password</div></center>";
                }
            }
        } 
        else{
            echo "<center><div class=alert>Invalid User</div></center>";
        }
    }
// Close mysql connection
mysqli_close($conn);
?>

<?php
// logout.php
// Initialize the session
session_start();
  
// Destroy the session.
session_destroy();
  
// Redirect to login page
header("location: login.php");
exit;
?>

Create Home Page

<?php
session_start();
if(!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] != true){
    header("location: login.php");
    exit;
}
include('config.php');
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <link rel="stylesheet" type="text/css" href="styles.css">
  <script>
    // Get confirmation before deleting
    function confirm_delete(){
      if(confirm("Are you sure you want to delete?") === true){
        return true;
      } else {
        return false;
      }
    }
</script>
  <title>Home</title>
</head>
<body>
<?php
  include('menu.php');
// Select fields
$sql = "SELECT * FROM address_table where UID =" . $_SESSION['userid'];
// Execute query
$result = mysqli_query($conn, $sql);
// Show Fields
if (mysqli_num_rows($result) > 0) {
?>
<!-- Create a HTML table to format the output -->
  <table cellspacing="0" cellpadding="1" border="1">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Address</th>
        <th>Phone</th>
        <th>EMail</th>
        <th>Actions</th>
      </tr>
<?php
    // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "<tr>";
        echo "<td>" . $row["id"] . "</td>";
        echo "<td>" . $row["name"] . "</td>";
        echo "<td>" . $row["address"] . "</td>";
        echo "<td>" . $row["phone"] . "</td>";
        echo "<td>" . $row["email"] . "</td>";
        // Links to update and delete file by passing table "id" fieled
        echo "<td>";
        echo "<a href=update.php?id=" . $row["id"] . "><button type=button>Update</button></a>&nbsp;"; 
        echo "<a href=delete.php?id=" . $row["id"] . "><button type=button Onclick='return confirm_delete();'>Delete</button></a>";
        echo "</td>";
    echo "</tr>";
  }
  echo "</table>";
} else {
  echo "<br><center><div class='alert warning'><strong>No record found</strong></div></center>";
}
// Close mysql connection
mysqli_close($conn);
?>
</body>
</html>

Create New Addresses

<?php
session_start();
if(!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] != true){
    header("location: login.php");
    exit;
}
include('config.php');
?>
<html lang="en">
<head>
    <link rel="stylesheet" href="styles.css">
    <title>Create Record</title>
</head>
<body>
    <?php
        include('menu.php');
    ?>
    <br>
    <!-- HTML form for user input. Form submits to itself to execute PHP code bellow -->
    <form action="<?php ($_SERVER["PHP_SELF"]); ?>" method="post">
        <div class="container">
            <h1 id="h1left">Add Address</h1><hr>
            <label for="form_name">Name</label>
            <input type="text" id="form_name" name="form_name">
            <label for="form_address">Address</label>
            <textarea id="form_address" name="form_address"></textarea>
            <label for="form_phone">Phone</label>
            <!-- Input type tel for phone number with formating (3 numbers - 3 numbers - 4 numbers) -->
            <input type="tel" id="form_phone" name="form_phone" pattern="[0-9]{3}-[0-9]{3}-[0-9]{4}">
            <small>Format: 123-456-7890</small><br>
            <!-- Input type email for validation -->
            <label for="form_email">Email</label>
            <input type="email" id="form_email" name="form_email">
            <input type="submit">
        </div>
    </form>
</body>
</html>
<?php  
    // If form submits and method is POST
    if($_SERVER["REQUEST_METHOD"] == "POST"){
        // Get form session variables
        $input_name = $_POST["form_name"];
        $input_address = $_POST["form_address"];
        $input_phone = $_POST["form_phone"];
        $input_email = $_POST["form_email"];
        $input_uid = $_SESSION['userid'];
        // Insert the variables's data into table
        $sql = "INSERT INTO address_table (uid, name, address, phone, email)
            VALUES ('$input_uid', '$input_name', '$input_address', '$input_phone', '$input_email')";
        // Execute the query
        if(mysqli_query($conn, $sql)){
            // On success, redirect to landing page
            header("location: index.php");
            exit();
        } else{
            // If faild, throw an error
            echo "Something went wrong. Please try again later.";
        }
    }
// Close mysql connection
mysqli_close($conn);
?>

Update Addresses

<?php
session_start();
if(!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] != true){
    header("location: login.php");
    exit;
}
// Include style sheet file
echo "<style>";
include('styles.css');
echo "</style>";
// Include the config file
include('config.php');
// Get record id from landing page link
$id=$_GET["id"];
// Select the field to be updated
$sql = "SELECT * FROM address_table where id=$id";
// execute the query
$result = mysqli_query($conn, $sql);
// Get the row
$row = mysqli_fetch_assoc($result);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Update Record</title>
</head>
<body>
    <?php
        include('menu.php');
    ?>
    <br>
    <!-- HTML form with old data. Form submits to itself to execute PHP code bellow -->
    <form action="<?php ($_SERVER["PHP_SELF"]); ?>" method="post">
        <div class="container">
            <h1 id="h1left">Update Address</h1><hr>
            <label for="form_name">Name</label>
            <input type="text" id="form_name" name="form_name" value="<?php echo $row["name"]; ?> ">
            <label for="form_address">Address</label>
            <textarea id="form_address" name="form_address"><?php echo $row["address"]; ?></textarea>
            <label for="form_phone">Phone</label>
            <!-- Input type tel for phone number with formating (3 numbers - 3 numbers - 4 numbers) -->
            <input type="tel" id="form_phone" name="form_phone" pattern="[0-9]{3}-[0-9]{3}-[0-9]{4}" value="<?php echo $row["phone"]; ?>">
            <small>Format: 123-456-7890</small><br>
            <!-- Input type email for validation -->
            <label for="form_email">Email</label>
            <input type="text" id="form_email" name="form_email" value="<?php echo $row["email"]; ?> ">
            <input type="submit">
        </div>
    </form>
</body>
</html>
<?php  
    // If form submits and method is POST? Get form data into variables
    if($_SERVER["REQUEST_METHOD"] == "POST"){
        $input_name = $_POST["form_name"];
        $input_address = $_POST["form_address"];
        $input_phone = $_POST["form_phone"];
        $input_email = $_POST["form_email"];
        // Update record with new changed data
        $sql = "UPDATE address_table SET name='$input_name', address='$input_address', phone='$input_phone', email='$input_email' where id='$id'";
        // Execute the query
        if(mysqli_query($conn, $sql)){
            // If successfull, redirect to landing page
            header("location: index.php");
            exit();
        } else{
            // If faild, throw an error
            echo "Something went wrong. Please try again later.";
        }
    }
// Close mysql connection
mysqli_close($conn);
?>

Delete Addresses

<?php
session_start();
if(!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] != true){
    header("location: login.php");
    exit;
}
include('config.php');
// Get record id from landing page link
$id=$_GET["id"];
// Delete field query
$sql = "DELETE FROM address_table where id=$id";
if (mysqli_query($conn, $sql)) {
    header("location: index.php");
  } else {
    echo "<center><div class=alert>Error deleting record: " . mysqli_error($conn) . "</div></center>";
  }
// Close mysql connection
mysqli_close($conn);
?>

Password Change

<?php
session_start();
if(!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] != true){
    header("location: login.php");
    exit;
}
?>
<html lang="en">
<head>
    <link rel="stylesheet" href="styles.css">
    <title>Change Password</title>
</head>
<body>
    <?php
        include('menu.php');
    ?>
    <br>
    <!-- HTML form for user input. Form submits to itself to execute PHP code bellow -->
    <form id="loginform" action="<?php ($_SERVER["PHP_SELF"]); ?>" method="post">
        <div class="container">
            <h1 id="h1left">Change Password</h1><hr>
            <label for="old_form_password">Old Password</label>
            <input type="password" id="old_form_password" name="old_form_password" required>
            <label for="form_password">New Password</label>
            <input type="password" id="form_password" name="form_password" required>
            <label for="form_password_repeat">Repeat New Password</label>
            <input type="password" id="form_password_repeat" name="form_password_repeat" required>
            <input type="submit">
        </div>
    </form>
</body>
</html>
<?php  
    // Include the config file
    include('config.php');
    // If form submits and method is POST
    if($_SERVER["REQUEST_METHOD"] == "POST"){
        if ($_POST["form_password"] == $_POST["form_password_repeat"]) {
            // Get form session variables
            // Verify with old password
            $sql = "Select * from auth_table where id =" . $_SESSION['userid'];
            $result = mysqli_query($conn, $sql);
            if (mysqli_num_rows($result) == 1) {
                while($row=mysqli_fetch_assoc($result)){
                    if (password_verify($_POST["old_form_password"], $row['password'])){ 
                         
                        $input_password = password_hash($_POST["form_password"], PASSWORD_DEFAULT);
                         
                        // Insert the variables's data into table
                        $sql = "UPDATE auth_table SET password = '$input_password' WHERE username = '$_SESSION[username]'";
                        // Execute the query
                        if(mysqli_query($conn, $sql)){
                            session_destroy();
                            header("location: login.php");
                            exit();
                        } else{
                            // If faild, throw an error
                            echo "<center><div class=alert>Something went wrong. Please try again later</div></center>";
                        }
                    } 
                    else{
                        echo "<center><div class=alert>Provided Old Password not valid</div></center>";
                    }
                }
            } 
            else{
                echo "<center><div class=alert>Invalid User</div></center>";
            }
            // Verify with old password end
        }
        else echo "<center><div class=alert>New Password and Repeat New Password don't match</div></center>";
    }
// Close mysql connection
mysqli_close($conn);
?>

I hope that once again, you may have find the code above helpful. If you face any difficulty or have some suggestion for improvement, please use comment bellow.

You can find the complete source code of this tutorial at GitHub.

PHP CRUD with Authentication

Create Database and Table

Create Config and CSS Files

Create User Registration

Create Login and Logout

Create Home Page

Create New Addresses

Update Addresses

Delete Addresses

Password Change

Leave a Reply Cancel reply

Recent Posts

Recent Comments

Archives

Categories