DatabaseObject.php (4588B) download
1<?php
2
3namespace Lollipop {
4 require_once "SQLDatabase.php";
5
6 abstract class DatabaseObject
7 {
8 protected string $table;
9 protected string $primary;
10
11 protected SQLDatabase $db;
12 protected array $data = [];
13 protected array $changed_keys = [];
14
15 function __construct(SQLDatabase $db)
16 {
17 $this->db = $db;
18 $this->primary = $this->get_primary();
19 $this->table = $this->get_table();
20 $this->notNullable();
21 }
22
23 abstract static function get_primary(): string;
24 abstract static function get_table(): string;
25
26 public function setData($data)
27 {
28 $this->data = $data;
29 }
30 public function where(string $key, string $value)
31 {
32 $sql = "SELECT * FROM {$this->table} WHERE $key = ?";
33 $value = array($value);
34 $stmt = $this->db->conn->prepare($sql);
35 $stmt->execute($value);
36 $result = $stmt->get_result();
37 if ($result->num_rows == 0) {
38 return false;
39 }
40 $this->data = $result->fetch_assoc();
41 return true;
42 }
43
44 public function where_array(array $values) : bool
45 {
46 $sql = "SELECT * FROM {$this->table} WHERE ";
47 $params = [];
48 $i = 0;
49 foreach($values as $key => $param){
50 if($i > 0)
51 $sql .= " and ";
52 $sql .= "{$key} = ?";
53 $params[] = $param;
54 }
55
56 $stmt = $this->db->conn->prepare($sql);
57 $stmt->execute($params);
58 $result = $stmt->get_result();
59
60 if ($result->num_rows == 0) {
61 return false;
62 }
63
64 $this->data = $result->fetch_assoc();
65 return true;
66 }
67 public function load(string $id): bool
68 {
69 /*this fuction accepts an $id value for the primary key
70 * loads the row into data[]
71 * returns bool if row is found
72 */
73 $sql = "SELECT * FROM {$this->table} WHERE {$this->primary} = ?";
74
75 $stmt = $this->db->conn->prepare($sql);
76 $stmt->execute([$id]);
77 $result = $stmt->get_result();
78
79 if ($result->num_rows == 0) {
80 return false;
81 }
82
83 $this->data = $result->fetch_assoc();
84 return true;
85 }
86
87 public function save() : bool
88 {
89 if (!$this->changed_keys)
90 return false;
91
92 $sql = "UPDATE {$this->table} SET ";
93
94 $values = [];
95 foreach ($this->changed_keys as $index => $key) {
96 if ($index > 0)
97 $sql .= ', ';
98 $sql .= "$key = ?";
99 $values[] = $this->data[$key];
100 }
101
102 $sql .= " WHERE {$this->primary} = ?";
103 $values[] = $this->data[$this->primary];
104
105 $stmt = $this->db->conn->prepare($sql);
106
107 $this->changed_keys = [];
108
109 if($stmt->execute($values))
110 return true;
111 else
112 return false;
113 }
114
115 public function add() : bool
116 /* this function add the set variables to the database */
117 {
118 if (!$this->changed_keys)
119 return false;
120
121 $sql = "INSERT INTO {$this->table} (";
122 $sql_val = ") VALUES (";
123 $values = [];
124
125 foreach ($this->changed_keys as $index => $key) {
126 if ($index > 0){
127 $sql .= ', ';
128 $sql_val .= ', ';
129 }
130 $sql .= $key;
131 $sql_val .= "?";
132 $values[] = $this->data[$key];
133 }
134
135 $sql .= $sql_val . ")";
136 $stmt = $this->db->conn->prepare($sql);
137
138 $this->changed_keys = [];
139
140 if($stmt->execute($values))
141 return true;
142 else
143 return false;
144 }
145 public function delete()
146 {
147 $sql = "DELETE FROM {$this->table} WHERE {$this->primary} = ?";
148 $stmt = $this->db->conn->prepare($sql);
149 $stmt->execute([$this->data[$this->primary]]);
150 $this->data = [];
151 $this->changed_keys = [];
152 }
153
154 public function __get(string $name)
155 {
156 return $this->data[$name];
157 }
158
159 public function __set(string $name, $value)
160 {
161 $this->data[$name] = $value;
162 $this->changed_keys[] = $name;
163 }
164
165 public function getData()
166 {
167 return $this->data;
168 }
169 private function notNullable(){
170 //non-auto-increment not-nullable collumn names query
171 $not_null = [];
172 $col_names = [];
173 $sql = " SELECT column_name, is_nullable, extra
174 FROM INFORMATION_SCHEMA.COLUMNS
175 WHERE TABLE_NAME = '{$this->table}'
176 AND TABLE_SCHEMA = 'panda'";
177 $stmt = $this->db->conn->prepare($sql);
178 $stmt->execute();
179 $result = $stmt->get_result();
180
181 if ($result->num_rows == 0) {
182 return false;
183 }
184 while($tmp = $result->fetch_assoc()){
185 if($tmp["is_nullable"] == 'NO'){
186 if($tmp["extra"] == "auto_increment")
187 continue;
188 $not_null[] = $tmp["column_name"];
189 }
190 $col_names[] = $tmp["column_name"];
191 }
192 $this->data["not_nullable"] = $not_null;
193 $this->data["column_names"] = $col_names;
194 return true;
195 }
196 }
197}