Hướng dẫn user management php mysql

Question

Nội dung chính Show

#api_user.php
#config.php
#dbhelp.php
#logout.php
#readme.txt
#register.php

Giải pháp quản lý trạng thái login -> Thực tế:
- Cách 1: Chỉ cho phép người dùng login vào 1 web tại 1 thời điểm
	- Ko cần thêm bảng:
- Cách 2: Cho login nhiều trình duyệt + ở nhiều thiết bị
	- Thêm bảng để làm

Ý tưởng giải pháp
	1) Login -> nhập thông tin
		- Login thành công
			- Lưu thông $_SESSION['currentUser'] = dữ liệu người -> Tồn tại trong 1 khoảng thời gian ngắn
			- Tạo ra token = giải thuật -> mỗi 1 người + tại một thời điểm login -> duy nhất -> ko trùng vs người khác
			- Lưu token vào database (user: Cách 1, tokens (id người dùng, token): Cách 2) -> Tồn tại tới khi xóa thì thôi
			- Lưu token vào cookie (1 tuần, 1 tháng, 1 năm -> OK)
			-> Validate trạng thái đã login bằng cách nào
				- B1. Check $_SESSION['currentUser']
					-> Tồn tại -> OK -> Xong login
					-> Ko tồn tại:
						- B2. Check tiếp:
							- Đọc token từ cookie (Không tồn tại -> chưa login)
							- Tìm token trong bảng tokens hoặc user
								-> thông tin người dùng -> lưu xuống $_SESSION['currentUser']
								-> Ko tìm thấy -> token fake -> login.php

		- Login thất bại: ko nói -> OK

-----------------------------------------------
alter table Users
add token nvarchar(32)

#api_user.php

<?php
session_start();
require_once('dbhelp.php');

$action = getPost('action');

switch ($action) {
	case 'delete':
		deleteUserFromDB();
		break;
}

function deleteUserFromDB() {
	$id = getPost('id');

	$query = "delete from Users where id = '$id'";
	execute($query);
}

#config.php

<?php
define('HOST', 'localhost');
define('DATABASE', 'C2108L');
// define('USERNAME', 'dieptv');
// define('PASSWORD', 'F3!KcshQRe8otOjO');
define('USERNAME', 'root');
define('PASSWORD', '');

function getPost($key, $slash = '\'') {
	$value = '';
	if(isset($_POST[$key])) {
		$value = $_POST[$key];
		// $value = Sinh Vien ' ABC ' => Sinh Vien \' ABC \'
		// \\ -> \
		// \' -> '
		// \' (Hieu la: ') -> \\\' (Hieu la: \')
		$value = str_replace($slash, "\\".$slash, $value);
	}

	return $value;
}

function getGet($key, $slash = '\'') {
	$value = '';
	if(isset($_GET[$key])) {
		$value = $_GET[$key];
		$value = str_replace($slash, "\\".$slash, $value);
	}

	return $value;
}

function getMD5Pwd($pwd) {
	$encrypt = md5($pwd);
	//length: 32 ky tu (a-zA-Z0-9)
	//Chu y: md5 -> hack -> Ko an toan nua -> table hash -> Tim ra mat khau goc
	$encrypt = md5('4957sdJKHG987&*^dfhdfd'.$encrypt.'()49085IUYd9854574Jhdh');

	return $encrypt;
}

function validateLogin() {
	//Check theo phien lam viec -> session
	if(isset($_SESSION['currentUser'])) {
		return $_SESSION['currentUser'];
	}

	//Check login tang 2
	$token = '';
	if(isset($_COOKIE['token'])) {
		$token = $_COOKIE['token'];
		$query = "select * from Users where token = '$token'";
		$data = executeResult($query, true);

		if($data != null) {
			$_SESSION['currentUser'] = $data;
			return $data;
		}
	}

	//TH nay khong ton tai token trong cookie + phien lam viec cung ko thay
	return null;
}

#dbhelp.php

<?php
require_once('config.php');

/**
 * Ham nay se su dung cho TH cau truy van: insert, update, delete
 */
