Создание php MVC приложения. Получение всех пользователей из БД
В данному уроке мы начнем разрабатывать раздел управления пользователями. Сегодняшней нашей задачей будет получение списка всех пользователей с их ролями.
Выборку мы будем делать из двух таблиц, поэтому нам понадобится SQL оператор INNER JOIN.
Код урока (users.tpl.php)
<!DOCTYPE html>
<html lang="ru" data-ng-app="users">
<head>
<meta charset="utf-8">
<base href="/cabinet/users/">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title><?php echo $pageData['title']; ?></title>
<!-- Bootstrap Core CSS -->
<link href="/css/bootstrap.min.css" rel="stylesheet">
<!-- MetisMenu CSS -->
<link href="/css/admin/metisMenu.min.css" rel="stylesheet">
<!-- Custom CSS -->
<link href="/css/admin/sb-admin-2.css" rel="stylesheet">
<!-- Morris Charts CSS -->
<link href="/css/admin/morris.css" rel="stylesheet">
<!-- Custom Fonts -->
<link href="/css/font-awesome.min.css" rel="stylesheet" type="text/css">
<!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->
<style>
table tr:hover {
cursor: pointer;
}
</style>
</head>
<body>
<div id="wrapper">
<!-- Navigation -->
<nav class="navbar navbar-default navbar-static-top" role="navigation" style="margin-bottom: 0">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="index.html">Кабинет</a>
</div>
<!-- /.navbar-header -->
<ul class="nav navbar-top-links navbar-right">
<!-- /.dropdown -->
<li class="dropdown">
<a class="dropdown-toggle" data-toggle="dropdown" href="#">
<i class="fa fa-user fa-fw"></i> <i class="fa fa-caret-down"></i>
</a>
<ul class="dropdown-menu dropdown-user">
<li><a href="#"><i class="fa fa-user fa-fw"></i> Профиль</a>
</li>
<li class="divider"></li>
<li><a href="/cabinet/logout"><i class="fa fa-sign-out fa-fw"></i> Выйти</a>
</li>
</ul>
<!-- /.dropdown-user -->
</li>
<!-- /.dropdown -->
</ul>
<!-- /.navbar-top-links -->
<div class="navbar-default sidebar" role="navigation">
<div class="sidebar-nav navbar-collapse">
<ul class="nav" id="side-menu">
<li>
<a href="/cabinet"><i class="fa fa-area-chart"></i> Статистика</a>
</li>
<li>
<a href="/cabinet/products"><i class="fa fa-cart-plus"></i> Товары</a>
</li>
<li>
<a href="/cabinet/users"><i class="fa fa-user-o"></i> Пользователи</a>
</li>
</ul>
</div>
<!-- /.sidebar-collapse -->
</div>
<!-- /.navbar-static-side -->
</nav>
<div id="page-wrapper" data-ng-controller="usersController">
<?php if($pageData['permission'] == 1) { ?>
<div class="row">
<div class="col-lg-12">
<h1 class="page-header"><?php echo $pageData['title']; ?></h1>
</div>
</div>
<div class="row">
<div class="col-lg-12">
<!-- /.panel -->
<div class="panel panel-default">
<div class="panel-heading">
<i class="fa fa-bar-chart-o fa-fw"></i>Пользователи
</div>
<!-- /.panel-heading -->
<div class="panel-body">
<div class="row">
<div class="col-lg-12">
<div class="table-responsive">
<table class="table table-bordered table-hover table-striped">
<thead>
<tr>
<th>Имя</th>
<th>Логин</th>
<th>Email</th>
<th>Роль</th>
</tr>
</thead>
<tbody>
<?php
foreach ($pageData['usersList'] as $key => $value) { ?>
<tr data-ng-click="showEditForm(); getUserData(<?php echo $value['id']; ?>);">
<td><?php echo $value['fullName']; ?></td>
<td><?php echo $value['login']; ?></td>
<td><?php echo $value['email']; ?></td>
<td><?php echo $value['role']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<!-- /.table-responsive -->
</div>
<!-- /.col-lg-4 (nested) -->
<!-- /.col-lg-8 (nested) -->
</div>
<!-- /.row -->
<div class="row">
<div class="col-lg-12">
<edit-user></edit-user>
</div>
</div>
</div>
<!-- /.panel-body -->
</div>
<!-- /.panel -->
<!-- /.panel -->
</div>
<!-- /.col-lg-8 -->
</div>
<div class="row">
<div class="col-lg-12">
<h1 class="page-header">Добавить нового пользователя</h1>
</div>
</div>
<div class="row">
<div class="col-lg-12">
<form class="form-horizontal" method="post" data-ng-submit="addNewUser()">
<fieldset>
<div class="form-group">
<label class="col-md-4 control-label" for="newUser">ФИО</label>
<div class="col-md-4">
<input id="newUser" name="newUser" data-ng-model="newUser" class="form-control input-md" required="true" type="text">
</div>
</div>
<div class="form-group">
<label class="col-md-4 control-label" for="newLogin">Логин</label>
<div class="col-md-4">
<input id="newLogin" name="newLogin" data-ng-model="newLogin" class="form-control " required="true" type="text">
</div>
</div>
<div class="form-group">
<label class="col-md-4 control-label" for="newEmail">Email</label>
<div class="col-md-4">
<input id="newEmail" name="newEmail" data-ng-model="newEmail" class="form-control input-md" required="true" type="email">
</div>
</div>
<div class="form-group">
<label class="col-md-4 control-label" for="newPassword">Пароль</label>
<div class="col-md-4">
<input id="newPassword" name="newPassword" data-ng-model="newPassword" class="form-control input-md" required="true" type="password">
</div>
</div>
<div class="form-group">
<label class="col-md-4 control-label" for="newRole">Роль</label>
<div class="col-md-4">
<select name="newRole" data-ng-model="newRole" class="form-control">
<option selected>Выберите роль</option>
<option value="1">Администратор</option>
<option value="2">Менеджер</option>
</select>
</div>
</div>
<div class="form-group">
<div class="col-md-4 col-md-offset-4">
<button class="btn btn-success">Сохранить</button>
</div>
</div>
</fieldset>
</form>
</div>
</div>
<?php } else { ?>
<h1 style="margin-top:0; padding-top:10px;">У вас недостаточно прав для работы с пользователями</h1>
<?php } ?>
</div>
<!-- /#page-wrapper -->
</div>
<!-- /#wrapper -->
<!-- jQuery -->
<script src="/js/jquery.js"></script>
<!-- Angular -->
<script src="/js/angular.min.js"></script>
<!-- Angular Route -->
<script src="/js/angular-route.js"></script>
<script src="/js/admin/users.js"></script>
<!-- Bootstrap Core JavaScript -->
<script src="/js/bootstrap.min.js"></script>
<!-- Metis Menu Plugin JavaScript -->
<script src="/js/admin/metisMenu.js"></script>
<!-- Custom Theme JavaScript -->
<script src="/js/admin/sb-admin-2.js"></script>
<script src="/js/admin/products.js"></script>
</body>
</html>
Код урока (UsersController.php)
<?php
class UsersController extends Controller{
private $pageTpl = "/views/users.tpl.php";
private $mailTpl = "/views/mail/newUser.tpl.html";
public function __construct() {
$this->model = new UsersModel();
$this->view = new View();
}
public function sendRegisterEmail($fullName, $login, $password, $email) {
$headers = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=UTF-8' . "\r\n";
$emailText = file_get_contents(ROOT . $this->mailTpl);
$emailText = str_replace('%fullName%', $fullName, $emailText);
$emailText = str_replace('%login%', $login, $emailText);
$emailText = str_replace('%password%', $password, $emailText);
$emailText = str_replace('%email%', $email, $emailText);
mail($email, "Для вас создана учетная запись", $emailText, $headers);
}
public function index() {
if(!$_SESSION['user']) {
header("Location: /");
}
$this->pageData['permission'] = $_SESSION['role_id'];
$this->pageData['title'] = "Пользователи";
$this->pageData['usersList'] = $this->model->getUsers();
$this->view->render($this->pageTpl, $this->pageData);
}
public function getUserById() {
if(!$_SESSION['user']) {
header("Location: /");
}
if(isset($_POST['id']) && $_POST['id'] != '') {
$userId = $_POST['id'];
$userInfo = json_encode($this->model->getUserById($userId));
echo $userInfo;
} else {
echo json_encode(array("success" => false, "text" => "Ошибка"));
}
}
public function getUsersRoles() {
if(!$_SESSION['user']) {
header("Location: /");
}
$roles = $this->model->getUsersRoles();
if(empty($roles)) {
echo json_encode(array("success" => false, "text" => "Ошибка"));
} else {
echo json_encode($roles);
}
}
public function updateUserData() {
if(!$_SESSION['user']) {
header("Location: /");
}
if(!empty($_POST) && !empty($_POST['id']) && !empty($_POST['fullName']) && !empty($_POST['login']) && !empty($_POST['email']) && !empty($_POST['role'])) {
$userId = $_POST['id'];
$userFullName = $_POST['fullName'];
$userLogin = $_POST['login'];
$userEmail = $_POST['email'];
$userRole = $_POST['role'];
if($this->model->updateUserInfo($userId, $userFullName, $userLogin, $userEmail, $userRole)) {
echo json_encode(array("success" => true, "text" => "Данные пользователя сохранены"));
} else {
echo json_encode(array("success" => false, "text" => "Ошибка сохранения данных"));
}
} else {
echo json_encode(array("success" => false, "text" => "Заполните все данные"));
}
}
public function deleteUser() {
if(!$_SESSION['user']) {
header("Location: /");
}
if(!empty($_POST) && !empty($_POST['id'])) {
$userId = $_POST['id'];
if($this->model->deleteUser($userId)) {
echo json_encode(array("success" => true, "text" => "Пользователь успешно удален"));
} else {
echo json_encode(array("success" => true, "text" => "Ошибка удаления"));
}
} else {
echo json_encode(array("success" => false, "text" => "Произошла ошибка. Попробуйте позже"));
}
}
public function addNewUser() {
if(!$_SESSION['user']) {
header("Location: /");
}
if(!empty($_POST) && !empty($_POST['fullName']) && !empty($_POST['password']) && !empty($_POST['login']) && !empty($_POST['email']) && !empty($_POST['role'])) {
$newUser = $_POST['fullName'];
$newLogin = $_POST['login'];
$newEmail = $_POST['email'];
$newPassword = md5($_POST['password']);
$passwordForEmail = $_POST['password'];
$newRole = $_POST['role'];
if($this->model->addNewUser($newLogin, $newUser, $newEmail, $newPassword, $newRole)) {
echo json_encode(array("success" => true, "text" => "Новый пользователь добавлен"));
$this->sendRegisterEmail($newUser, $newLogin, $passwordForEmail, $newEmail);
} else {
echo json_encode(array("success" => false, "text" => "Ошибка добавления"));
}
} else {
echo json_encode(array("success" => false, "text" => "Заполните все данные"));
}
}
}
Код урока (UsersModel.php)
<?php
class UsersModel extends Model {
public function getUsers() {
$sql = "SELECT users.id, users.login, users.fullName, users.email, role.name as role FROM users
INNER JOIN role ON users.role_id = role.id";
$stmt = $this->db->prepare($sql);
$stmt->execute();
$result = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$result[$row['id']] = $row;
}
return $result;
}
public function getUserById($userId) {
$sql = "SELECT users.id, users.email, users.fullName, users.login, role.name as role FROM users
INNER JOIN role ON users.role_id = role.id
WHERE users.id = :id";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(":id", $userId, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
if(!empty($result)) {
return $result;
} else {
return false;
}
}
public function getUsersRoles() {
$result = array();
$sql = "SELECT * FROM role";
$stmt = $this->db->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$result[] = $row;
}
return $result;
}
public function updateUserInfo($userId, $userFullName, $userLogin, $userEmail, $userRole) {
$sql = "UPDATE users
SET login = :login, fullName = :fullName, email = :email, role_id = :roleId
WHERE id = :id
";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(":login", $userLogin, PDO::PARAM_STR);
$stmt->bindValue(":fullName", $userFullName, PDO::PARAM_STR);
$stmt->bindValue(":email", $userEmail, PDO::PARAM_STR);
$stmt->bindValue(":roleId", $userRole, PDO::PARAM_INT);
$stmt->bindValue(":id", $userId, PDO::PARAM_INT);
$stmt->execute();
return true;
}
public function addNewUser($userLogin, $userFullName, $userEmail, $userPassword, $userRole) {
$sql = "INSERT INTO users(login, fullName, email, password, role_id)
VALUES (:login, :fullName, :email, :password, :role_id)
";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(":login", $userLogin, PDO::PARAM_STR);
$stmt->bindValue(":fullName", $userFullName, PDO::PARAM_STR);
$stmt->bindValue(":email", $userEmail, PDO::PARAM_STR);
$stmt->bindValue(":password", $userPassword, PDO::PARAM_STR);
$stmt->bindValue(":role_id", $userRole, PDO::PARAM_INT);
$stmt->execute();
return true;
}
public function deleteUser($id) {
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(":id", $id, PDO::PARAM_INT);
$stmt->execute();
return true;
}
}
0 Комментариев