In another article, I have written about Very Simple Add, Edit, Delete, View in PHP & MySQL. That article contains a basic CRUD (Create, Read, Update, Delete) system. It doesn’t have the feature of login and register.
In this article, I will be presenting a complete CRUD system containing login and register feature. User should register himself first. And then he can add data after logging in.
Here is the step-by-step guide on creating such CRUD system:
First of all, we need to create database and tables. Let the database name be ‘test2‘.
create database
test2;
There are two tables in the system: login and products.
login: This table contains user’s information for login. It contains user’s name, email, username and password. User registration data is saved into this table.
products: This table contains data added by logged in users. This table contains product information like name, quantity, and price.
use `test2`;
CREATE TABLE `login` (
`id` int(9) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`username` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`qty` int(5) NOT NULL,
`price` decimal(10,2) NOT NULL,
`login_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT FK_products_1
FOREIGN KEY (login_id) REFERENCES login(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
Note: Cascading is done in
productstable makinglogin_idas foreign key tologintable. This means that all the products entered by a user is automatically deleted fromproductstable whenever that particular user is deleted fromlogintable.
Now, we will create a connection.php file which contains database connection code. This code connects to the MySQL database. This file is included in all PHP pages where database connection is necessary.
connection.php
In below code, the database host name is localhost where username=root and password=root. The database test2 has been selected.
<?php
/*
// mysql_connect("database-host", "username", "password")
$conn = mysql_connect("localhost","root","root")
or die("cannot connected");
// mysql_select_db("database-name", "connection-link-identifier")
@mysql_select_db("test2",$conn);
*/
/**
* mysql_connect is deprecated
* using mysqli_connect instead
*/
$databaseHost = 'localhost';
$databaseName = 'test2';
$databaseUsername = 'root';
$databasePassword = 'root';
$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName);
?>
index.php is our homepage. In this page, if the user is not logged in then login and register links are displayed. If the user is logged in then the user is greeted and a link is displayed to view & add products.
Note: session_start() function should be written at the beginning of every php file where session handling is done. You can see the same in index.php as well.
index.php
<?php session_start(); ?>
<html>
<head>
<title>Homepage</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body>
<div id="header">
Welcome to my page!
</div>
<?php
if(isset($_SESSION['valid'])) {
include("connection.php");
$result = mysqli_query($mysqli, "SELECT * FROM login");
?>
Welcome <?php echo $_SESSION['name'] ?> ! <a href='logout.php'>Logout</a><br/>
<br/>
<a href='view.php'>View and Add Products</a>
<br/><br/>
<?php
} else {
echo "You must be logged in to view this page.<br/><br/>";
echo "<a href='login.php'>Login</a> | <a href='register.php'>Register</a>";
}
?>
<div id="footer">
Created by <a href="http://blog.chapagain.com.np" title="Mukesh Chapagain">Mukesh Chapagain</a>
</div>
</body>
</html>
index.php uses a little bit of Cascading StyleSheet (CSS). Here is the CSS file code used in index.php.
style.css
body {
margin: auto;
height: 500px;
padding: 20px;
}
#header {
width: 700px;
color: maroon;
font-size: 32px;
padding: 10px 10px 10px 0px;
margin-bottom: 15px;
border-bottom: 1px solid green;
}
#footer {
border-top: 1px solid green;
margin-top: 20px;
color: #336699;
padding-top: 10px;
}
Registration page asks for user’s name, email, username and password. The registration data is saved in login table.
register.php
<html>
<head>
<title>Register</title>
</head>
<body>
<a href="index.php">Home</a> <br />
<?php
include("connection.php");
if(isset($_POST['submit'])) {
$name = $_POST['name'];
$email = $_POST['email'];
$user = $_POST['username'];
$pass = $_POST['password'];
if($user == "" || $pass == "" || $name == "" || $email == "") {
echo "All fields should be filled. Either one or many fields are empty.";
echo "<br/>";
echo "<a href='register.php'>Go back</a>";
} else {
mysqli_query($mysqli, "INSERT INTO login(name, email, username, password) VALUES('$name', '$email', '$user', md5('$pass'))")
or die("Could not execute the insert query.");
echo "Registration successfully";
echo "<br/>";
echo "<a href='login.php'>Login</a>";
}
} else {
?>
<p><font size="+2">Register</font></p>
<form name="form1" method="post" action="">
<table width="75%" border="0">
<tr>
<td width="10%">Full Name</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="email"></td>
</tr>
<tr>
<td>Username</td>
<td><input type="text" name="username"></td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="submit" value="Submit"></td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
After successful registration, user needs to login in order to add products in the system.
When the login is successful, I have set three SESSION variables. You can see it in below login.php code.
SESSION[‘valid’] = This variable contains user’s username
SESSION[‘name’] = This variable contains user’s full name
SESSION[‘id’] = This variable contains user’s id
login.php
<?php session_start(); ?>
<html>
<head>
<title>Login</title>
</head>
<body>
<a href="index.php">Home</a> <br />
<?php
include("connection.php");
if(isset($_POST['submit'])) {
$user = mysqli_real_escape_string($mysqli, $_POST['username']);
$pass = mysqli_real_escape_string($mysqli, $_POST['password']);
if($user == "" || $pass == "") {
echo "Either username or password field is empty.";
echo "<br/>";
echo "<a href='login.php'>Go back</a>";
} else {
$result = mysqli_query($mysqli, "SELECT * FROM login WHERE username='$user' AND password=md5('$pass')")
or die("Could not execute the select query.");
$row = mysqli_fetch_assoc($result);
if(is_array($row) && !empty($row)) {
$validuser = $row['username'];
$_SESSION['valid'] = $validuser;
$_SESSION['name'] = $row['name'];
$_SESSION['id'] = $row['id'];
} else {
echo "Invalid username or password.";
echo "<br/>";
echo "<a href='login.php'>Go back</a>";
}
if(isset($_SESSION['valid'])) {
header('Location: index.php');
}
}
} else {
?>
<p><font size="+2">Login</font></p>
<form name="form1" method="post" action="">
<table width="75%" border="0">
<tr>
<td width="10%">Username</td>
<td><input type="text" name="username"></td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="submit" value="Submit"></td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
After successful login, user is redirected to homepage (index.php). There is a link to view and add new products.
To add product data into database, we need an html form.
add.html
<html>
<head>
<title>Add Data</title>
</head>
<body>
<a href="index.php">Home</a> | <a href="view.php">View Products</a> | <a href="logout.php">Logout</a>
<br/><br/>
<form action="add.php" method="post" name="form1">
<table width="25%" border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>Quantity</td>
<td><input type="text" name="qty"></td>
</tr>
<tr>
<td>Price</td>
<td><input type="text" name="price"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="Submit" value="Add"></td>
</tr>
</table>
</form>
</body>
</html>
Form action on add.html is add.php. It means that the submitted form data will go to add.php. In add.php, we do a simple validation of checking if the entered name, quantity & price are empty or not. If they are all filled then the data will be inserted into database table.
add.php
<?php session_start(); ?>
<?php
if(!isset($_SESSION['valid'])) {
header('Location: login.php');
}
?>
<html>
<head>
<title>Add Data</title>
</head>
<body>
<?php
//including the database connection file
include_once("connection.php");
if(isset($_POST['Submit'])) {
$name = $_POST['name'];
$qty = $_POST['qty'];
$price = $_POST['price'];
$loginId = $_SESSION['id'];
// checking empty fields
if(empty($name) || empty($qty) || empty($price)) {
if(empty($name)) {
echo "<font color='red'>Name field is empty.</font><br/>";
}
if(empty($qty)) {
echo "<font color='red'>Quantity field is empty.</font><br/>";
}
if(empty($price)) {
echo "<font color='red'>Price field is empty.</font><br/>";
}
//link to the previous page
echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
} else {
// if all the fields are filled (not empty)
//insert data to database
$result = mysqli_query($mysqli, "INSERT INTO products(name, qty, price, login_id) VALUES('$name','$qty','$price', '$loginId')");
//display success message
echo "<font color='green'>Data added successfully.";
echo "<br/><a href='view.php'>View Result</a>";
}
}
?>
</body>
</html>
Products data is fetched from database and displayed in view.php file. This file also contains a link to add data. On every row of displayed data, there is also a link to edit and delete data. Below is a sample image of view.php:
At the beginning of view.php file below and add.php file above, you must have noticed session_start() and isset() function.
session_start() function should be written at the beginning of every page where SESSION handling is done.
isset() function checks if the SESSION variable is set or not. If not, the user will be redirected to login page (login.php).
view.php
<?php session_start(); ?>
<?php
if(!isset($_SESSION['valid'])) {
header('Location: login.php');
}
?>
<?php
//including the database connection file
include_once("connection.php");
//fetching data in descending order (lastest entry first)
$result = mysqli_query($mysqli, "SELECT * FROM products WHERE login_id=".$_SESSION['id']." ORDER BY id DESC");
?>
<html>
<head>
<title>Homepage</title>
</head>
<body>
<a href="index.php">Home</a> | <a href="add.html">Add New Data</a> | <a href="logout.php">Logout</a>
<br/><br/>
<table width='80%' border=0>
<tr bgcolor='#CCCCCC'>
<td>Name</td>
<td>Quantity</td>
<td>Price (euro)</td>
<td>Update</td>
</tr>
<?php
while($res = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>".$res['name']."</td>";
echo "<td>".$res['qty']."</td>";
echo "<td>".$res['price']."</td>";
echo "<td><a href=\"edit.php?id=$res[id]\">Edit</a> | <a href=\"delete.php?id=$res[id]\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>";
}
?>
</table>
</body>
</html>
Each row of data can be edited separately. Row ID is passed in the URL of edit.php. ID uniquely identifies the data entry.
While adding data, we had two files: add.html and add.php. While editing data, I have kept the entire thing in a single edit.php file. Edit form in HTML and database update code in PHP are present in the same file.
In the code below, at first a single row entry of data is fetched based on the id. The fetched data is displayed in the edit form. When user edits the data and submits the form, then some simple validation is done for empty data. When everything is correct, then that particular entry of data is updated in database.
edit.php
<?php session_start(); ?>
<?php
if(!isset($_SESSION['valid'])) {
header('Location: login.php');
}
?>
<?php
// including the database connection file
include_once("connection.php");
if(isset($_POST['update']))
{
$id = $_POST['id'];
$name = $_POST['name'];
$qty = $_POST['qty'];
$price = $_POST['price'];
// checking empty fields
if(empty($name) || empty($qty) || empty($price)) {
if(empty($name)) {
echo "<font color='red'>Name field is empty.</font><br/>";
}
if(empty($qty)) {
echo "<font color='red'>Quantity field is empty.</font><br/>";
}
if(empty($price)) {
echo "<font color='red'>Price field is empty.</font><br/>";
}
} else {
//updating the table
$result = mysqli_query($mysqli, "UPDATE products SET name='$name', qty='$qty', price='$price' WHERE id=$id");
//redirectig to the display page. In our case, it is view.php
header("Location: view.php");
}
}
?>
<?php
//getting id from url
$id = $_GET['id'];
//selecting data associated with this particular id
$result = mysqli_query($mysqli, "SELECT * FROM products WHERE id=$id");
while($res = mysqli_fetch_array($result))
{
$name = $res['name'];
$qty = $res['qty'];
$price = $res['price'];
}
?>
<html>
<head>
<title>Edit Data</title>
</head>
<body>
<a href="index.php">Home</a> | <a href="view.php">View Products</a> | <a href="logout.php">Logout</a>
<br/><br/>
<form name="form1" method="post" action="edit.php">
<table border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name" value="<?php echo $name;?>"></td>
</tr>
<tr>
<td>Quantity</td>
<td><input type="text" name="qty" value="<?php echo $qty;?>"></td>
</tr>
<tr>
<td>Price</td>
<td><input type="text" name="price" value="<?php echo $price;?>"></td>
</tr>
<tr>
<td><input type="hidden" name="id" value=<?php echo $_GET['id'];?>></td>
<td><input type="submit" name="update" value="Update"></td>
</tr>
</table>
</form>
</body>
</html>
Each row of data can be deleted separately. Row ID is passed in the URL of delete.php. ID uniquely identifies the data entry. After deletion, the user is redirected to view page (view.php).
delete.php
<?php session_start(); ?>
<?php
if(!isset($_SESSION['valid'])) {
header('Location: login.php');
}
?>
<?php
//including the database connection file
include("connection.php");
//getting id of the data from url
$id = $_GET['id'];
//deleting the row from table
$result=mysqli_query($mysqli, "DELETE FROM products WHERE id=$id");
//redirecting to the display page (view.php in our case)
header("Location:view.php");
?>
User can finally logout. User is redirected to homepage (index.php) after logout.
logout.php
session_start();
session_destroy();
header("Location:index.php");
Download Full Source Code: Complete Create, Read, Update, Delete in PHP & MySQL
Hope this helps. Thanks.