function execute($query) {
	// Mo ket noi toi CSDL
	$conn = mysqli_connect(HOST, USERNAME, PASSWORD, DATABASE);
	mysqli_set_charset($conn, 'utf8');

	// Thuc hien query -> insert du lieu vao database
	// $query = "insert into Users(fullname, email, password, address) values ('$fullname', '$email', '$pwd', '$address')";
	// echo $query;
	mysqli_query($conn, $query);

	// Dong ket noi CSDL
	mysqli_close($conn);
}

function executeResult($query, $isSingle = false) {
	// Mo ket noi toi CSDL
	$conn = mysqli_connect(HOST, USERNAME, PASSWORD, DATABASE);
	mysqli_set_charset($conn, 'utf8');

	// Thuc hien query -> insert du lieu vao database
	// $query = "insert into Users(fullname, email, password, address) values ('$fullname', '$email', '$pwd', '$address')";
	// echo $query;
	// $query = "select * from Users where email = '$email' and password = '$pwd'";
	$resultset = mysqli_query($conn, $query);

	if($isSingle) {
		$data = mysqli_fetch_array($resultset, 1);
	} else {
		$data = [];
		while(($row = mysqli_fetch_array($resultset, 1)) != null) {
			$data[] = $row;
		}
	}

	// Dong ket noi CSDL
	mysqli_close($conn);

	return $data;
}

#edit.php

<?php
session_start();
require_once('dbhelp.php');
// if(!isset($_SESSION['currentUser'])) {
// 	header('Location: login.php');
// 	die();
// }
$user = validateLogin();
if($user == null) {
	header('Location: login.php');
	die();
}

require_once('dbhelp.php');

$id = $fullname = $email = $pwd = $address = "";

if(!empty($_POST)) {
	// Them thong tin nguoi dung vao CSDL

	$fullname = getPost('fullname');
	$email = getPost('email');
	$pwd = getPost('pwd');
	$pwd = getMD5Pwd($pwd);
	//Yeu cau: pwd -> ma hoa -> ma hoa 1 chieu: password -> encrypt -> encrypt code -> ko the dich nguoc lai mat khau goc
	$address = getPost('address');
	$id = getPost('id');

	// Mo ket noi toi CSDL
	// $conn = mysqli_connect(HOST, USERNAME, PASSWORD, DATABASE);
	// mysqli_set_charset($conn, 'utf8');

	// Thuc hien query -> insert du lieu vao database
	// insert, update, delete
	$query = "update Users set fullname = '$fullname', email = '$email', password = '$pwd', address = '$address' where id = '$id'";
	execute($query);
	// echo $query;
	// mysqli_query($conn, $query);

	// Dong ket noi CSDL
	// mysqli_close($conn);
	header('Location: list.php');
}

// Doc noi dung id tu $_GET
$id = getGet('id');
if($id > 0) {
	$query = "select * from Users where id = '$id'";
	$result = executeResult($query, true);
	$id = '';
	if($result != null) {
		$fullname = $result['fullname'];
		$email = $result['email'];
		$address = $result['address'];
		$id = $result['id'];
	}
} else {
	$id = '';
}
if($id == '') {
	header('Location: list.php');
}
?>

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Edit Page</title>

	<!-- Bootstrap -> thiet ke GUI -->
	<link href="https://cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css" rel="stylesheet">
	<script src="https://cdn.jsdelivr.net/npm//dist/js/bootstrap.bundle.min.js"></script>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

	<style type="text/css">
		.form-group {
			margin-bottom: 20px;
		}
	</style>
</head>
<body>
<div class="container">
	<form method="post">
		<div class="form-group">
			<label>Fullname</label>
			<input type="text" name="id" value="<?=$id?>" style="display: none;">
			<input required type="text" name="fullname" class="form-control" value="<?=$fullname?>">
		</div>
		<div class="form-group">
			<label>Email</label>
			<input required type="email" name="email" class="form-control" value="<?=$email?>">
		</div>
		<div class="form-group">
			<label>Password</label>
			<input required type="password" name="pwd" class="form-control">
		</div>
		<div class="form-group">
			<label>Address</label>
			<input required type="text" name="address" class="form-control" value="<?=$address?>">
		</div>
		<div class="form-group">
			<button class="btn btn-success">Save</button>
		</div>
	</form>
