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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
|
<?php
/**
* The model that has the database functionality. DO NOT instantiate this class! If you need an instance, extend the DBModel class!
*/
class DB {
private static $servername = 'localhost';
private static $username = 'root';
private static $password = 'password';
private static $dbname = 'devrix_job_board';
public const STRING_TYPE = 'VARCHAR(100)';
public const FLOAT_TYPE = 'DOUBLE(16,4)';
public const INTEGER_TYPE = 'INT';
private PDO $connection;
function __construct() {
// Try to connect
try {
$this->connection = new PDO('mysql:host=' . self::$servername, self::$username, self::$password);
$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
echo '[DB Exception]: ' . $e->getMessage();
}
// Make sure the actual db exists
$createDBQuery = 'CREATE DATABASE IF NOT EXISTS ' . self::$dbname . ';';
$this->connection->exec($createDBQuery);
// Connect to the actual database
$this->connection->exec('USE ' . self::$dbname);
}
/**
* Generates a foreign key string from the given parameters.
*
* @return string
*/
static function genFK(string $currentColumn, string $foreignTable, string $foreignColumn) {
return "CONSTRAINT fk_{$currentColumn}_{$foreignTable} FOREIGN KEY ({$currentColumn}) REFERENCES {$foreignTable}($foreignColumn)";
}
/* Create */
/**
* Creates a table, with the given name and given column information.
* **WARNING**: The method also creates an auto-incremented int id column, **DO NOT** specify it!
*
* @param string[] $columns
* Represents the column information. Each value must be like: "column_name data_type", where the data_type should be taken
* from the constant values of DB class.
* Example value: array("first_name " . DB::STRING_TYPE, "age " . DB::INTEGER_TYPE, "income " . DB::FLOAT_TYPE)
*
* @param string[] $foreignKeys - OPTIONAL
* Represents the foreign keys. All foreign keys **MUST** be generated the the static DB method genFK.
* Example value: array(DB::genFK("jobId", "jobs", "id"), DB::genFK("cityId", "cities", "id"))
*/
function createTable(string $tableName, array $columns, array $foreignKeys = null) {
$query = "CREATE TABLE IF NOT EXISTS {$tableName}(
id INT NOT NULL AUTO_INCREMENT, ";
// Create column parts of query string
foreach ($columns as $column) {
$query .= "{$column}, ";
}
// Create key(s) parts of query string
$query .= 'PRIMARY KEY (id), ';
foreach ($foreignKeys as $fk) {
$query .= "{$fk}, ";
}
$query = rtrim($query, ', ');
$query .= ');';
try {
$this->connection->exec($query);
}
catch (PDOException $e) {
echo '[DB Exception]: ' . $e->getMessage();
}
}
/**
* Inserts a value in a given table.
*
* @param string[] $values
* Represents the data that should be inserted. The values should be ordered the same way the table columns are ordered.
* Example: "John", 20, 2000.3
* **WARNING**: DO NOT insert the id value
*/
function insertValue(string $tableName, ...$values) {
$query = "INSERT INTO {$tableName} VALUES(NULL, ";
// Create the values part of the query string
foreach($values as $value) {
// In queries, strings must be surrounded in single quotes
if (gettype($value) == 'string')
$query .= "'{$value}', ";
else
$query .= "{$value}, ";
}
$query = rtrim($query, ', ');
$query .= ');';
try {
$this->connection->exec($query);
}
catch (PDOException $e) {
echo '[DB Exception]: ' . $e->getMessage();
}
}
/* Read */
/**
* Returns the next value that has the given id.
*/
function getById(string $tableName, int $id) {
$sth = $this->connection->prepare("SELECT * FROM {$tableName} WHERE id = {$id}");
$sth->execute();
return $sth->fetch(PDO::FETCH_OBJ);
}
/**
* Returns all values inside a table.
*/
function getAllValues(string $tableName) {
$sth = $this->connection->prepare("SELECT * FROM {$tableName}");
$sth->execute();
return $sth->fetchAll(PDO::FETCH_OBJ);
}
/* Update */
/**
* Updates the value with the given id inside the specified table.
*
* @param string[] $values
* Represents the data that should be updated. There should always be an even amount of data, where you alternate the column name and column value.
* Example: "first_name", "John", "age", 20, "income", 2000.3
*/
function updateById(string $tableName, int $id, ...$values) {
$query = "UPDATE {$tableName} SET ";
// Create the name = value part of query string
for ($i = 0; $i < sizeof($values); $i += 2) {
$query .= "{$values[$i]} = ";
// In queries, strings must be surrounded in single quotes
if (gettype($values[$i+1]) == 'string')
$query .= "'{$values[$i+1]}'";
else
$query .= $values[$i+1];
$query .= ', ';
}
$query = rtrim($query, ', ');
$query .= " WHERE id = {$id};";
try {
$this->connection->exec($query);
}
catch (PDOException $e) {
echo '[DB Exception]: ' . $e->getMessage();
}
}
/* Delete */
/**
* Deletes the value with the given id inside the given table.
*/
function deleteById(string $tableName, int $id) {
try {
$this->connection->exec("DELETE FROM {$tableName} WHERE id = {$id}");
}
catch (PDOException $e) {
echo '[DB Exception]: ' . $e->getMessage();
}
}
}
|
