CRUD Operation On A User Profile In PHP/ MySQL

Introduction

In this article, I’ll show you how to perform the CRUD operation on a user profile in PHP/MySQL, such as - insert, update, delete, retrieve. This application will work for admins where the admin can add member’s information like username, profile picture, and description etc., i the same way as insertion admin can update member information and also can delete members where only admin can delete member record.

The purpose or aim of this application is to create and manage the work of the user easily by identifying each and every person. This application is created with Bootstrap, PHP/MySQL.

PHP

How to create a database in MySQL?

For creating a database, you should follow some steps as given below.

We will create two tables - one for admin and another one for users.

CREATE TABLE `admin` (
`id` int(11) NOT NULL,
`username` varchar(25) NOT NULL,
`password` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `users` (
`userid` int(11) NOT NULL,
`username` varchar(20) NOT NULL,
`description` varchar(50) NOT NULL,
`userprofile` varchar(200) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- Table structure for table `admin`
--
CREATE TABLE `admin` (
`id` int(11) NOT NULL,
`username` varchar(25) NOT NULL,
`password` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `admin`
--
INSERT INTO `admin` (`id`, `username`, `password`) VALUES
(1, 'admin', 'admin');
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`userid` int(11) NOT NULL,
`username` varchar(20) NOT NULL,
`description` varchar(50) NOT NULL,
`userprofile` varchar(200) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`userid`, `username`, `description`, `userprofile`) VALUES
(52, 'Dave Doe', 'Member3', '384680.png'),
(53, 'March Doe', 'Member4', '930734.png'),
(44, 'John Doe', 'Member1', '871406.png'),
(51, 'Jean Doe', 'Member2', '48895.png');

PHP

Now, we will start our PHP code.

Home.php

<div class="container">
<h1 align="center">PHP/MySQL Add, Edit, Delete, With User Profile.</h1>
<div class="page-header">
<h1 class="h2"> List of Members<a class="btn btn-success" href="addmember.php" style="margin-left: 770px;"><span class="glyphicon glyphicon-user"></span> Add Member</a></h1><hr>
</div>
<div class="row">
<?php
$stmt = $DB_con->prepare('SELECT userid, username, description, userprofile FROM users ORDER BY userid DESC');
$stmt->execute();
if($stmt->rowCount() > 0)
{
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
extract($row);
?>
<div class="col-xs-3">
<h3 class="page-header" style="background-color:cadetblue" align="center"><?php echo $username."<br>".$description; ?></h3>
<img src="uploads/<?php echo $row['userprofile']; ?>" class="img-rounded" width="250px" height="250px" /><hr>
<p class="page-header" align="center">
<span>
<a class="btn btn-primary" href="editform.php?edit_id=<?php echo $row['userid']; ?>"><span class="glyphicon glyphicon-pencil"></span> Edit</a>
<a class="btn btn-warning" href="?delete_id=<?php echo $row['userid']; ?>" title="click for delete" onclick="return confirm('Are You Sure You Want To Delete This User?')"><span class="glyphicon glyphicon-trash"></span> Delete</a>
</span>
</p>
</div>
<?php
}
}
else
{
?>
<div class="col-xs-12">
<div class="alert alert-warning">
<span class="glyphicon glyphicon-info-sign"></span> No Data Found.
</div>
</div>
<?php
}
?>
</div>
</div>

This code is used for deleting a user.

<?php
require_once 'dbcon.php';
if(isset($_GET['delete_id']))
{
$stmt_select = $DB_con->prepare('SELECT userprofile FROM users WHERE userid =:uid');
$stmt_select->execute(array(':uid'=>$_GET['delete_id']));
$imgRow=$stmt_select->fetch(PDO::FETCH_ASSOC);
unlink("user_images/".$imgRow['userprofile']);
$stmt_delete = $DB_con->prepare('DELETE FROM users WHERE userid =:uid');
$stmt_delete->bindParam(':uid',$_GET['delete_id']);
$stmt_delete->execute();
header("Location: index.php");
}
?>

Editform.php

<?php
error_reporting( ~E_NOTICE );
require_once 'dbcon.php';
if(isset($_GET['edit_id']) && !empty($_GET['edit_id']))
{
$id = $_GET['edit_id'];
$stmt_edit = $DB_con->prepare('SELECT username, description, userprofile FROM users WHERE userid =:uid');
$stmt_edit->execute(array(':uid'=>$id));
$edit_row = $stmt_edit->fetch(PDO::FETCH_ASSOC);
extract($edit_row);
}
else
{
header("Location: index.php");
}
if(isset($_POST['btn_save_updates']))
{
$username = $_POST['user_name'];
$description = $_POST['description'];
$imgFile = $_FILES['user_image']['name'];
$tmp_dir = $_FILES['user_image']['tmp_name'];
$imgSize = $_FILES['user_image']['size'];
if($imgFile)
{
$upload_dir = 'uploads/';
$imgExt = strtolower(pathinfo($imgFile,PATHINFO_EXTENSION));
$valid_extensions = array('jpeg', 'jpg', 'png', 'gif');
$userprofile = rand(1000,1000000).".".$imgExt;
if(in_array($imgExt, $valid_extensions))
{
if($imgSize < 5000000)
{
unlink($upload_dir.$edit_row['userprofile']);
move_uploaded_file($tmp_dir,$upload_dir.$userprofile);
}
else
{
$errMSG = "Sorry, Your File Is Too Large To Upload. It Should Be Less Than 5MB.";
}
}
else
{
$errMSG = "Sorry, only JPG, JPEG, PNG & GIF Extension Files Are Allowed.";
}
}
else
{
$userprofile = $edit_row['userprofile'];
}
if(!isset($errMSG))
{
$stmt = $DB_con->prepare('UPDATE users SET username=:uname, description=:udes, userprofile=:upic WHERE userid=:uid');
$stmt->bindParam(':uname',$username);
$stmt->bindParam(':udes',$description);
$stmt->bindParam(':upic',$userprofile);
$stmt->bindParam(':uid',$id);
if($stmt->execute()){
?>
<script>
alert('Successfully Updated...');
window.location.href='home.php';
</script>
<?php
}
else{
$errMSG = "Sorry User Could Not Be Updated!";
}
}
}
?>

Addmember.php

<?php
error_reporting( ~E_NOTICE );
require_once 'dbcon.php';
if(isset($_GET['edit_id']) && !empty($_GET['edit_id']))
{
$id = $_GET['edit_id'];
$stmt_edit = $DB_con->prepare('SELECT username, description, userprofile FROM users WHERE userid =:uid');
$stmt_edit->execute(array(':uid'=>$id));
$edit_row = $stmt_edit->fetch(PDO::FETCH_ASSOC);
extract($edit_row);
}
else
{
header("Location: index.php");
}
if(isset($_POST['btn_save_updates']))
{
$username = $_POST['user_name'];
$description = $_POST['description'];
$imgFile = $_FILES['user_image']['name'];
$tmp_dir = $_FILES['user_image']['tmp_name'];
$imgSize = $_FILES['user_image']['size'];
if($imgFile)
{
$upload_dir = 'uploads/';
$imgExt = strtolower(pathinfo($imgFile,PATHINFO_EXTENSION));
$valid_extensions = array('jpeg', 'jpg', 'png', 'gif');
$userprofile = rand(1000,1000000).".".$imgExt;
if(in_array($imgExt, $valid_extensions))
{
if($imgSize < 5000000)
{
unlink($upload_dir.$edit_row['userprofile']);
move_uploaded_file($tmp_dir,$upload_dir.$userprofile);
}
else
{
$errMSG = "Sorry, Your File Is Too Large To Upload. It Should Be Less Than 5MB.";
}
}
else
{
$errMSG = "Sorry, only JPG, JPEG, PNG & GIF Extension Files Are Allowed.";
}
}
else
{
$userprofile = $edit_row['userprofile'];
}
if(!isset($errMSG))
{
$stmt = $DB_con->prepare('UPDATE users SET username=:uname, description=:udes, userprofile=:upic WHERE userid=:uid');
$stmt->bindParam(':uname',$username);
$stmt->bindParam(':udes',$description);
$stmt->bindParam(':upic',$userprofile);
$stmt->bindParam(':uid',$id);
if($stmt->execute()){
?>
<script>
alert('Successfully Updated...');
window.location.href='home.php';
</script>
<?php
}
else{
$errMSG = "Sorry User Could Not Be Updated!";
}
}
}
?>

Output Screen

Output Screen