</div>
</body>
</html>

#list.php

<?php
session_start();
require_once('dbhelp.php');
// if(!isset($_SESSION['currentUser'])) {
// 	header('Location: login.php');
// 	die();
// }
$user = validateLogin();
if($user == null) {
	header('Location: login.php');
	die();
}

require_once('dbhelp.php');
$query = "select * from Users";
$userList = executeResult($query);
?>

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>User Management Page</title>

	<!-- Bootstrap -> thiet ke GUI -->
	<link href="https://cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css" rel="stylesheet">
	<script src="https://cdn.jsdelivr.net/npm//dist/js/bootstrap.bundle.min.js"></script>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

	<style type="text/css">
		.form-group {
			margin-bottom: 20px;
		}
	</style>
</head>
<body>
<div class="container">
	<a href="register.php">
		<button class="btn btn-warning" style="margin-bottom: 20px;">Add User</button>
	</a>
	<table class="table table-bordered">
		<thead>
			<tr>
				<th style="width: 50px">No</th>
				<th>Full Name</th>
				<th>Email</th>
				<th>Address</th>
				<th style="width: 60px"></th>
				<th style="width: 60px"></th>
			</tr>
		</thead>
		<tbody>
<?php
$index = 0;
foreach($userList as $item) {
	echo '<tr>
			<td style="width: 50px">'.(++$index).'</td>
			<td>'.$item['fullname'].'</td>
			<td>'.$item['email'].'</td>
			<td>'.$item['address'].'</td>
			<td style="width: 60px">
				<a href="edit.php?id='.$item['id'].'"><button class="btn btn-warning">Edit</button></a>
			</td>
			<td style="width: 60px"><button class="btn btn-danger" onclick="deleteUser('.$item['id'].')">Delete</button></td>
		</tr>';
}
?>
		</tbody>
	</table>
	<a href="logout.php">Logout</a>
</div>

<script type="text/javascript">
	function deleteUser(id) {
		var option = confirm('Are you sure to delete this user?')
		if(!option) return

		$.post('api_user.php', {
			'action': 'delete',
			'id': id
		}, function(data) {
			location.reload()
		})
	}
</script>
</body>
</html>

#login.php

<?php
session_start();
require_once('dbhelp.php');
// if(isset($_SESSION['currentUser'])) {
// 	header('Location: list.php');
// 	die();
// }
$user = validateLogin();
if($user != null) {
	header('Location: list.php');
	die();
}

$msg = $email = $pwd = "";

if(!empty($_POST)) {
	// Them thong tin nguoi dung vao CSDL
	$email = getPost('email');
	$pwd = getPost('pwd');
	$pwd = getMD5Pwd($pwd);

	// Mo ket noi toi CSDL
	// $conn = mysqli_connect(HOST, USERNAME, PASSWORD, DATABASE);
	// mysqli_set_charset($conn, 'utf8');

	// Thuc hien query -> insert du lieu vao database
	// $query = "insert into Users(fullname, email, password, address) values ('$fullname', '$email', '$pwd', '$address')";
	// echo $query;
	$query = "select * from Users where email = '$email' and password = '$pwd'";
	$data = executeResult($query, true);
	// $resultset = mysqli_query($conn, $query);

	// $data = [];
	// while(($row = mysqli_fetch_array($resultset, 1)) != null) {
	// 	$data[] = $row;
	// }

	// Dong ket noi CSDL
	// mysqli_close($conn);

	if($data != null) {
		//login thanh cong
		$_SESSION['currentUser'] = $data;
		$token = getMD5Pwd($data['email'].time());

		//Luu token vao database
		$query = "update Users set token = '$token' where id = ".$data['id'];
		execute($query);

		//Luu vao cookie
		setcookie('token', $token, time() + 7 * 24 * 60 * 60, '/');

		header('Location: list.php');
		die();
	} else {
		$msg = 'Danh Nhap That Bai!';
	}
}
?>

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Login Page</title>

	<!-- Bootstrap -> thiet ke GUI -->
	<link href="https://cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css" rel="stylesheet">
	<script src="https://cdn.jsdelivr.net/npm//dist/js/bootstrap.bundle.min.js"></script>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

	<style type="text/css">
		.form-group {
			margin-bottom: 20px;
		}
	</style>
