Create php MVC app. Get all users

Home » Tutorials » PHP+MySQL » Create php MVC app. Get all users
In this tutorial we will begin to make management users section. Our task today will be to select all users from database with their roles.
We will make select from two tables, that’s why we must use INNER JOIN SQL operator

Code lesson (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>

Code lesson (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" => "Заполните все данные"));
        }

    }


}

Code lesson (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 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

Pin It on Pinterest

Share This