Hướng dẫn user management php mysql

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="//cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css" rel="stylesheet"> <script src="//cdn.jsdelivr.net/npm//dist/js/bootstrap.bundle.min.js"></script> <script src="//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="//cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css" rel="stylesheet"> <script src="//cdn.jsdelivr.net/npm//dist/js/bootstrap.bundle.min.js"></script> <script src="//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" title="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="//cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css" rel="stylesheet"> <script src="//cdn.jsdelivr.net/npm//dist/js/bootstrap.bundle.min.js"></script> <script src="//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="//cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css" rel="stylesheet"> <script src="//cdn.jsdelivr.net/npm//dist/js/bootstrap.bundle.min.js"></script> <script src="//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>