I’m working on trying to build an attendance application using PHP, PDO and MySQL. I currently have a table called users that just shows a list of users that can log into the application. The table structure is shown below.
I have a status column that shows an integer of 1 or 0. My thoughts are if the column is set to 1, then the user could log in at anytime since their account is active. If the column is set to 0, then the user cannot log in at all unless their status is set to 1 again.
I have a graphical table in my app that shows this column. Only users with the Administrator role can access the table:
And an edit form where the option can be changed:
But I don’t have anything in PHP code to provide functionality… What’s the best way to do something like this?
The code I currently have follows. First is the graphical users table in my app:
<?php
include('nav/head.php');
if($role_id != '1') {
header('Location: error.php');
exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<meta name="description" content="">
<meta name="author" content="">
<title>CCRP | Users</title>
<?php include('nav/header.php') ?>
<!-- Page Heading -->
<h1 class="h3 mb-2 text-gray-800">Users List</h1><br>
<!-- <p class="mb-4">DataTables is a third party plugin that is used to generate the demo table below. For more information about DataTables, please visit the <a target="_blank" href="https://datatables.net">official DataTables documentation</a>.</p> -->
<!-- DataTables Example -->
<div class="card shadow mb-4">
<div class="card-header py-3">
<h6 class="m-0 font-weight-bold text-primary">Add, Edit or Remove User Accounts</h6>
</div>
<div class="card-body">
<a class="btn btn-success" href="user_new.php"><i class="fa fa-user-plus"></i>  Add New User</a>
<br><br>
<div class="table-responsive">
<table class="table table-bordered" id="dataTable" width="100%" cellspacing="0">
<thead>
<tr>
<th>User ID</th>
<th>User Role</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email Address</th>
<th>Username</th>
<th>Status</th>
<th>Action</th>
</tr>
</thead>
<?php
$stmt = $pdo->prepare("SELECT id, role_id, first_name, last_name, email, username, status FROM users");
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
?>
<tr>
<td><?php print($row['id']) ?></td>
<td>
<?php
if ($row['role_id'] === '1') {
echo 'Administrator';
} elseif ($row['role_id'] === '2') {
echo 'Operator';
}
?>
</td>
<td><?php print($row['first_name']) ?></td>
<td><?php print($row['last_name']) ?></td>
<td><?php print($row['email']) ?></td>
<td><?php print($row['username']) ?></td>
<td>
<?php
if ($row['status'] === '1') {
echo '<strong style="color: #009900;">Active</strong>';
} elseif ($row['status'] === '0') {
echo '<strong style="color: #a40000;">Inactive</strong>';
}
?>
</td>
<td>
<a href="user_edit.php?edit_id=<?php print($row['id']); ?>"><i class="fa fa-user-edit"></i></a>
<a href="#" data-toggle="modal" data-target="#deleteModal_<?php echo $row['id'];?>">
<i style="color: #a40000;"class="fas fa-trash fa-sm fa-fw mr-2"></i>
</a>
<!-- Delete Modal -->
<div class="modal fade" id="deleteModal_<?php echo $row['id'];?>" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Delete User</h5>
<button class="close" type="button" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">Are you sure you want to delete <?php print($row['first_name'] . ' ' . $row['last_name']);?> from the users list?</div>
<div class="modal-footer">
<button class="btn btn-secondary" type="button" data-dismiss="modal">Cancel</button>
<form action="api/users/delete.php" method="post">
<input type="submit" class="btn btn-danger" name="Delete_User[<?php echo $row['id'];?>]" value="Delete" />
</form>
</div>
</div>
</div>
</div>
</td>
</tr>
<?php } ?>
</table>
</div>
</div>
</div>
</div>
<!-- /.container-fluid -->
</div>
<!-- End of Main Content -->
<?php include('nav/footer.php'); ?>
</html>
Next is the edit form:
<?php
include('nav/head.php');
// Define user data by User ID
if(isset($_GET['edit_id'])) {
$id = $_GET['edit_id'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE id=:id");
$stmt->execute(array(":id" => $id));
$rowUser = $stmt->fetch(PDO::FETCH_ASSOC);
} else {
$id = null;
$rowUser = null;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<meta name="description" content="">
<meta name="author" content="">
<link rel="stylesheet" type="text/css" href="css/toggle.css">
<title>CCRP | <?php print($rowUser['first_name'] . " " . $rowUser['last_name']); ?></title>
<?php include('nav/header.php'); ?>
<h1 class="h3 mb-2 text-gray-800"> Edit <?php print($rowUser['first_name'] . " " . $rowUser['last_name']); ?></h1>
<br>
<form action="api/users/edit.php" method="post">
<input type="hidden" class="form-control" id="id" name="id" placeholder="" value="<?php print($rowUser['id']); ?>" maxlength="255" autocomplete="off" readonly/>
<div class="form-group">
<label for="role_id">User Status</label>
<!-- <input type="text" class="form-control" id="role_id" name="role_id" placeholder="" value="<?php print($rowUser['role_id']); ?>" maxlength="255" autocomplete="off" /> -->
<?php
if($rowUser['status'] === '1') {
echo '<select class="form-control" id="status" name="status">
<option selected value="1">Active</option>
<option value="0">Inactive</option>
</select>';
} elseif ($rowUser['status'] === '0') {
echo '<select class="form-control" id="status" name="status">
<option value="1">Active</option>
<option selected value="0">Inactive</option>
</select>';
}
?>
</div>
<div class="form-group">
<label for="role_id">User Role</label>
<!-- <input type="text" class="form-control" id="role_id" name="role_id" placeholder="" value="<?php print($rowUser['role_id']); ?>" maxlength="255" autocomplete="off" /> -->
<?php
if($rowUser['role_id'] === '1') {
echo '<select class="form-control" id="role_id" name="role_id">
<option selected value="1">Administrator</option>
<option value="2">Operator</option>
</select>';
} elseif ($rowUser['role_id'] === '2') {
echo '<select class="form-control" id="role_id" name="role_id">
<option value="1">Administrator</option>
<option selected value="2">Operator</option>
</select>';
}
?>
</div>
<div class="form-group">
<label for="first_name">First Name</label>
<input type="text" class="form-control" id="first_name" name="first_name" placeholder="" value="<?php print($rowUser['first_name']); ?>" maxlength="255" autocomplete="off" />
</div>
<div class="form-group">
<label for="last_name">Last Name</label>
<input type="text" class="form-control" id="last_name" name="last_name" placeholder="" value="<?php print($rowUser['last_name']); ?>" maxlength="14" autocomplete="off" />
</div>
<div class="form-group">
<label for="email">Email</label>
<input type="text" class="form-control" id="email" name="email" placeholder="" value="<?php print($rowUser['email']); ?>" autocomplete="off" />
</div>
<div class="form-group">
<label for="username">Username</label>
<input type="text" class="form-control" id="username" name="username" placeholder="" value="<?php print($rowUser['username']); ?>" autocomplete="off" />
</div>
<hr style="background-color: #a40000;">
<div class="form-group">
<label for="password">New Password</label>
<input type="password" class="form-control" id="password" name="password" placeholder="" autocomplete="off" />
</div>
<div class="form-group">
<label for="confirm_pwd">Confirm Password</label>
<input type="password" class="form-control" id="confirm_pwd" name="confirm_pwd" placeholder="" autocomplete="off" />
</div>
<input type="submit" name="btn_save" class="btn btn-success" value="Save">
<input type="submit" name="btn_cancel" class="btn btn-danger" value="Cancel">
</form>
</div>
<!-- /.container-fluid -->
</div>
<!-- End of Main Content -->
<?php include('nav/footer.php'); ?>
</html>
And finally, the edit API:
<?php
include ('../dbconnect.php');
// Update
$stmt = $pdo->prepare("UPDATE users SET role_id = :role_id, first_name = :first_name, last_name = :last_name, email = :email, username = :username, status = :status WHERE id = :id");
$stmt->bindParam(':role_id', $role_id);
$stmt->bindParam(':first_name', $first_name);
$stmt->bindParam(':last_name', $last_name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':status', $status);
$stmt->bindParam(':id', $id);
// Update User Info
if(isset($_POST['btn_save'])) {
$role_id = $_POST["role_id"];
$first_name = $_POST["first_name"];
$last_name = $_POST["last_name"];
$email = $_POST["email"];
$username = $_POST["username"];
$status = $_POST["status"];
$id = $_POST["id"];
$stmt->execute();
header('Location: ../../users.php');
}
// Return to Users Page
if(isset($_POST['btn_cancel'])) {
header('Location: ../../users.php');
}
?>