DbHandler.class.php (9632B)
1 <?php 2 ############################### 3 # include files from root dir # 4 ############################### 5 $root_1 = realpath($_SERVER["DOCUMENT_ROOT"]); 6 $currentdir = getcwd(); 7 $root_2 = str_replace($root_1, '', $currentdir); 8 $root_3 = explode("/", $root_2); 9 if ($root_3[1] == 'core') { 10 echo $root_3[1]; 11 $root = realpath($_SERVER["DOCUMENT_ROOT"]); 12 }else{ 13 $root = $root_1 . '/' . $root_3[1]; 14 } 15 /** 16 * Class to handle all db operations 17 * This class will have CRUD methods for database tables 18 * 19 * @author Ravi Tamada 20 */ 21 class DbHandler { 22 23 private $conn; 24 25 function __construct() { 26 global $root; 27 require_once $root . '/core/api/v1/DbConnect.class.php'; 28 // opening db connection 29 $db = new DbConnect(); 30 $this->conn = $db->connect(); 31 } 32 33 /* ------------- `users` table method ------------------ */ 34 35 /** 36 * Creating new user 37 * @param String $name User full name 38 * @param String $email User login email id 39 * @param String $password User login password 40 */ 41 public function createUser($name, $email, $password) { 42 require_once 'PassHash.class.php'; 43 $response = array(); 44 45 // First check if user already existed in db 46 if (!$this->isUserExists($email)) { 47 // Generating password hash 48 $password_hash = PassHash::hash($password); 49 50 // Generating API key 51 $api_key = $this->generateApiKey(); 52 53 // insert query 54 $stmt = $this->conn->prepare("INSERT INTO users(name, email, password_hash, api_key, status) values(?, ?, ?, ?, 1)"); 55 $stmt->bind_param("ssss", $name, $email, $password_hash, $api_key); 56 57 $result = $stmt->execute(); 58 59 $stmt->close(); 60 61 // Check for successful insertion 62 if ($result) { 63 // User successfully inserted 64 return USER_CREATED_SUCCESSFULLY; 65 } else { 66 // Failed to create user 67 return USER_CREATE_FAILED; 68 } 69 } else { 70 // User with same email already existed in the db 71 return USER_ALREADY_EXISTED; 72 } 73 74 return $response; 75 } 76 77 /** 78 * Checking user login 79 * @param String $email User login email id 80 * @param String $password User login password 81 * @return boolean User login status success/fail 82 */ 83 public function checkLogin($email, $password) { 84 // fetching user by email 85 $stmt = $this->conn->prepare("SELECT password_hash FROM users WHERE email = ?"); 86 87 $stmt->bind_param("s", $email); 88 89 $stmt->execute(); 90 91 $stmt->bind_result($password_hash); 92 93 $stmt->store_result(); 94 95 if ($stmt->num_rows > 0) { 96 // Found user with the email 97 // Now verify the password 98 99 $stmt->fetch(); 100 101 $stmt->close(); 102 103 if (PassHash::check_password($password_hash, $password)) { 104 // User password is correct 105 return TRUE; 106 } else { 107 // user password is incorrect 108 return FALSE; 109 } 110 } else { 111 $stmt->close(); 112 113 // user not existed with the email 114 return FALSE; 115 } 116 } 117 118 /** 119 * Checking for duplicate user by email address 120 * @param String $email email to check in db 121 * @return boolean 122 */ 123 private function isUserExists($email) { 124 $stmt = $this->conn->prepare("SELECT id from users WHERE email = ?"); 125 if ($stmt === FALSE) { 126 die($this->conn->error); 127 } 128 $stmt->bind_param("s", $email); 129 $stmt->execute(); 130 $stmt->store_result(); 131 $num_rows = $stmt->num_rows; 132 $stmt->close(); 133 return $num_rows > 0; 134 } 135 136 /** 137 * Fetching user by email 138 * @param String $email User email id 139 */ 140 public function getUserByEmail($email) { 141 $stmt = $this->conn->prepare("SELECT name, email, api_key, status, created_at FROM users WHERE email = ?"); 142 $stmt->bind_param("s", $email); 143 if ($stmt->execute()) { 144 $user = $stmt->get_result()->fetch_assoc(); 145 $stmt->close(); 146 return $user; 147 } else { 148 return NULL; 149 } 150 } 151 152 /** 153 * Fetching user api key 154 * @param String $user_id user id primary key in user table 155 */ 156 public function getApiKeyById($user_id) { 157 $stmt = $this->conn->prepare("SELECT api_key FROM users WHERE id = ?"); 158 $stmt->bind_param("i", $user_id); 159 if ($stmt->execute()) { 160 $api_key = $stmt->get_result()->fetch_assoc(); 161 $stmt->close(); 162 return $api_key; 163 } else { 164 return NULL; 165 } 166 } 167 168 /** 169 * Fetching user id by api key 170 * @param String $api_key user api key 171 */ 172 public function getUserId($api_key) { 173 $stmt = $this->conn->prepare("SELECT id FROM users WHERE api_key = ?"); 174 $stmt->bind_param("s", $api_key); 175 if ($stmt->execute()) { 176 $user_id = $stmt->get_result()->fetch_assoc(); 177 $stmt->close(); 178 return $user_id; 179 } else { 180 return NULL; 181 } 182 } 183 184 /** 185 * Validating user api key 186 * If the api key is there in db, it is a valid key 187 * @param String $api_key user api key 188 * @return boolean 189 */ 190 public function isValidApiKey($api_key) { 191 $stmt = $this->conn->prepare("SELECT id from users WHERE api_key = ?"); 192 $stmt->bind_param("s", $api_key); 193 $stmt->execute(); 194 $stmt->store_result(); 195 $num_rows = $stmt->num_rows; 196 $stmt->close(); 197 return $num_rows > 0; 198 } 199 200 /** 201 * Generating random Unique MD5 String for user Api key 202 */ 203 private function generateApiKey() { 204 return md5(uniqid(rand(), true)); 205 } 206 207 /* ------------- `posts` table method ------------------ */ 208 209 /** 210 * Creating new task 211 * @param String $user_id user id to whom task belongs to 212 * @param String $task task text 213 */ 214 public function createPost($id, $text, $title, $author, $category) { 215 $stmt = $this->conn->prepare("INSERT INTO posts (text,title,author,date,category) VALUES (?, ?, ?, NOW(), ?)"); 216 $stmt->bind_param("ssss", $text, $title, $author, $category); 217 $result = $stmt->execute(); 218 $stmt->close(); 219 220 if ($result) { 221 // task row created 222 // now assign the task to user 223 $new_post_id = $this->conn->insert_id; 224 // task created successfully 225 return $new_post_id; 226 } else { 227 // task failed to create 228 return NULL; 229 } 230 } 231 232 /** 233 * Fetching single task 234 * @param String $task_id id of the task 235 */ 236 public function getPosts($post_id) { 237 $id_sql = $this->conn->prepare("SELECT COUNT(*) FROM posts"); 238 if ($id_sql->execute()) { 239 $post_id_clean_array = $id_sql->get_result()->fetch_assoc(); 240 $post_id_clean = $post_id_clean_array["COUNT(*)"]; 241 $post["post_id_clean"] = $post_id_clean; 242 } 243 if ($post_id == NULL) { 244 $x = 1; 245 $id = 1; 246 while ($x < $post_id_clean+1){ 247 $stmt = $this->conn->prepare("SELECT id,title,text,author,category,date FROM posts WHERE id = ?"); 248 $stmt->bind_param("i", $id); 249 250 if ($stmt->execute()) { 251 $post["$x"] = $stmt->get_result()->fetch_assoc(); 252 253 if ($post["$x"]["text"] != NULL){ 254 $post["$x"]["text"] = html_entity_decode($post["$x"]["text"]); 255 $post["$x"]["text"] = strip_tags($post["$x"]["text"]); 256 }else{ 257 $post["$x"]["text"] = NULL; 258 } 259 $stmt->close(); 260 261 } 262 $x = $x+1; 263 $id = $id+1; 264 } 265 if ($id == $post_id_clean+1){ 266 return $post; 267 }else{ 268 return NULL; 269 } 270 271 }else{ 272 $stmt = $this->conn->prepare("SELECT id,title,text,author,category,date FROM posts WHERE id = ?"); 273 $stmt->bind_param("i", $post_id); 274 if ($stmt->execute()) { 275 $post = $stmt->get_result()->fetch_assoc(); 276 if ($post["text"] != NULL){ 277 $post["text"] = html_entity_decode($post["text"]); 278 }else{ 279 $post = NULL; 280 } 281 $stmt->close(); 282 return $post; 283 } else { 284 return NULL; 285 } 286 } 287 288 } 289 290 /** 291 * Updating task 292 * @param String $task_id id of the task 293 * @param String $task task text 294 * @param String $status task status 295 */ 296 public function updatePost($user_id, $task_id, $task, $status) { 297 $stmt = $this->conn->prepare("UPDATE tasks t, user_tasks ut set t.task = ?, t.status = ? WHERE t.id = ? AND t.id = ut.task_id AND ut.user_id = ?"); 298 $stmt->bind_param("siii", $task, $status, $task_id, $user_id); 299 $stmt->execute(); 300 $num_affected_rows = $stmt->affected_rows; 301 $stmt->close(); 302 return $num_affected_rows > 0; 303 } 304 305 /** 306 * Deleting a task 307 * @param String $task_id id of the task to delete 308 */ 309 public function deletePost($user_id, $task_id) { 310 $stmt = $this->conn->prepare("DELETE t FROM tasks t, user_tasks ut WHERE t.id = ? AND ut.task_id = t.id AND ut.user_id = ?"); 311 $stmt->bind_param("ii", $task_id, $user_id); 312 $stmt->execute(); 313 $num_affected_rows = $stmt->affected_rows; 314 $stmt->close(); 315 return $num_affected_rows > 0; 316 } 317 318 319 } 320 321 ?>