DatabaseObject.php (10232B) download
1<?php
2
3namespace 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}