DatabaseObject.php (10232B)
1 <?php 2 3 namespace Lollipop { 4 require_once "SQLDatabase.php"; 5 6 /// Lollipop\DatabaseObject is an abstract class, a TableClass like Model\User should extends this 7 abstract class DatabaseObject 8 { 9 protected string $table; 10 protected string $primary; 11 protected array $column_names; 12 protected array $not_nullable; 13 protected SQLDatabase $db; 14 protected array $data = []; 15 protected array $changed_keys = []; 16 private string $schema; 17 18 public function __construct(SQLDatabase $db) 19 { 20 $this->db = $db; 21 $this->primary = $this->get_primary(); 22 $this->table = $this->get_table(); 23 $this->schema = $this->get_schema(); 24 } 25 26 abstract public static function get_primary(): string; 27 abstract public static function get_table(): string; 28 abstract public static function get_schema(): string; 29 30 /// setData is to bulk-set the row instead of one-for-one 31 public function setData($data) 32 { 33 $this->data = $data; 34 } 35 36 /// select row by key 37 public function where(string $key, string $value) 38 { 39 $sql = "SELECT * FROM {$this->table} WHERE $key = ?"; 40 $value = array($value); 41 $stmt = $this->db->conn->prepare($sql); 42 $stmt->execute($value); 43 $result = $stmt->get_result(); 44 if ($result->num_rows == 0) { 45 return false; 46 } 47 $this->data = $result->fetch_assoc(); 48 return true; 49 } 50 51 /// select rows by multiple values 52 public function where_array(array $values): bool 53 { 54 $sql = "SELECT * FROM {$this->table} WHERE "; 55 $params = []; 56 $i = 0; 57 foreach($values as $key => $param) { 58 if($i > 0) { 59 $sql .= " and "; 60 } 61 $sql .= "{$key} = ?"; 62 $params[] = $param; 63 } 64 65 $stmt = $this->db->conn->prepare($sql); 66 $stmt->execute($params); 67 $result = $stmt->get_result(); 68 69 if ($result->num_rows == 0) { 70 return false; 71 } 72 73 $this->data = $result->fetch_assoc(); 74 return true; 75 } 76 77 /// select row by id (and key is $this->primary_key()) 78 public function load(string $id): bool 79 { 80 /*this fuction accepts an $id value for the primary key 81 * loads the row into data[] 82 * returns bool if row is found 83 */ 84 $sql = "SELECT * FROM {$this->table} WHERE {$this->primary} = ?"; 85 86 $stmt = $this->db->conn->prepare($sql); 87 $stmt->execute([$id]); 88 $result = $stmt->get_result(); 89 90 if ($result->num_rows == 0) { 91 return false; 92 } 93 94 $this->data = $result->fetch_assoc(); 95 return true; 96 } 97 98 /// update the row 99 public function save(): bool 100 { 101 if (!$this->changed_keys) { 102 return false; 103 } 104 105 $sql = "UPDATE {$this->table} SET "; 106 107 $values = []; 108 foreach ($this->changed_keys as $index => $key) { 109 if ($index > 0) { 110 $sql .= ', '; 111 } 112 $sql .= "$key = ?"; 113 $values[] = $this->data[$key]; 114 } 115 116 $sql .= " WHERE {$this->primary} = ?"; 117 $values[] = $this->data[$this->primary]; 118 119 $stmt = $this->db->conn->prepare($sql); 120 121 $this->changed_keys = []; 122 123 if($stmt->execute($values)) { 124 return true; 125 } else { 126 return false; 127 } 128 } 129 130 /// insert row into database if not existent 131 public function add(): bool 132 /* this function add the set variables to the database */ 133 { 134 if (!$this->changed_keys) { 135 return false; 136 } 137 138 $sql = "INSERT INTO {$this->table} ("; 139 $sql_val = ") VALUES ("; 140 $values = []; 141 142 foreach ($this->changed_keys as $index => $key) { 143 if ($index > 0) { 144 $sql .= ', '; 145 $sql_val .= ', '; 146 } 147 $sql .= $key; 148 $sql_val .= "?"; 149 $values[] = $this->data[$key]; 150 } 151 152 $sql .= $sql_val . ")"; 153 $stmt = $this->db->conn->prepare($sql); 154 155 $this->changed_keys = []; 156 157 if($stmt->execute($values)) { 158 return true; 159 } else { 160 return false; 161 } 162 } 163 164 /// deletes the row 165 public function delete() 166 { 167 $sql = "DELETE FROM {$this->table} WHERE {$this->primary} = ?"; 168 $stmt = $this->db->conn->prepare($sql); 169 $stmt->execute([$this->data[$this->primary]]); 170 $this->data = []; 171 $this->changed_keys = []; 172 } 173 174 /// magic method: echo $obj->column 175 public function __get(string $name) 176 { 177 return $this->data[$name]; 178 } 179 180 /// magic method: $obj->column = "value" 181 public function __set(string $name, $value) 182 { 183 $this->data[$name] = $value; 184 $this->changed_keys[] = $name; 185 } 186 187 /// get row as array 188 public function getData() 189 { 190 return $this->data; 191 } 192 193 /// get not-nullable fields of this table 194 public function notNullable() 195 { 196 //non-auto-increment not-nullable collumn names query 197 $col_names = []; 198 $sql = " SELECT column_name, is_nullable, extra 199 FROM INFORMATION_SCHEMA.COLUMNS 200 WHERE TABLE_NAME = '{$this->table}' 201 AND TABLE_SCHEMA = '{$this->schema}'"; 202 $stmt = $this->db->conn->prepare($sql); 203 $stmt->execute(); 204 $result = $stmt->get_result(); 205 206 if ($result->num_rows == 0) { 207 return []; 208 } 209 while($tmp = $result->fetch_assoc()) { 210 if($tmp["is_nullable"] == 'NO') { 211 if(!$tmp["extra"] == "auto_increment") { 212 $col_names[] = $tmp["column_name"]; 213 } 214 } 215 } 216 return $col_names; 217 } 218 219 /// get column names of table 220 public function get_column_names(): array 221 { 222 $column_names = []; 223 $sql = " SELECT column_name 224 FROM INFORMATION_SCHEMA.COLUMNS 225 WHERE TABLE_NAME = '$this->table' 226 AND TABLE_SCHEMA = '$this->schema'"; 227 $stmt = $this->db->conn->prepare($sql); 228 $stmt->execute(); 229 $result = $stmt->get_result(); 230 231 if ($result->num_rows == 0) { 232 return []; 233 } 234 while($tmp = $result->fetch_assoc()) { 235 $column_names[] = $tmp["column_name"]; 236 } 237 return $column_names; 238 } 239 240 /// get column names without auto-increments 241 public function get_col_names_no_ai(): array 242 { 243 $column_names = []; 244 $sql = " SELECT column_name, extra 245 FROM INFORMATION_SCHEMA.COLUMNS 246 WHERE TABLE_NAME = '$this->table' 247 AND TABLE_SCHEMA = '$this->schema' 248 AND EXTRA not like '%auto_increment%'"; 249 $stmt = $this->db->conn->prepare($sql); 250 $stmt->execute(); 251 $result = $stmt->get_result(); 252 253 if ($result->num_rows == 0) { 254 return []; 255 } 256 while($tmp = $result->fetch_assoc()) { 257 $column_names[] = $tmp["column_name"]; 258 } 259 return $column_names; 260 } 261 262 /// get auto-incremented columns 263 public function get_col_names_ai(): array 264 { 265 $column_names = []; 266 $sql = " SELECT column_name, extra 267 FROM INFORMATION_SCHEMA.COLUMNS 268 WHERE TABLE_NAME = '$this->table' 269 AND TABLE_SCHEMA = '$this->schema' 270 AND EXTRA like '%auto_increment%'"; 271 $stmt = $this->db->conn->prepare($sql); 272 $stmt->execute(); 273 $result = $stmt->get_result(); 274 275 if ($result->num_rows == 0) { 276 return []; 277 } 278 while($tmp = $result->fetch_assoc()) { 279 $column_names[] = $tmp["column_name"]; 280 } 281 return $column_names; 282 } 283 284 /// get column infos 285 public function get_col_info(): array 286 { 287 $column_names = []; 288 $sql = " SELECT column_name, extra, data_type 289 FROM INFORMATION_SCHEMA.COLUMNS 290 WHERE TABLE_NAME = '$this->table' 291 AND TABLE_SCHEMA = '$this->schema'"; 292 $stmt = $this->db->conn->prepare($sql); 293 $stmt->execute(); 294 $result = $stmt->get_result(); 295 296 if ($result->num_rows == 0) { 297 return []; 298 } 299 while($tmp = $result->fetch_assoc()) { 300 if(str_contains($tmp['data_type'], "varchar") || str_contains($tmp['data_type'], "text")) { 301 $column_names[$tmp["column_name"]]["input_type"] = "text"; 302 } elseif(str_contains($tmp['data_type'], "date")) { 303 $column_names[$tmp["column_name"]]["input_type"] = "date"; 304 } elseif(str_contains($tmp['data_type'], "int")) { 305 $column_names[$tmp["column_name"]]["input_type"] = "number"; 306 } elseif(str_contains($tmp['data_type'], "double")) { 307 $column_names[$tmp["column_name"]]["input_type"] = "number"; 308 } 309 if(str_contains($tmp['extra'], "auto_increment")) { 310 $column_names[$tmp["column_name"]]['extra'] = "auto_increment"; 311 } 312 if(str_contains($tmp['column_name'], "password")) { 313 $column_names[$tmp["column_name"]]['extra'] = "password"; 314 } 315 } 316 return $column_names; 317 } 318 } 319 }