Welcome to Ghokante! Today, we are breaking down the two most essential skills every backend developer needs: managing database records (CRUD) and securing applications (Authentication).
To make learning intuitive, we have split this guide into two completely standalone parts. You can build them separately to understand exactly how each mechanism works. Before we start coding, let's set up our database.
Step 0: Database Setup
Open your phpMyAdmin or MySQL console and run these queries. We will create one database (phonebook) and two separate tables for our two projects.
CREATE DATABASE phonebook;
USE phonebook;
-- Table for Part I (CRUD)
CREATE TABLE contacts (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
phone VARCHAR(20) NOT NULL
);
-- Table for Part II (Authentication)
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL
);Part I: Build a Simple CRUD Application
CRUD stands for Create, Read, Update, and Delete. It is the lifecycle of data. In this standalone project, we will build a phonebook where you can manage contacts.
1. The Hub: index.php
This single file handles reading our contacts from the database, displaying them in an HTML table, inserting new contacts via a form, and deleting them.
<?php
$conn = mysqli_connect("localhost","root","","phonebook");
if(isset($_POST['add'])){
$name = $_POST['name'];
$phone = $_POST['phone'];
$query = "INSERT INTO contacts(name,phone) VALUES('$name','$phone')";
mysqli_query($conn,$query);
}
if(isset($_GET['delete'])){
$id = $_GET['delete'];
$query = "DELETE FROM contacts WHERE id=$id";
mysqli_query($conn,$query);
header("Location: index.php");
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Phonebook Application</title>
</head>
<body>
<form action="" method="POST">
<input type="text" name="name" placeholder="Enter name">
<input type="text" name="phone" placeholder="Enter phone">
<input type="submit" name="add">
</form>
<br>
<table border="2">
<tr>
<th>ID</th>
<th>NAME</th>
<th>PHONE</th>
<th>ACTIONS</th>
</tr>
<?php
$query = "SELECT * FROM contacts";
$table_result = mysqli_query($conn, $query);
while( $row = mysqli_fetch_assoc($table_result) ){
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['phone']; ?></td>
<td>
<a href="edit.php?id=<?php echo $row['id']; ?>">Edit</a>
<a href="index.php?delete=<?php echo $row['id']; ?>">Delete</a>
</td>
</tr>
<?php
}
?>
</table>
</body>
</html>2. Updating Data: edit.php
When you click "Edit" on the index page, it sends the contact's ID to this file. We catch that ID, fetch the current data, and display it in a form so the user can modify it.
<?php
$conn = mysqli_connect("localhost","root","","phonebook");
$id = $_GET['id'];
$query = "SELECT * FROM contacts WHERE id=$id";
$result = mysqli_query($conn,$query);
$row = mysqli_fetch_assoc($result);
if(isset($_POST['update'])){
$name = $_POST['name'];
$phone = $_POST['phone'];
$query = "UPDATE contacts SET name='$name',phone='$phone' WHERE id=$id";
mysqli_query($conn,$query);
header("Location: index.php");
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Update Page</title>
</head>
<body>
<h1>Edit Page</h1>
<form action="" method="POST">
<input type="text" name="name" value="<?php echo $row['name']; ?>">
<input type="text" name="phone" value="<?php echo $row['phone']; ?>">
<input type="submit" name="update" value="Update">
</form>
</body>
</html>Part II: User Authentication (Login & Signup)
In this standalone application, we will build a complete authentication flow. We will create a landing page, allow new users to sign up, let them log in safely, and direct them to a protected dashboard.
1. The Landing Page: index.php
This is the front door of your new standalone app, giving users the choice to either log in or create a new account.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Homepage</title>
</head>
<body>
<h1>Welcome to the Homepage.</h1>
<a href="login.php">Login</a>
<br>
<br>
<a href="signup.php">Signup</a>
</body>
</html>2. User Registration: signup.php
This page accepts the user's details and saves them securely into our users table.
<?php
session_start();
$conn = mysqli_connect("localhost","root","","phonebook");
if(isset($_POST['signup'])){
$name = $_POST['name'];
$email = $_POST['email'];
$password = $_POST['password'];
$phone = $_POST['phone'];
$query = "INSERT INTO users(name,email,password,phone) VALUES('$name','$email','$password','$phone')";
mysqli_query($conn,$query);
header("Location: login.php");
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Sign Up</title>
</head>
<body>
<h1>Signup form</h1>
<form method="POST">
<input type="text" name="name" placeholder="Name"><br><br>
<input type="email" name="email" placeholder="Email"><br><br>
<input type="password" name="password" placeholder="Password"><br><br>
<input type="text" name="phone" placeholder="Phone"><br><br>
<input type="submit" name="signup" value="Signup">
</form>
</body>
</html>3. The Login Gateway: login.php
This is where the magic happens. We check the database for the user's credentials. If they match, we start a Session. A session remembers who the user is as they navigate through the website.
<?php
session_start();
$conn = mysqli_connect("localhost", "root", "", "phonebook");
if (isset($_POST['login'])) {
$email = $_POST['email'];
$password = $_POST['password'];
$query = "SELECT * from users where email='$email' and password='$password'";
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) == 1) {
$user = mysqli_fetch_assoc($result);
$_SESSION['user_id'] = $user['id'];
$_SESSION['user_name'] = $user['name'];
header("Location: dashboard.php");
} else {
echo "Invalid username or password";
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Document</title>
</head>
<body>
<h1>Login Form</h1>
<form method="post">
<input type="text" name="email" placeholder="Enter Email"><br><br>
<input type="password" name="password" placeholder="Enter password"><br><br>
<input type="submit" name="login" value="Login">
</form>
</body>
</html>4. The Protected Page: dashboard.php
Notice the if(!isset($_SESSION['user_id'])) check at the very top. This acts as a security guard. If a user tries to access this page directly without logging in first, they are immediately redirected back to the login page.
<?php
session_start();
if(!isset($_SESSION['user_id'])){
header("Location: login.php");
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Document</title>
</head>
<body>
<h1>Dashboard</h1>
<h2>Welcome <?php echo $_SESSION['user_name']; ?></h2>
<a href="logout.php">Logout</a>
</body>
</html>5. Ending the Session: logout.php
Finally, when the user is done, we must securely destroy their session data so no one else can use their account.
<?php
session_start();
session_destroy();
header("Location: login.php");
?>Challenge Time!
Now that you understand both pieces independently, your next challenge is to merge them! Try moving the CRUD table code from Part I into the dashboard.php file in Part II. That way, only logged-in users can view and edit the phonebook.
Leave a comment
Your email address will not be published. Required fields are marked *