Library Management System in PHP and MySQL
The Library Management System is a web-based system used in a library to manage items. It is a software system that helps in maintaining records of all library. A library management system is a Digital library software that keeps track of each book issued, returned, and added to the library.
You can find books in an instant, issue/reissue books quickly, and manage all the data efficiently and orderly using this system. The purpose of a library management system is to provide instant and accurate data regarding any type of book, thereby saving a lot of time and effort.
In this tutorial, you will learn to how manage Library Management System in PHP and MySQL. This is a very simple example, you can just copy-paste, and change it according to your requirements.
Before started to implement the Library Management System in PHP and MySQL, look files structure:
- library-management-system
- config
- Config.php
- class
- Author.php
- Books.php
- Category.php
- IssueBooks.php
- Publisher.php
- Rack.php
- User.php
- templates
- footer.php
- header.php
- left_menus.php
- menus.php
- css
- style.css
- js
- images
- author.js
- books.js
- category.js
- issue_books.js
- publisher.js
- rack.js
- user.js
- popup
- add-author.php
- add-book.php
- add-category.php
- add-issue-books.php
- add-publisher.php
- add-rack.php
- add-user.php
- config
- author.php
- author_action.php
- books.php
- books_action.php
- category.php
- category_action.php
- dashboard.php
- index.php
- issue_books.php
- issue_books_action.php
- logout.php
- publisher.php
- publisher_action.php
- rack.php
- rack_action.php
- user.php
- user_action.php
Features of library management system:
- Users/Members records Management
- Category records Management
- Author/Writer records Management
- Publisher records Management
- Rack records Management
- Book records Management
- Issue Books records Management
Step 1: Create the Database and Table
For this tutorial, you need a MySQL database with the following tables:
Create
user table to store user login details.|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE `user` ( `id` int(11) UNSIGNED NOT NULL, `first_name` varchar(255) DEFAULT NULL, `last_name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `password` varchar(64) NOT NULL, `role` enum('admin','user') DEFAULT 'admin' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; ALTER TABLE `user` ADD PRIMARY KEY (`id`); ALTER TABLE `user` MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT; |
Create
book table to store books details.|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `categoryid` int(11) NOT NULL, `authorid` int(11) NOT NULL, `rackid` int(11) NOT NULL, `name` text NOT NULL, `picture` varchar(250) NOT NULL, `publisherid` int(11) NOT NULL, `isbn` varchar(30) NOT NULL, `no_of_copy` int(5) NOT NULL, `status` enum('Enable','Disable') NOT NULL, `added_on` datetime NOT NULL DEFAULT current_timestamp(), `updated_on` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ALTER TABLE `book` ADD PRIMARY KEY (`bookid`); ALTER TABLE `book` MODIFY `bookid` int(11) NOT NULL AUTO_INCREMENT; |
Create
author table to store book author details.|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `author` ( `authorid` int(11) NOT NULL, `name` varchar(200) NOT NULL, `status` enum('Enable','Disable') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ALTER TABLE `author` ADD PRIMARY KEY (`authorid`); ALTER TABLE `author` MODIFY `authorid` int(11) NOT NULL AUTO_INCREMENT; |
Create
publisher table to store publisher details.|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `publisher` ( `publisherid` int(11) NOT NULL, `name` varchar(255) NOT NULL, `status` enum('Enable','Disable') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `publisher` ADD PRIMARY KEY (`publisherid`); ALTER TABLE `publisher` MODIFY `publisherid` int(11) NOT NULL AUTO_INCREMENT; |
Create
category table to store book category details.|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `category` ( `categoryid` int(11) NOT NULL, `name` varchar(200) NOT NULL, `status` enum('Enable','Disable') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ALTER TABLE `category` ADD PRIMARY KEY (`categoryid`); ALTER TABLE `category` MODIFY `categoryid` int(11) NOT NULL AUTO_INCREMENT; |
Create
rack table to store book location details.|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `rack` ( `rackid` int(11) NOT NULL, `name` varchar(200) NOT NULL, `status` enum('Enable','Disable') NOT NULL DEFAULT 'Enable' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ALTER TABLE `rack` ADD PRIMARY KEY (`rackid`); ALTER TABLE `rack` MODIFY `rackid` int(11) NOT NULL AUTO_INCREMENT; |
Create
issued_book table to store book issue details.|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE `issued_book` ( `issuebookid` int(11) NOT NULL, `bookid` int(11) NOT NULL, `userid` int(11) NOT NULL, `issue_date_time` datetime NOT NULL DEFAULT current_timestamp(), `expected_return_date` datetime NOT NULL, `return_date_time` datetime NOT NULL, `status` enum('Issued','Returned','Not Return') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ALTER TABLE `issued_book` ADD PRIMARY KEY (`issuebookid`); ALTER TABLE `issued_book` MODIFY `issuebookid` int(11) NOT NULL AUTO_INCREMENT; |
Create a Database configuration file named
Config.php inside config/ folder|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<?php /** * Description of Config Class * * @author Team TechArise * * @email info@techarise.com */ session_start(); class Config { private $host = 'localhost'; private $user = 'root'; private $password = ""; private $database = "lms_DB"; public function getConnection() { $conn = new mysqli($this->host, $this->user, $this->password, $this->database); if ($conn->connect_error) { die("Error failed to connect to MySQL: " . $conn->connect_error); } else { return $conn; } } } ?> |
Step 2: Create/Manage Users
i- Create a class file named
User.php inside class/ folder.|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 |
<?php /** * Description of User Class * * @author Team TechArise * * @email info@techarise.com */ class User { private $userTable = 'user'; private $conn; public function __construct($db) { $this->conn = $db; } public function login() { if ($this->email && $this->password) { $sqlQuery = " SELECT * FROM " . $this->userTable . " WHERE email = ? AND password = ?"; $stmt = $this->conn->prepare($sqlQuery); $password = md5($this->password); $stmt->bind_param("ss", $this->email, $password); $stmt->execute(); $result = $stmt->get_result(); if ($result->num_rows > 0) { $user = $result->fetch_assoc(); $_SESSION["userid"] = $user['id']; $_SESSION["role"] = $user['role']; $_SESSION["name"] = ucfirst($user['first_name']) . " " . ucfirst($user['last_name']);; $_SESSION["email"] = $user['email']; return 1; } else { return 0; } } else { return 0; } } public function loggedIn() { if (!empty($_SESSION["userid"])) { return 1; } else { return 0; } } public function isAdmin() { if (!empty($_SESSION["userid"]) && $_SESSION["role"] == 'admin') { return 1; } else { return 0; } } public function listUsers() { $sqlQuery = "SELECT id, first_name, last_name, email, password, role FROM " . $this->userTable . " "; if (!empty($_POST["search"]["value"])) { $sqlQuery .= ' WHERE (id LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR first_name LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR email LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR password LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR role LIKE "%' . $_POST["search"]["value"] . '%" '; } if (!empty($_POST["order"])) { $sqlQuery .= 'ORDER BY ' . $_POST['order']['0']['column'] . ' ' . $_POST['order']['0']['dir'] . ' '; } else { $sqlQuery .= 'ORDER BY id DESC '; } if ($_POST["length"] != -1) { $sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length']; } $stmt = $this->conn->prepare($sqlQuery); $stmt->execute(); $result = $stmt->get_result(); $stmtTotal = $this->conn->prepare($sqlQuery); $stmtTotal->execute(); $allResult = $stmtTotal->get_result(); $allRecords = $allResult->num_rows; $displayRecords = $result->num_rows; $records = array(); $count = 1; while ($user = $result->fetch_assoc()) { $rows = array(); $rows[] = $count; $rows[] = ucfirst($user['first_name']) . " " . ucfirst($user['last_name']); $rows[] = $user['email']; $rows[] = $user['role']; $rows[] = '<button type="button" name="update" id="' . $user["id"] . '" class="btn btn-primary btn-sm update"><span class="fa fa-edit" title="Edit"> Edit</span></button>'; $rows[] = '<button type="button" name="delete" id="' . $user["id"] . '" class="btn btn-danger btn-sm delete" ><span class="fa fa-trash-o" title="Delete"> Delete</span></button>'; $records[] = $rows; $count++; } $output = array( "draw" => intval($_POST["draw"]), "iTotalRecords" => $displayRecords, "iTotalDisplayRecords" => $allRecords, "data" => $records ); echo json_encode($output); } public function insert() { if ($this->role && $this->email && $this->password && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" INSERT INTO " . $this->userTable . "(`first_name`, `last_name`, `email`, `password`, `role`) VALUES(?, ?, ?, ?, ?)"); $this->role = htmlspecialchars(strip_tags($this->role)); $this->email = htmlspecialchars(strip_tags($this->email)); $this->first_name = htmlspecialchars(strip_tags($this->first_name)); $this->last_name = htmlspecialchars(strip_tags($this->last_name)); $this->password = md5($this->password); $stmt->bind_param("sssss", $this->first_name, $this->last_name, $this->email, $this->password, $this->role); if ($stmt->execute()) { return true; } } } public function update() { if ($this->role && $this->email && $_SESSION["userid"]) { $updatePass = ''; if ($this->password) { $this->password = md5($this->password); $updatePass = ", password = '" . $this->password . "'"; } $stmt = $this->conn->prepare(" UPDATE " . $this->userTable . " SET first_name = ?, last_name = ?, email = ?, role = ? $updatePass WHERE id = ?"); $this->role = htmlspecialchars(strip_tags($this->role)); $this->email = htmlspecialchars(strip_tags($this->email)); $this->first_name = htmlspecialchars(strip_tags($this->first_name)); $this->last_name = htmlspecialchars(strip_tags($this->last_name)); $stmt->bind_param("ssssi", $this->first_name, $this->last_name, $this->email, $this->role, $this->id); if ($stmt->execute()) { return true; } } } public function delete() { if ($this->id && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" DELETE FROM " . $this->userTable . " WHERE id = ?"); $this->id = htmlspecialchars(strip_tags($this->id)); $stmt->bind_param("i", $this->id); if ($stmt->execute()) { return true; } } } public function getUserDetails() { if ($this->user_id && $_SESSION["userid"]) { $sqlQuery = " SELECT id, first_name, last_name, email, password, role FROM " . $this->userTable . " WHERE id = ? "; $stmt = $this->conn->prepare($sqlQuery); $stmt->bind_param("i", $this->user_id); $stmt->execute(); $result = $stmt->get_result(); $records = array(); while ($user = $result->fetch_assoc()) { $rows = array(); $rows['id'] = $user['id']; $rows['first_name'] = $user['first_name']; $rows['last_name'] = $user['last_name']; $rows['email'] = $user['email']; $rows['role'] = $user['role']; $records[] = $rows; } $output = array( "data" => $records ); echo json_encode($output); } } function getUsersList() { $stmt = $this->conn->prepare(" SELECT id, first_name, last_name FROM " . $this->userTable . " WHERE role = 'user'"); $stmt->execute(); $result = $stmt->get_result(); return $result; } function getTotalUsers() { $stmt = $this->conn->prepare(" SELECT * FROM " . $this->userTable); $stmt->execute(); $result = $stmt->get_result(); return $result->num_rows; } } ?> |
ii- Create HTML file named
user.php|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
<?php include_once 'config/Config.php'; include_once 'class/User.php'; $conn = new Config(); $db = $conn->getConnection(); $user = new User($db); if (!$user->loggedIn()) { header("Location: index.php"); } include('templates/header.php'); ?> <div class="container-fluid"> <div class="row"> <?php include('templates/left_menus.php'); ?> <div class="col-md-10 col-lg-10"> <h2>Users</h2> <div class="panel-heading"> <div class="row"> <div class="col-md-10"> <h3 class="panel-title"></h3> </div> <div class="col-md-2" align="right"> <button type="button" id="addUser" class="btn btn-info btn-sm" title="Add User"><span class="fa fa-plus"> Add</span></button> </div> </div> </div> <table id="userListing" class="table table-striped table-bordered"> <thead> <tr> <th>Sn.</th> <th>Name</th> <th>Email</th> <th>Role</th> <th></th> <th></th> </tr> </thead> </table> </div> </div> </div> <?php include('popup/add-user.php'); include('templates/footer.php'); ?> |
iii- Create a JS file named
user_action.php to handle GET/POST requests|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
<?php include_once 'config/Config.php'; include_once 'class/User.php'; $conn = new Config(); $db = $conn->getConnection(); $user = new User($db); if(!empty($_POST['action']) && $_POST['action'] == 'listUsers') { $user->listUsers(); } if(!empty($_POST['action']) && $_POST['action'] == 'getUserDetails') { $user->user_id = $_POST["id"]; $user->getUserDetails(); } if(!empty($_POST['action']) && $_POST['action'] == 'addUser') { $user->role = $_POST["role"]; $user->first_name = $_POST["first_name"]; $user->last_name = $_POST["last_name"]; $user->email = $_POST["email"]; $user->password = $_POST["password"]; $user->insert(); } if(!empty($_POST['action']) && $_POST['action'] == 'updateUser') { $user->id = $_POST["id"]; $user->role = $_POST["role"]; $user->first_name = $_POST["first_name"]; $user->last_name = $_POST["last_name"]; $user->email = $_POST["email"]; $user->password = $_POST["password"]; $user->update(); } if(!empty($_POST['action']) && $_POST['action'] == 'deleteUser') { $user->id = $_POST["id"]; $user->delete(); } ?> |
iv- Create a JS file named
user.js to handle Ajax requests, inside the js/ folder|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
jQuery(document).ready(function () { var userRecords = jQuery('#userListing').DataTable({ "lengthChange": false, "processing": true, "serverSide": true, "bFilter": false, 'serverMethod': 'post', "order": [], "ajax": { url: "user_action.php", type: "POST", data: { action: 'listUsers' }, dataType: "json" }, "columnDefs": [ { "targets": [0, 4, 5], "orderable": false, }, ], "pageLength": 10 }); jQuery('#addUser').click(function () { jQuery('#userModal').modal({ backdrop: 'static', keyboard: false }); jQuery("#userModal").on("shown.bs.modal", function () { jQuery('#userForm')[0].reset(); jQuery('.modal-title').html("<i class='fa fa-plus'></i> Add User"); jQuery('#action').val('addUser'); jQuery('#save').val('Save'); }); }); jQuery("#userListing").on('click', '.update', function () { var id = jQuery(this).attr("id"); var action = 'getUserDetails'; jQuery.ajax({ url: 'user_action.php', method: "POST", data: { id: id, action: action }, dataType: "json", success: function (respData) { jQuery("#userModal").on("shown.bs.modal", function () { jQuery('#userForm')[0].reset(); respData.data.forEach(function (item) { jQuery('#id').val(item['id']); jQuery('#role').val(item['role']); jQuery('#first_name').val(item['first_name']); jQuery('#last_name').val(item['last_name']); jQuery('#email').val(item['email']); }); jQuery('.modal-title').html("<i class='fa fa-plus'></i> Edit User"); jQuery('#action').val('updateUser'); jQuery('#save').val('Save'); }).modal({ backdrop: 'static', keyboard: false }); } }); }); jQuery("#userModal").on('submit', '#userForm', function (event) { event.preventDefault(); jQuery('#save').attr('disabled', 'disabled'); var formData = jQuery(this).serialize(); jQuery.ajax({ url: "user_action.php", method: "POST", data: formData, success: function (data) { jQuery('#userForm')[0].reset(); jQuery('#userModal').modal('hide'); jQuery('#save').attr('disabled', false); userRecords.ajax.reload(); } }) }); jQuery("#userListing").on('click', '.delete', function () { var id = jQuery(this).attr("id"); var action = "deleteUser"; if (confirm("Are you sure you want to delete this record?")) { jQuery.ajax({ url: "user_action.php", method: "POST", data: { id: id, action: action }, success: function (data) { userRecords.ajax.reload(); } }) } else { return false; } }); }); |
Step 3: Manage Books
i- Create a class file named
Books.php inside class/ folder.|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 |
<?php /** * Description of Books Class * * @author Team TechArise * * @email info@techarise.com */ class Books { private $bookTable = 'book'; private $issuedBookTable = 'issued_book'; private $categoryTable = 'category'; private $authorTable = 'author'; private $publisherTable = 'publisher'; private $rackTable = 'rack'; private $conn; public function __construct($db) { $this->conn = $db; } public function listBook() { $sqlQuery = "SELECT book.bookid, book.picture, book.name, book.status, book.isbn, book.no_of_copy, book.updated_on, author.name as author_name, category.name AS category_name, rack.name As rack_name, publisher.name AS publisher_name FROM " . $this->bookTable . " book LEFT JOIN " . $this->authorTable . " author ON author.authorid = book.authorid LEFT JOIN " . $this->categoryTable . " category ON category.categoryid = book.categoryid LEFT JOIN " . $this->rackTable . " rack ON rack.rackid = book.rackid LEFT JOIN " . $this->publisherTable . " publisher ON publisher.publisherid = book.publisherid "; if (!empty($_POST["search"]["value"])) { $sqlQuery .= ' WHERE (book.bookid LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR book.name LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR book.status LIKE "%' . $_POST["search"]["value"] . '%" '; } if (!empty($_POST["order"])) { $sqlQuery .= 'ORDER BY ' . $_POST['order']['0']['column'] . ' ' . $_POST['order']['0']['dir'] . ' '; } else { $sqlQuery .= 'ORDER BY book.bookid DESC '; } if ($_POST["length"] != -1) { $sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length']; } $stmt = $this->conn->prepare($sqlQuery); $stmt->execute(); $result = $stmt->get_result(); $stmtTotal = $this->conn->prepare($sqlQuery); $stmtTotal->execute(); $allResult = $stmtTotal->get_result(); $allRecords = $allResult->num_rows; $displayRecords = $result->num_rows; $records = array(); $count = 1; while ($book = $result->fetch_assoc()) { $rows = array(); if (!$book['picture']) { $book['picture'] = 'placeholder.png'; } $rows[] = '<img src="images/' . $book['picture'] . '" width="50" height="50">'; $rows[] = ucfirst($book['name']); $rows[] = ucfirst($book['isbn']); $rows[] = ucfirst($book['author_name']); $rows[] = ucfirst($book['publisher_name']); $rows[] = ucfirst($book['category_name']); $rows[] = ucfirst($book['rack_name']); $rows[] = ucfirst($book['no_of_copy']); $rows[] = $book['status']; $rows[] = $book['updated_on']; $rows[] = '<button type="button" name="update" id="' . $book["bookid"] . '" class="btn btn-primary btn-sm update"><span class="fa fa-edit" title="Edit"> </span></button> <button type="button" name="delete" id="' . $book["bookid"] . '" class="btn btn-danger btn-sm delete" ><span class="fa fa-trash-o" title="Delete"> </span></button>'; $records[] = $rows; $count++; } $output = array( "draw" => intval($_POST["draw"]), "iTotalRecords" => $displayRecords, "iTotalDisplayRecords" => $allRecords, "data" => $records ); echo json_encode($output); } public function insert() { if ($this->name && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" INSERT INTO " . $this->bookTable . "(`name`, `status`, `isbn`, `no_of_copy`, `categoryid`, `authorid`, `rackid`, `publisherid`) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"); $this->name = htmlspecialchars(strip_tags($this->name)); $this->isbn = htmlspecialchars(strip_tags($this->isbn)); $this->no_of_copy = htmlspecialchars(strip_tags($this->no_of_copy)); $this->author = htmlspecialchars(strip_tags($this->author)); $this->publisher = htmlspecialchars(strip_tags($this->publisher)); $this->category = htmlspecialchars(strip_tags($this->category)); $this->rack = htmlspecialchars(strip_tags($this->rack)); $this->status = htmlspecialchars(strip_tags($this->status)); $stmt->bind_param("sssiiiii", $this->name, $this->status, $this->isbn, $this->no_of_copy, $this->category, $this->author, $this->rack, $this->publisher); if ($stmt->execute()) { return true; } } } public function update() { if ($this->name && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" UPDATE " . $this->bookTable . " SET name = ?, status = ?, isbn = ?, no_of_copy = ?, categoryid = ?, authorid = ?, rackid = ?, publisherid = ? WHERE bookid = ?"); $this->name = htmlspecialchars(strip_tags($this->name)); $this->isbn = htmlspecialchars(strip_tags($this->isbn)); $this->no_of_copy = htmlspecialchars(strip_tags($this->no_of_copy)); $this->author = htmlspecialchars(strip_tags($this->author)); $this->publisher = htmlspecialchars(strip_tags($this->publisher)); $this->category = htmlspecialchars(strip_tags($this->category)); $this->rack = htmlspecialchars(strip_tags($this->rack)); $this->status = htmlspecialchars(strip_tags($this->status)); $this->bookid = htmlspecialchars(strip_tags($this->bookid)); $stmt->bind_param("sssiiiiii", $this->name, $this->status, $this->isbn, $this->no_of_copy, $this->category, $this->author, $this->rack, $this->publisher, $this->bookid); if ($stmt->execute()) { return true; } } } public function delete() { if ($this->bookid && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" DELETE FROM " . $this->bookTable . " WHERE bookid = ?"); $this->bookid = htmlspecialchars(strip_tags($this->bookid)); $stmt->bind_param("i", $this->bookid); if ($stmt->execute()) { return true; } } } public function getBookDetails() { if ($this->bookid && $_SESSION["userid"]) { $sqlQuery = "SELECT book.bookid, book.picture, book.name, book.status, book.isbn, book.no_of_copy, book.updated_on, author.authorid, category.categoryid, rack.rackid, publisher.publisherid FROM " . $this->bookTable . " book LEFT JOIN " . $this->authorTable . " author ON author.authorid = book.authorid LEFT JOIN " . $this->categoryTable . " category ON category.categoryid = book.categoryid LEFT JOIN " . $this->rackTable . " rack ON rack.rackid = book.rackid LEFT JOIN " . $this->publisherTable . " publisher ON publisher.publisherid = book.publisherid WHERE bookid = ? "; $stmt = $this->conn->prepare($sqlQuery); $stmt->bind_param("i", $this->bookid); $stmt->execute(); $result = $stmt->get_result(); $records = array(); while ($book = $result->fetch_assoc()) { $rows = array(); $rows['bookid'] = $book['bookid']; $rows['name'] = $book['name']; $rows['status'] = $book['status']; $rows['isbn'] = $book['isbn']; $rows['no_of_copy'] = $book['no_of_copy']; $rows['categoryid'] = $book['categoryid']; $rows['rackid'] = $book['rackid']; $rows['publisherid'] = $book['publisherid']; $rows['authorid'] = $book['authorid']; $records[] = $rows; } $output = array( "data" => $records ); echo json_encode($output); } } function getAuthorList() { $stmt = $this->conn->prepare(" SELECT authorid, name FROM " . $this->authorTable); $stmt->execute(); $result = $stmt->get_result(); return $result; } function getCategoryList() { $stmt = $this->conn->prepare(" SELECT categoryid, name FROM " . $this->categoryTable); $stmt->execute(); $result = $stmt->get_result(); return $result; } function getPublisherList() { $stmt = $this->conn->prepare(" SELECT publisherid, name FROM " . $this->publisherTable); $stmt->execute(); $result = $stmt->get_result(); return $result; } function getRackList() { $stmt = $this->conn->prepare(" SELECT rackid, name FROM " . $this->rackTable); $stmt->execute(); $result = $stmt->get_result(); return $result; } function getBookList() { $stmt = $this->conn->prepare(" SELECT book.bookid, book.name, issue_book.status FROM " . $this->bookTable . " book LEFT JOIN " . $this->issuedBookTable . " issue_book ON issue_book.bookid = book.bookid"); $stmt->execute(); $result = $stmt->get_result(); return $result; } function getTotalBooks() { $stmt = $this->conn->prepare(" SELECT * FROM " . $this->bookTable); $stmt->execute(); $result = $stmt->get_result(); return $result->num_rows; } function getTotalIssuedBooks() { $stmt = $this->conn->prepare(" SELECT * FROM " . $this->issuedBookTable . " WHERE status = 'Issued'"); $stmt->execute(); $result = $stmt->get_result(); return $result->num_rows; } function getTotalReturnedBooks() { $stmt = $this->conn->prepare(" SELECT * FROM " . $this->issuedBookTable . " WHERE status = 'Returned'"); $stmt->execute(); $result = $stmt->get_result(); return $result->num_rows; } } ?> |
ii- Create HTML file named
books.php|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
<?php include_once 'config/Config.php'; include_once 'class/User.php'; include_once 'class/Books.php'; $conn = new Config(); $db = $conn->getConnection(); $user = new User($db); if (!$user->loggedIn()) { header("Location: index.php"); } $book = new Books($db); include('templates/header.php'); ?> <div class="container-fluid"> <div class="row"> <?php include('templates/left_menus.php'); ?> <div class="col-md-10 col-lg-10"> <h2>Manage Books</h2> <div class="panel-heading"> <div class="row"> <div class="col-md-10"> <h3 class="panel-title"></h3> </div> <div class="col-md-2" align="right"> <button type="button" id="addBook" class="btn btn-info btn-sm" title="Add book"><span class="fa fa-plus"> Add Book</span></button> </div> </div> </div> <table id="bookListing" class="table table-striped table-bordered"> <thead> <tr> <td style="width: 5%;">#</td> <th style="width: 10%;">Book</th> <th style="width: 10%;">ISBN</th> <th style="width: 10%;">Author</th> <th style="width: 10%;">Publisher</th> <th style="width: 10%;">Category</th> <th style="width: 8%;">Rack</th> <th style="width: 10%;">No of copy</th> <th style="width: 5%;">Status</th> <th style="width: 10%;">Updated On</th> <th style="width: 12%;">Action</th> </tr> </thead> </table> </div> </div> </div> <?php include('popup/add-book.php'); include('templates/footer.php'); ?> |
iii- Create a JS file named
books_action.php to handle GET/POST requests|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
<?php include_once 'config/Config.php'; include_once 'class/Books.php'; $conn = new Config(); $db = $conn->getConnection(); $book = new Books($db); if(!empty($_POST['action']) && $_POST['action'] == 'listBook') { $book->listBook(); } if(!empty($_POST['action']) && $_POST['action'] == 'getBookDetails') { $book->bookid = $_POST["bookid"]; $book->getBookDetails(); } if(!empty($_POST['action']) && $_POST['action'] == 'addBook') { $book->name = $_POST["name"]; $book->isbn = $_POST["isbn"]; $book->no_of_copy = $_POST["no_of_copy"]; $book->author = $_POST["author"]; $book->publisher = $_POST["publisher"]; $book->category = $_POST["category"]; $book->rack = $_POST["rack"]; $book->status = $_POST["status"]; $book->insert(); } if(!empty($_POST['action']) && $_POST['action'] == 'updateBook') { $book->bookid = $_POST["bookid"]; $book->name = $_POST["name"]; $book->isbn = $_POST["isbn"]; $book->no_of_copy = $_POST["no_of_copy"]; $book->author = $_POST["author"]; $book->publisher = $_POST["publisher"]; $book->category = $_POST["category"]; $book->rack = $_POST["rack"]; $book->status = $_POST["status"]; $book->update(); } if(!empty($_POST['action']) && $_POST['action'] == 'deleteBook') { $book->bookid = $_POST["bookid"]; $book->delete(); } ?> |
iv- Create a JS file named
books.js to handle Ajax requests, inside the js/ folder|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
jQuery(document).ready(function () { var bookRecords = jQuery('#bookListing').DataTable({ "lengthChange": false, "processing": true, "serverSide": true, "bFilter": false, 'serverMethod': 'post', "order": [], "ajax": { url: "books_action.php", type: "POST", data: { action: 'listBook' }, dataType: "json" }, "columnDefs": [ { "targets": [0, 9, 10], "orderable": false, }, ], "pageLength": 10 }); jQuery('#addBook').click(function () { jQuery('#bookModal').modal({ backdrop: 'static', keyboard: false }); jQuery("#bookModal").on("shown.bs.modal", function () { jQuery('#bookForm')[0].reset(); jQuery('.modal-title').html("<i class='fa fa-plus'></i> Add Book"); jQuery('#action').val('addBook'); jQuery('#save').val('Save'); }); }); jQuery("#bookListing").on('click', '.update', function () { var bookid = jQuery(this).attr("id"); var action = 'getBookDetails'; jQuery.ajax({ url: 'books_action.php', method: "POST", data: { bookid: bookid, action: action }, dataType: "json", success: function (respData) { jQuery("#bookModal").on("shown.bs.modal", function () { jQuery('#bookForm')[0].reset(); respData.data.forEach(function (item) { jQuery('#bookid').val(item['bookid']); jQuery('#name').val(item['name']); jQuery('#isbn').val(item['isbn']); jQuery('#no_of_copy').val(item['no_of_copy']); jQuery('#category').val(item['categoryid']); jQuery('#rack').val(item['rackid']); jQuery('#publisher').val(item['publisherid']); jQuery('#author').val(item['authorid']); jQuery('#status').val(item['status']); }); jQuery('.modal-title').html("<i class='fa fa-plus'></i> Edit Book"); jQuery('#action').val('updateBook'); jQuery('#save').val('Save'); }).modal({ backdrop: 'static', keyboard: false }); } }); }); jQuery("#bookModal").on('submit', '#bookForm', function (event) { event.preventDefault(); jQuery('#save').attr('disabled', 'disabled'); var formData = jQuery(this).serialize(); jQuery.ajax({ url: "books_action.php", method: "POST", data: formData, success: function (data) { jQuery('#bookForm')[0].reset(); jQuery('#bookModal').modal('hide'); jQuery('#save').attr('disabled', false); bookRecords.ajax.reload(); } }) }); jQuery("#bookListing").on('click', '.delete', function () { var bookid = jQuery(this).attr("id"); var action = "deleteBook"; if (confirm("Are you sure you want to delete this record?")) { jQuery.ajax({ url: "books_action.php", method: "POST", data: { bookid: bookid, action: action }, success: function (data) { bookRecords.ajax.reload(); } }) } else { return false; } }); }); |
Step 4: Manage Issued Books
i- Create a class file named
IssueBooks.php inside class/ folder.|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 |
<?php /** * Description of IssueBooks Class * * @author Team TechArise * * @email info@techarise.com */ class IssueBooks { private $issuedBookTable = 'issued_book'; private $bookTable = 'book'; private $userTable = 'user'; private $conn; public function __construct($db) { $this->conn = $db; } public function listIssuedBook() { $sqlQuery = "SELECT issue_book.issuebookid, issue_book.issue_date_time, issue_book.expected_return_date, issue_book.return_date_time, issue_book.status, book.name As book_name, book.isbn, user.first_name, user.last_name FROM " . $this->issuedBookTable . " issue_book LEFT JOIN " . $this->bookTable . " book ON book.bookid = issue_book.bookid LEFT JOIN " . $this->userTable . " user ON user.id = issue_book.userid "; if (!empty($_POST["search"]["value"])) { $sqlQuery .= ' WHERE (issue_book.issuebookid LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR issue_book.issue_date_time LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR issue_book.status LIKE "%' . $_POST["search"]["value"] . '%" '; } if (!empty($_POST["order"])) { $sqlQuery .= 'ORDER BY ' . $_POST['order']['0']['column'] . ' ' . $_POST['order']['0']['dir'] . ' '; } else { $sqlQuery .= 'ORDER BY issue_book.issuebookid DESC '; } if ($_POST["length"] != -1) { $sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length']; } $stmt = $this->conn->prepare($sqlQuery); $stmt->execute(); $result = $stmt->get_result(); $stmtTotal = $this->conn->prepare($sqlQuery); $stmtTotal->execute(); $allResult = $stmtTotal->get_result(); $allRecords = $allResult->num_rows; $displayRecords = $result->num_rows; $records = array(); $count = 1; while ($issueBook = $result->fetch_assoc()) { $rows = array(); $rows[] = $count; $rows[] = ucfirst($issueBook['book_name']); $rows[] = ucfirst($issueBook['isbn']); $rows[] = ucfirst($issueBook['first_name']) . " " . ucfirst($issueBook['last_name']); $rows[] = ucfirst($issueBook['issue_date_time']); $rows[] = ucfirst($issueBook['expected_return_date']); $rows[] = ucfirst($issueBook['return_date_time']); $rows[] = $issueBook['status']; $rows[] = '<button type="button" name="update" id="' . $issueBook["issuebookid"] . '" class="btn btn-primary btn-sm update"><span class="fa fa-edit" title="Edit"> Edit</span></button>'; $rows[] = '<button type="button" name="delete" id="' . $issueBook["issuebookid"] . '" class="btn btn-danger btn-sm delete" ><span class="fa fa-trash-o" title="Delete"> Delete</span></button>'; $records[] = $rows; $count++; } $output = array( "draw" => intval($_POST["draw"]), "iTotalRecords" => $displayRecords, "iTotalDisplayRecords" => $allRecords, "data" => $records ); echo json_encode($output); } public function insert() { if ($this->book && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" INSERT INTO " . $this->issuedBookTable . "(`bookid`, `userid`, `expected_return_date`, `return_date_time`, `status`) VALUES(?, ?, ?, ?, ?)"); $this->book = htmlspecialchars(strip_tags($this->book)); $this->users = htmlspecialchars(strip_tags($this->users)); $this->expected_return_date = htmlspecialchars(strip_tags($this->expected_return_date)); $this->return_date = htmlspecialchars(strip_tags($this->return_date)); $this->status = htmlspecialchars(strip_tags($this->status)); $stmt->bind_param("iisss", $this->book, $this->users, $this->expected_return_date, $this->return_date, $this->status); if ($stmt->execute()) { return true; } } } public function update() { if ($this->issuebookid && $this->book && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" UPDATE " . $this->issuedBookTable . " SET bookid = ?, userid = ?, expected_return_date = ?, return_date_time = ?, status = ? WHERE issuebookid = ?"); $this->book = htmlspecialchars(strip_tags($this->book)); $this->users = htmlspecialchars(strip_tags($this->users)); $this->expected_return_date = htmlspecialchars(strip_tags($this->expected_return_date)); $this->return_date = htmlspecialchars(strip_tags($this->return_date)); $this->status = htmlspecialchars(strip_tags($this->status)); $stmt->bind_param("iisssi", $this->book, $this->users, $this->expected_return_date, $this->return_date, $this->status, $this->issuebookid); if ($stmt->execute()) { return true; } } } public function delete() { if ($this->issuebookid && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" DELETE FROM " . $this->issuedBookTable . " WHERE issuebookid = ?"); $this->issuebookid = htmlspecialchars(strip_tags($this->issuebookid)); $stmt->bind_param("i", $this->issuebookid); if ($stmt->execute()) { return true; } } } public function getIssueBookDetails() { if ($this->issuebookid && $_SESSION["userid"]) { $sqlQuery = "SELECT issue_book.issuebookid, issue_book.issue_date_time, issue_book.expected_return_date, issue_book.return_date_time, issue_book.status, issue_book.bookid, issue_book.userid, book.name AS book_name FROM " . $this->issuedBookTable . " issue_book LEFT JOIN " . $this->bookTable . " book ON book.bookid = issue_book.bookid LEFT JOIN " . $this->userTable . " user ON user.id = issue_book.userid WHERE issue_book.issuebookid = ?"; $stmt = $this->conn->prepare($sqlQuery); $stmt->bind_param("i", $this->issuebookid); $stmt->execute(); $result = $stmt->get_result(); $records = array(); while ($issueBook = $result->fetch_assoc()) { $rows = array(); $rows['issuebookid'] = $issueBook['issuebookid']; $rows['bookid'] = $issueBook['bookid']; $rows['book_name'] = $issueBook['book_name']; $rows['status'] = $issueBook['status']; $rows['userid'] = $issueBook['userid']; $rows['expected_return_date'] = date('Y-m-d\TH:i:s', strtotime($issueBook['expected_return_date'])); $rows['return_date_time'] = date('Y-m-d\TH:i:s', strtotime($issueBook['return_date_time'])); $records[] = $rows; } $output = array( "data" => $records ); echo json_encode($output); } } } ?> |
ii- Create HTML file named
issue_books.php|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
<?php include_once 'config/Config.php'; include_once 'class/User.php'; include_once 'class/Books.php'; $conn = new Config(); $db = $conn->getConnection(); $user = new User($db); if (!$user->loggedIn()) { header("Location: index.php"); } $book = new Books($db); include('templates/header.php'); ?> <div class="container-fluid"> <div class="row"> <?php include('templates/left_menus.php'); ?> <div class="col-md-10 col-lg-10"> <h2>Manage Issue Books</h2> <div class="panel-heading"> <div class="row"> <div class="col-md-10"> <h3 class="panel-title"></h3> </div> <div class="col-md-2" align="right"> <button type="button" id="issueBook" class="btn btn-info btn-sm" title="Issue Book"><span class="fa fa-plus"> Issue Book</span></button> </div> </div> </div> <table id="issuedBookListing" class="table table-striped table-bordered"> <thead> <tr> <th>Id</th> <th>Book</th> <th>ISBN</th> <th>User</th> <th>Issue Date</th> <th>Expected Return</th> <th>Return Date</th> <th>Status</th> <th></th> <th></th> </tr> </thead> </table> </div> </div> </div> <?php include('popup/add-issue-books.php'); include('templates/footer.php'); ?> |
iii- Create a JS file named
issue_books_action.php to handle GET/POST requests|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
<?php include_once 'config/Config.php'; include_once 'class/IssueBooks.php'; $conn = new Config(); $db = $conn->getConnection(); $issueBook = new IssueBooks($db); if(!empty($_POST['action']) && $_POST['action'] == 'listIssuedBook') { $issueBook->listIssuedBook(); } if(!empty($_POST['action']) && $_POST['action'] == 'getIssueBookDetails') { $issueBook->issuebookid = $_POST["issuebookid"]; $issueBook->getIssueBookDetails(); } if(!empty($_POST['action']) && $_POST['action'] == 'issueBook') { $issueBook->book = $_POST["book"]; $issueBook->users = $_POST["users"]; $issueBook->expected_return_date = $_POST["expected_return_date"]; $issueBook->return_date = $_POST["return_date"]; $issueBook->status = $_POST["status"]; $issueBook->insert(); } if(!empty($_POST['action']) && $_POST['action'] == 'updateIssueBook') { $issueBook->issuebookid = $_POST["issuebookid"]; $issueBook->book = $_POST["book"]; $issueBook->users = $_POST["users"]; $issueBook->expected_return_date = $_POST["expected_return_date"]; $issueBook->return_date = $_POST["return_date"]; $issueBook->status = $_POST["status"]; $issueBook->update(); } if(!empty($_POST['action']) && $_POST['action'] == 'deleteIssueBook') { $issueBook->issuebookid = $_POST["issuebookid"]; $issueBook->delete(); } ?> |
iv- Create a JS file named
issue_books.js to handle Ajax requests, inside the js/ folder|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
jQuery(document).ready(function () { var issuedBookRecords = jQuery('#issuedBookListing').DataTable({ "lengthChange": false, "processing": true, "serverSide": true, "bFilter": false, 'serverMethod': 'post', "order": [], "ajax": { url: "issue_books_action.php", type: "POST", data: { action: 'listIssuedBook' }, dataType: "json" }, "columnDefs": [ { "targets": [0, 8, 9], "orderable": false, }, ], "pageLength": 10 }); jQuery('#issueBook').click(function () { jQuery('#issuedBookModal').modal({ backdrop: 'static', keyboard: false }); jQuery("#issuedBookModal").on("shown.bs.modal", function () { jQuery('#issuedBookForm')[0].reset(); jQuery('.modal-title').html("<i class='fa fa-plus'></i> Issue Book"); jQuery('#action').val('issueBook'); jQuery('#save').val('Save'); }); }); jQuery("#issuedBookListing").on('click', '.update', function () { var issuebookid = jQuery(this).attr("id"); var action = 'getIssueBookDetails'; jQuery.ajax({ url: 'issue_books_action.php', method: "POST", data: { issuebookid: issuebookid, action: action }, dataType: "json", success: function (respData) { jQuery("#issuedBookModal").on("shown.bs.modal", function () { jQuery('#issuedBookForm')[0].reset(); respData.data.forEach(function (item) { jQuery('#issuebookid').val(item['issuebookid']); jQuery('#book').val(item['bookid']); jQuery('#users').val(item['userid']); jQuery('#expected_return_date').val(item['expected_return_date']); jQuery('#return_date').val(item['return_date_time']); jQuery('#status').val(item['status']); }); jQuery('.modal-title').html("<i class='fa fa-plus'></i> Edit issued Book"); jQuery('#action').val('updateIssueBook'); jQuery('#save').val('Save'); }).modal({ backdrop: 'static', keyboard: false }); } }); }); jQuery("#issuedBookModal").on('submit', '#issuedBookForm', function (event) { event.preventDefault(); jQuery('#save').attr('disabled', 'disabled'); var formData = jQuery(this).serialize(); jQuery.ajax({ url: "issue_books_action.php", method: "POST", data: formData, success: function (data) { jQuery('#issuedBookForm')[0].reset(); jQuery('#issuedBookModal').modal('hide'); jQuery('#save').attr('disabled', false); issuedBookRecords.ajax.reload(); } }) }); jQuery("#issuedBookListing").on('click', '.delete', function () { var issuebookid = jQuery(this).attr("id"); var action = "deleteIssueBook"; if (confirm("Are you sure you want to delete this record?")) { jQuery.ajax({ url: "issue_books_action.php", method: "POST", data: { issuebookid: issuebookid, action: action }, success: function (data) { issuedBookRecords.ajax.reload(); } }) } else { return false; } }); }); |
Step 5: Manage Book Rack
i- Create a class file named
Rack.php inside class/ folder.|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 |
<?php /** * Description of Rack Class * * @author Team TechArise * * @email info@techarise.com */ class Rack { private $rackTable = 'rack'; private $conn; public function __construct($db) { $this->conn = $db; } public function listRack() { $sqlQuery = "SELECT rackid, name, status FROM " . $this->rackTable . " "; if (!empty($_POST["search"]["value"])) { $sqlQuery .= ' WHERE (rackid LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR name LIKE "%' . $_POST["search"]["value"] . '%" '; $sqlQuery .= ' OR status LIKE "%' . $_POST["search"]["value"] . '%" '; } if (!empty($_POST["order"])) { $sqlQuery .= 'ORDER BY ' . $_POST['order']['0']['column'] . ' ' . $_POST['order']['0']['dir'] . ' '; } else { $sqlQuery .= 'ORDER BY rackid DESC '; } if ($_POST["length"] != -1) { $sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length']; } $stmt = $this->conn->prepare($sqlQuery); $stmt->execute(); $result = $stmt->get_result(); $stmtTotal = $this->conn->prepare($sqlQuery); $stmtTotal->execute(); $allResult = $stmtTotal->get_result(); $allRecords = $allResult->num_rows; $displayRecords = $result->num_rows; $records = array(); $count = 1; while ($rack = $result->fetch_assoc()) { $rows = array(); $rows[] = $count; $rows[] = ucfirst($rack['name']); $rows[] = $rack['status']; $rows[] = '<button type="button" name="update" id="' . $rack["rackid"] . '" class="btn btn-primary btn-sm update"><span class="fa fa-edit" title="Edit"> Edit</span></button>'; $rows[] = '<button type="button" name="delete" id="' . $rack["rackid"] . '" class="btn btn-danger btn-sm delete" ><span class="fa fa-trash-o" title="Delete"> Delete</span></button>'; $records[] = $rows; $count++; } $output = array( "draw" => intval($_POST["draw"]), "iTotalRecords" => $displayRecords, "iTotalDisplayRecords" => $allRecords, "data" => $records ); echo json_encode($output); } public function insert() { if ($this->name && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" INSERT INTO " . $this->rackTable . "(`name`, `status`) VALUES(?, ?)"); $this->name = htmlspecialchars(strip_tags($this->name)); $this->status = htmlspecialchars(strip_tags($this->status)); $stmt->bind_param("ss", $this->name, $this->status); if ($stmt->execute()) { return true; } } } public function update() { if ($this->name && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" UPDATE " . $this->rackTable . " SET name = ?, status = ? WHERE rackid = ?"); $this->name = htmlspecialchars(strip_tags($this->name)); $this->status = htmlspecialchars(strip_tags($this->status)); $this->rackid = htmlspecialchars(strip_tags($this->rackid)); $stmt->bind_param("ssi", $this->name, $this->status, $this->rackid); if ($stmt->execute()) { return true; } } } public function delete() { if ($this->rackid && $_SESSION["userid"]) { $stmt = $this->conn->prepare(" DELETE FROM " . $this->rackTable . " WHERE rackid = ?"); $this->rackid = htmlspecialchars(strip_tags($this->rackid)); $stmt->bind_param("i", $this->rackid); if ($stmt->execute()) { return true; } } } public function getRackDetails() { if ($this->rackid && $_SESSION["userid"]) { $sqlQuery = " SELECT rackid, name, status FROM " . $this->rackTable . " WHERE rackid = ? "; $stmt = $this->conn->prepare($sqlQuery); $stmt->bind_param("i", $this->rackid); $stmt->execute(); $result = $stmt->get_result(); $records = array(); while ($rack = $result->fetch_assoc()) { $rows = array(); $rows['rackid'] = $rack['rackid']; $rows['name'] = $rack['name']; $rows['status'] = $rack['status']; $records[] = $rows; } $output = array( "data" => $records ); echo json_encode($output); } } } ?> |
ii- Create HTML file named
rack.php|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
<?php include_once 'config/Config.php'; include_once 'class/User.php'; $conn = new Config(); $db = $conn->getConnection(); $user = new User($db); if (!$user->loggedIn()) { header("Location: index.php"); } include('templates/header.php'); ?> <div class="container-fluid"> <div class="row"> <?php include('templates/left_menus.php'); ?> <div class="col-md-10 col-lg-10"> <h2>Rack Location List</h2> <div class="panel-heading"> <div class="row"> <div class="col-md-10"> <h3 class="panel-title"></h3> </div> <div class="col-md-2" align="right"> <button type="button" id="addRack" class="btn btn-info btn-sm" title="Add Rack"><span class="fa fa-plus"> Add</span></button> </div> </div> </div> <table id="rackListing" class="table table-striped table-bordered"> <thead> <tr> <th>Sn.</th> <th>Name</th> <th>Status</th> <th></th> <th></th> </tr> </thead> </table> </div> </div> </div> <?php include('popup/add-rack.php'); include('templates/footer.php'); ?> |
iii- Create a JS file named
rack_action.php to handle GET/POST requests|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
<?php include_once 'config/Config.php'; include_once 'class/Rack.php'; $conn = new Config(); $db = $conn->getConnection(); $rack = new Rack($db); if(!empty($_POST['action']) && $_POST['action'] == 'listRack') { $rack->listRack(); } if(!empty($_POST['action']) && $_POST['action'] == 'getRackDetails') { $rack->rackid = $_POST["rackid"]; $rack->getRackDetails(); } if(!empty($_POST['action']) && $_POST['action'] == 'addRack') { $rack->name = $_POST["name"]; $rack->status = $_POST["status"]; $rack->insert(); } if(!empty($_POST['action']) && $_POST['action'] == 'updateRack') { $rack->rackid = $_POST["rackid"]; $rack->name = $_POST["name"]; $rack->status = $_POST["status"]; $rack->update(); } if(!empty($_POST['action']) && $_POST['action'] == 'deleteRack') { $rack->rackid = $_POST["rackid"]; $rack->delete(); } ?> |
iv- Create a JS file named
rack.js to handle Ajax requests, inside the js/ folder|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
jQuery(document).ready(function () { var rackRecords = jQuery('#rackListing').DataTable({ "lengthChange": false, "processing": true, "serverSide": true, "bFilter": false, 'serverMethod': 'post', "order": [], "ajax": { url: "rack_action.php", type: "POST", data: { action: 'listRack' }, dataType: "json" }, "columnDefs": [ { "targets": [0, 3, 4], "orderable": false, }, ], "pageLength": 10 }); jQuery('#addRack').click(function () { jQuery('#rackModal').modal({ backdrop: 'static', keyboard: false }); jQuery("#rackModal").on("shown.bs.modal", function () { jQuery('#rackForm')[0].reset(); jQuery('.modal-title').html("<i class='fa fa-plus'></i> Add Rack"); jQuery('#action').val('addRack'); jQuery('#save').val('Save'); }); }); jQuery("#rackListing").on('click', '.update', function () { var rackid = jQuery(this).attr("id"); var action = 'getRackDetails'; jQuery.ajax({ url: 'rack_action.php', method: "POST", data: { rackid: rackid, action: action }, dataType: "json", success: function (respData) { jQuery("#rackModal").on("shown.bs.modal", function () { jQuery('#rackForm')[0].reset(); respData.data.forEach(function (item) { jQuery('#rackid').val(item['rackid']); jQuery('#name').val(item['name']); jQuery('#status').val(item['status']); }); jQuery('.modal-title').html("<i class='fa fa-plus'></i> Edit Rack"); jQuery('#action').val('updateRack'); jQuery('#save').val('Save'); }).modal({ backdrop: 'static', keyboard: false }); } }); }); jQuery("#rackModal").on('submit', '#rackForm', function (event) { event.preventDefault(); jQuery('#save').attr('disabled', 'disabled'); var formData = jQuery(this).serialize(); jQuery.ajax({ url: "rack_action.php", method: "POST", data: formData, success: function (data) { jQuery('#rackForm')[0].reset(); jQuery('#rackModal').modal('hide'); jQuery('#save').attr('disabled', false); rackRecords.ajax.reload(); } }) }); jQuery("#rackListing").on('click', '.delete', function () { var rackid = jQuery(this).attr("id"); var action = "deleteRack"; if (confirm("Are you sure you want to delete this record?")) { jQuery.ajax({ url: "rack_action.php", method: "POST", data: { rackid: rackid, action: action }, success: function (data) { rackRecords.ajax.reload(); } }) } else { return false; } }); }); |
Create files named (header.php and footer.php)
This file contains the header and footer section of the webpage. The Bootstrap library is used to provide a better UI, so, include it in the header and footer section.
header.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<!doctype html> <html lang="en"> <head> <link rel="canonical" href="https://techarise.com/" /> <meta name="author" content="TechArise"> <meta name="description" content="Learn Web Development Tutorials & Web Developer Resources, PHP, MySQL, jQuery, CSS, XHTML, jQuery UI, CSS3, HTML5, HTML, web design, webdesign, with TechArise tutorials. View live demo"> <meta name="keywords" content="TechArise, tutorial TechArise, tutorials, freebies, resources, web development, webdev, demo, PHP, MySQL, jQuery, CSS, XHTML, jQuery UI, CSS3, HTML5, HTML, web design, webdesign, php script, dynamic web content" /> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <link rel="icon" type="image/ico" href="https://techarise.com/wp-content/themes/v1/favicon.ico"> <!-- Bootstrap CSS --> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css"> <link href="http://maxcdn.bootstrapcdn.com/font-awesome/4.1.0/css/font-awesome.min.css" rel="stylesheet"> <link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.2.9/css/responsive.bootstrap4.min.css" /> <link rel="stylesheet" href="https://cdn.datatables.net/1.11.3/css/dataTables.bootstrap4.min.css" /> <!--- Custom css ---> <link rel="stylesheet" href="css/style.css" /> <title>Library Management System | Tech Arise</title> </head> <body> <?php include('menus.php'); ?> |
menus.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<header style="margin-bottom:65px;"> <!-- Fixed navbar --> <nav class="navbar navbar-expand-md navbar-dark fixed-top bg-dark" style="background: #273E4A!important;"> <a class="navbar-brand" href="https://techarise.com"><strong>Tech Arise</strong></a> <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarCollapse" aria-controls="navbarCollapse" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="navbarCollapse"> <ul class="navbar-nav mr-auto"> <li class="nav-item active"> <a class="nav-link" href="https://techarise.com">Home <span class="sr-only">(current)</span></a> </li> </ul> <?php if (!empty($_SESSION) && $_SESSION["userid"]) { ?> <span class="nav-item dropdown"> <a style="color:#fff;" class="nav-link" href="logout.php" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false"> <i class="fa fa-user"></i> <?php echo ucfirst($_SESSION["name"]); ?> </a> </span> <?php } ?> </div> </nav> </header> |
footer.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<footer class="footer"> <div class="container"> </div> </footer> <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js"></script> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"></script> <script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script> <script src="https://cdn.datatables.net/1.11.3/js/dataTables.bootstrap4.min.js"></script> <script src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.min.js"></script> <script src="https://cdn.datatables.net/responsive/2.2.9/js/responsive.bootstrap4.min.js"></script> <script src="js/books.js"></script> <script src="js/category.js"></script> <script src="js/author.js"></script> <script src="js/publisher.js"></script> <script src="js/rack.js"></script> <script src="js/issue_books.js"></script> <script src="js/user.js"></script> </body> </html> |