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