</head>
<body>
<div class="container">
	<h2 style="text-align: center; color: red;"><?=$msg?></h2>
	<form method="post">
		<div class="form-group">
			<label>Email</label>
			<input required type="email" name="email" class="form-control">
		</div>
		<div class="form-group">
			<label>Password</label>
			<input required type="password" name="pwd" class="form-control">
		</div>
		<div class="form-group">
			<button class="btn btn-success">Login</button>
		</div>
	</form>
</div>
</body>
</html>

#logout.php

<?php
session_start();
header('Location: login.php');

session_destroy();

#readme.txt

Nội dung học:
	- Giới thiệu về phpmyadmin (mysql)
	- Thiết kế 1 CSDL quản lý người dùng
	- Phát triển 1 dự án quản lý người dùng

1) Tài khoản người dùng:
	- IP: PC Name -> localhost
	- TK:
		User Name:root
		Pwd: 
	- Tạo được 1 TK mới:
		User Name: dieptv
		Pwd: F3!KcshQRe8otOjO

2) MySQL
	- CSDL: C2108L
	- Tạo 1 bảng User

create table Users (
	id int primary key auto_increment,
	fullname varchar(50),
	email varchar(150),
	password varchar(32),
	address varchar(200)
)

3) Phat trien du an ket noi PHP & MySQL

#register.php

<?php
session_start();
$fullname = $email = $pwd = $address = "";

if(!empty($_POST)) {
	// Them thong tin nguoi dung vao CSDL
	require_once('dbhelp.php');

	$fullname = getPost('fullname');
	$email = getPost('email');
	$pwd = getPost('pwd');
	$pwd = getMD5Pwd($pwd);
	//Yeu cau: pwd -> ma hoa -> ma hoa 1 chieu: password -> encrypt -> encrypt code -> ko the dich nguoc lai mat khau goc
	$address = getPost('address');

	// Mo ket noi toi CSDL
	// $conn = mysqli_connect(HOST, USERNAME, PASSWORD, DATABASE);
	// mysqli_set_charset($conn, 'utf8');

	// Thuc hien query -> insert du lieu vao database
	// insert, update, delete
	$query = "insert into Users(fullname, email, password, address) values ('$fullname', '$email', '$pwd', '$address')";
	execute($query);
	// echo $query;
	// mysqli_query($conn, $query);

	// Dong ket noi CSDL
	// mysqli_close($conn);
}
?>

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Register Page</title>

	<!-- Bootstrap -> thiet ke GUI -->
	<link href="https://cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css" rel="stylesheet">
	<script src="https://cdn.jsdelivr.net/npm//dist/js/bootstrap.bundle.min.js"></script>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

	<style type="text/css">
		.form-group {
			margin-bottom: 20px;
		}
	</style>
</head>
<body>
<div class="container">
	<form method="post">
		<div class="form-group">
			<label>Fullname</label>
			<input required type="text" name="fullname" class="form-control">
		</div>
		<div class="form-group">
			<label>Email</label>
			<input required type="email" name="email" class="form-control">
		</div>
		<div class="form-group">
			<label>Password</label>
			<input required type="password" name="pwd" class="form-control">
		</div>
		<div class="form-group">
			<label>Address</label>
			<input required type="text" name="address" class="form-control">
		</div>
		<div class="form-group">
			<button class="btn btn-success">Register</button>
		</div>
	</form>
</div>
</body>
</html>

programming php PHP account system