NOTES
- Database settings are kept in
lib/Core-Config.php
. - MYSQL PDO extension is required.
- You can always rewrite the entire database module to work with other databases – It’s PDO after all.
CONNECT & DISCONNECT
function __construct()
Connects to the database when the object is created.
function __destruct()
Closes the database connection when done.
AUTO COMMIT
function start()
Auto-commit off.
$this->DB->start();
$pass = $this->DB->update(SQL 1);
if ($pass) { $pass = $this->DB->update(SQL 2); }
$this->DB->end($pass);
return $pass;
function end($pass)
Used in conjunction with start()
.
$pass
– Boolean, commit or rollback?
QUERY
function query($sql, $data)
Runs an SQL query.
$sql
– String, SQL query to run.$data
– Array, data to feed into query.
$this->DB->query(
"DELETE FROM `users` WHERE `id`=?", [$id]
);
FETCH
function fetchAll($sql, $data, $key)
Fetch multiple rows of data.
$sql
– String, SQL query to run.$data
– Array, data to feed into query.$key
– String, use this column as the key of the array. Optional.
$users = $this->DB->fetchAll(
"SELECT * FROM `users` WHERE `age`<?",
[30], "user_id"
);
function fetchKV($sql, $data, $key, $value)
Fetch multiple rows of data, sorted into KEY => VALUE
.
$sql
– String, SQL query to run.$data
– Array, data to feed into query.$key
– String, use this column as the key of the array.$value
– String, use this column as the value of the array.
$users = $this->DB->fetchKV(
"SELECT * FROM `users`", null, "user_id", "user_email"
);
// $users = [ID => EMAIL, ID => EMAIL, ETC...]
function fetch($sql, $data)
Fetch a single row of data.
$sql
– String, SQL query to run.$data
– Array, data to feed into query.
$user = $this->DB->fetch(
"SELECT * FROM `users` WHERE `id`=?", [$id]
);
function fetchCol($sql, $data)
Fetch a single column of data.
$sql
– String, SQL query to run.$data
– Array, data to feed into query.
$email = $this->DB->fetchCol(
"SELECT `user_email` FROM `users` WHERE `id`=?", [$id]
);
INSERT UPDATE DELETE
function insert($table, $fields, $data, $replace)
Insert or replace data into the specified database table.
$table
– String, target table.$fields
– Array, name of fields.$data
– Array, data to feed into query.$replace
– Boolean, replace instead of insert?
$this->DB->insert("users",
["user_email", "user_password"]
["john@doe.com", "123", "jane@doe.com", "456"]
);
function update($table, $fields, $where, $data)
Run update query.
$table
– String, target table.$fields
– Array, name of fields.$where
– String, the “WHERE” clause.$data
– Array, data to feed into query.
$this->DB->update("table",
["user_email", "user_pass"],
"`user_id`=?",
["joy@doe.com", "PASSWORD", 1]
);
function delete($table, $where, $data)
Run delete query.
$table
– String, target table.$where
– String, the “WHERE” clause.$data
– Array, data to feed into query.
$this->DB->delete("content",
"`content_id`=?", [123]
);