· 8 years ago · Jun 08, 2018, 07:30 PM
1<?php
2
3 class crud{
4
5 static private $conn;
6
7 static private $host = 'localhost';
8
9 static private $dbname = 'imobiliariamonaco';
10
11 static private $user = 'root';
12
13 static private $pass = '';
14
15 /**
16 * Construct, connection with database
17 *
18 * @param string $host
19 *
20 * @param string $dbname
21 *
22 * @param string $user
23 *
24 * @param string $pass
25 *
26 * @return void
27 *
28 * @access public
29 */
30 public function __construct(string $host = NULL, string $dbname = NULL, string $user = NULL, string $pass = NULL){
31
32 self::connection($host, $dbname, $user, $pass);
33
34 }
35
36
37 /*------------------------------------------------------------------------------*/
38
39
40 /**
41 * Connection database
42 *
43 * @access protected
44 */
45 protected function connection(string $host = NULL, string $dbname = NULL, string $user = NULL, string $pass = NULL){
46
47 try{
48
49 self::$conn = new PDO('mysql:host=' . (!$host ? self::$host : $host) . ';dbname=' . (!$dbname ? self::$dbname : $dbname), (!$user ? self::$user : $user), (!$pass ? self::$pass : $pass));
50
51 self::$conn->exec('SET CHARACTER SET utf8');
52
53 }catch(PDOExecption $e){
54
55 print '<h1>Erro ao conectar banco de dados</h1>';
56
57 }
58
59 }
60
61
62 /*------------------------------------------------------------------------------*/
63
64
65 /**
66 * Prepare to execute PDO
67 *
68 * @param string $text
69 *
70 * @param int $count
71 *
72 * @param string $separator
73 *
74 * @return bool
75 *
76 * @access private
77 */
78 private function placeholders(string $text, $count = 0, $separator = ','){
79
80 $result = array();
81
82 if($count > 0):
83
84 for($x=0; $x<$count; $x++):
85
86 $result[] = $text;
87
88 endfor;
89
90 endif;
91
92 return implode($separator, $result);
93
94 }
95
96
97 /*------------------------------------------------------------------------------*/
98
99
100 /**
101 * Execute custom sql
102 *
103 * @param string $sql
104 *
105 * @param string $prepare
106 *
107 * @param array $values
108 *
109 * @param string $type
110 *
111 * @return bool
112 *
113 * @access private
114 */
115 private function exec(string $sql, string $prepare = NULL, array $values = NULL, string $type = NULL){
116
117 $returns = false;
118
119 if($prepare):
120
121 $insert = self::$conn->prepare($sql);
122
123 endif;
124
125 try{
126
127 self::$conn->beginTransaction();
128
129 if($prepare):
130
131 if($values):
132
133 if(in_array(true, array_map('is_array', $values), true)):
134
135 sort($values);
136
137 $insertValues = array();
138
139 foreach($values as $d):
140
141 if(in_array(true, array_map('is_array', $d), true)):
142
143 foreach($d as $tri):
144
145 $insertValues = array_merge($insertValues, array_values($tri));
146
147 endforeach;
148
149 else:
150
151 $insertValues = array_merge($insertValues, array_values($d));
152
153 endif;
154
155 endforeach;
156
157 endif;
158
159 endif;
160
161 $exec = $insert->execute(($values ? (in_array(true, array_map('is_array', $values), true) ? $insertValues : array_values($values)) : array_values($values)));
162
163 $lastId = self::$conn->lastInsertId();
164
165 else:
166
167 $exec = self::$conn->query($sql);
168
169 endif;
170
171 if($exec):
172
173 self::$conn->commit();
174
175 $returns = ($type ? $lastId : true);
176
177 else:
178
179 self::$conn->rollback();
180
181 $returns = false;
182
183 endif;
184
185 }catch(PDOExecption $e){
186
187 self::$conn->rollback();
188
189 $returns = false;
190
191 }
192
193 return $returns;
194
195 }
196
197
198 /*------------------------------------------------------------------------------*/
199
200
201 /**
202 * Mount prepare PDO
203 *
204 * @param array $data
205 *
206 * @param bool $mult
207 *
208 * @return string
209 *
210 * @access private
211 */
212 private function mountPrepare($data, $mult = false){
213
214 $valuePrepare = '';
215
216 $valuePrepare .= ($mult ? '(' : '');
217
218 for($i = 0; $i < count($data); $i++):
219
220 $valuePrepare .= '?' . ($i != (count($data) - 1) || !$mult ? ',' : '');
221
222 endfor;
223
224 $valuePrepare .= ($mult ? '),' : '');
225
226 return $valuePrepare;
227
228 }
229
230
231 /*------------------------------------------------------------------------------*/
232
233
234 /**
235 * Create
236 *
237 * @param string $table
238 *
239 * @param array $data
240 *
241 * @param string $type
242 *
243 * @return bool or int depends on type
244 *
245 * @access public
246 */
247 public function create(string $table, array $data, string $type = NULL){
248
249 $chunk = (count($data) > 200 ? array_chunk($data, 2, true) : $data);
250
251 $i = 0; foreach($chunk as $teste):
252
253 $multidimensional = in_array(true, array_map('is_array', $teste), true);
254
255 $fields = implode(', ', array_keys(($multidimensional ? $teste[0] : $teste)));
256
257 $fieldsTri = array();
258
259 $valuePrepare = '';
260
261 if($multidimensional):
262
263 foreach($teste as $item):
264
265 $tridimensional = in_array(true, array_map('is_array', $item), true);
266
267 if($tridimensional):
268
269 foreach($item as $tri):
270
271 $fieldsTri[] = $tri;
272
273 $valuePrepare .= self::mountPrepare($tri, true);
274
275 endforeach;
276
277 else:
278
279 $valuePrepare .= self::mountPrepare($item, true);
280
281 endif;
282
283 endforeach;
284
285 else:
286
287 $valuePrepare .= self::mountPrepare($teste);
288
289 endif;
290
291 if(count($fieldsTri) >= 1):
292
293 $fields = implode(', ', array_keys($fieldsTri));
294
295 endif;
296
297 return self::exec('INSERT INTO ' . $table . ' (' . $fields . ') VALUES ' . (!$multidimensional ? '(' : '') . substr($valuePrepare,0,-1) . (!$multidimensional ? ')' : ''), 'create', $teste, $type);
298
299 $i++; endforeach;
300
301 }
302
303
304 /*------------------------------------------------------------------------------*/
305
306
307 /**
308 * Read
309 *
310 * @param string $table
311 *
312 * @param string $cond
313 *
314 * @param string $inner
315 *
316 * @return array
317 *
318 * @access public
319 */
320 public function read(string $table, string $cond = NULL, string $fields = NULL, string $inner = NULL){
321
322 $select = self::$conn->query('SELECT ' . (!$fields ? '*' : $fields) . ' FROM ' . $table . ($inner ? ' as imob' : '') . ' ' . $inner . ' ' . $cond);
323
324 for($y = 0; $y < $select->columnCount(); $y++):
325
326 $names[$y] = $select->getColumnMeta($y);
327
328 endfor;
329
330 $result = array();
331
332 for($x = 0; $row = $select->fetch(PDO::FETCH_ASSOC); $x++):
333
334 for($i = 0; $i < $select->columnCount(); $i++):
335
336 $result[$x][$names[$i]['name']] = $row[$names[$i]['name']];
337
338 endfor;
339
340 endfor;
341
342 return $result;
343
344 }
345
346
347 /*------------------------------------------------------------------------------*/
348
349
350 /**
351 * Update
352 *
353 * @param string $table
354 *
355 * @param array $data
356 *
357 * @param string $where
358 *
359 * @return bool
360 *
361 * @access public
362 */
363 public function update(string $table, array $data, string $where){
364
365 foreach($data as $fields => $values):
366
367 $sets[] = '$fields = ' . $values;
368
369 endforeach;
370
371 return self::exec('UPDATE ' . $table . ' SET ' . implode(', ', $sets) . ' WHERE ' . $where);
372
373 }
374
375
376 /*------------------------------------------------------------------------------*/
377
378
379 /**
380 * Delete
381 *
382 * @param string $table
383 *
384 * @param string $where
385 *
386 * @return bool
387 *
388 * @access public
389 */
390 public function delete(string $table, string $where){
391
392 return self::exec('DELETE FROM ' . $table . ' WHERE ' . $where);
393
394 }
395
396
397 /*------------------------------------------------------------------------------*/
398
399
400 /**
401 * Execute function of custom sql by type
402 *
403 * @param string $table
404 *
405 * @param string $type (drop, create_table)
406 *
407 * @param string $fields
408 *
409 * @return bool
410 *
411 * @access public
412 */
413 public function customSql(string $table, string $type, string $fields = NULL){
414
415 if($type == 'drop'):
416
417 $sql = 'DROP TABLE IF EXISTS ' . $table;
418
419 elseif($type == 'create_table'):
420
421 $sql = 'CREATE TABLE IF NOT EXISTS ' . $table . ' (' . $fields . ')';
422
423 endif;
424
425 return self::exec($sql, ($type == 'drop' ? 'prepare' : ''));
426
427 }
428
429 }
430
431?>