-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCode.php
96 lines (94 loc) · 3.17 KB
/
Code.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
<?php
class BulkUpdateController
{
/**
* prepareUpdate
*
* All rows must have a fixed amount of columns and all of them must have a value.
*
* They must have a column called "id" (you can edit it to your needs).
*
* For MS SQL and MySQL.
* @param array $array: e.g. [[columnname => value, columnname2 => value2], [columnname => value, columnname2 => value2]]
* @param string $table: name of table to update
* @return string: SQL string to perform the Bulk Update
*/
public function prepareUpdate(array $array, string $table): string
{
$columnname = null;
$columnNumber = count($array[0]);
$sqld = 'UPDATE ' . $table . ' SET ';
$amount = count($array) * $columnNumber * $columnNumber;
$counter = 0;
$index = 0;
$columns = [];
$idsarray = [];
while ($amount !== $counter) {
foreach ($array as $value) {
$id = 0;
$columns = array_keys($value);
foreach ($value as $key => $nested) {
if ($key === 'id') {
$id = $nested;
if (count($idsarray) !== count($array)) {
array_push($idsarray, $nested);
}
} else {
if (is_null($columnname)) {
$columnname = $columns[$index];
if ($columnname !== 'id') {
$sqld .= $columnname;
$sqld .= ' = CASE';
}
}
if ($columnname === $key) {
$sqld .= ' WHEN id = ' . $id . ' THEN ' . $this->checkValue($nested) . ' ';
}
}
$counter++;
}
}
if ($columnname !== 'id') {
if ($index === $columnNumber - 1) {
$sqld .= 'ELSE ' . $columns[$index] . ' END ';
$index++;
} else {
$sqld .= 'ELSE ' . $columns[$index] . ' END, ';
$index++;
$columnname = null;
}
} else {
$columnname = null;
$index++;
}
}
$sqld .= 'WHERE id IN (';
foreach ($idsarray as $key => $value) {
if ($key === count($idsarray) - 1) {
$sqld .= $value . '';
} else {
$sqld .= $value . ', ';
}
}
$sqld .= ');';
return ($sqld);
}
// Function to show some values the SQL needs explicitly e.g null, true, false and apostrophe (for strings)
private function checkValue($value): string
{
if (is_string($value)) {
return '\'' . $value . '\'';
}
if (is_bool($value)) {
if ($value) {
return 'true';
} else {
return 'false';
}
}
if (is_null($value)) {
return 'null';
}
return $value;
}
}