Core Boxx – Database Module

Files Config Notes

Reference & Examples

FILES CONFIG NOTES

Files and config list for this module, and some notes (if any).

FILES LIST

Only one file – lib/LIB-DB.php

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.

DATABASE MODULE REFERENCES

Now for the list of library functions, and a few short examples.

PROPERTIES

$pdo PDO object.
$stmt Prepared SQL statement.
$lastID Last inserted ID.
$lastRows Number of rows affected.

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();
$this->DB->insert(SOMETHING);
$this->DB->update(SOMETHING);
$this->DB->delete(SOMTHING);
$this->DB->end();

function end ($pass)

Used in conjunction with start().

$pass – Boolean, commit or rollback? Default true.

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 – Null, string, or true.
- null Arrange results with PDO::ASSOC.
- string Use this column as the key of the array.
- true For single column select only, returns a flat array.

// NULL - [0 => [KEY=>VALUE], 1 => [KEY=>VALUE], ...]
$users = $this->DB->fetchAll("SELECT * FROM `users` WHERE `age`<?", [30]);

// STRING - [ID => [KEY=>VALUE], ID => [KEY=>VALUE], ...]
$users = $this->DB->fetchAll("SELECT * FROM `users` WHERE `age`<?", [30], "user_id");

// TRUE - [NAME, NAME, ...]
$users = $this->DB->fetchAll("SELECT `user_name` FROM `users` WHERE `age`<?", [30], true);

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.

// [ID => EMAIL, ID => EMAIL, ...]
$users = $this->DB->fetchKV(
  "SELECT * FROM `users`", null, "user_id", "user_email"
);

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 replace ($table, $fields, $data)

Same as “insert” above, but with replace = true.

$this->DB->replace("users", 
  ["user_email", "user_password"]
  ["john@doe.com", "123"]
);

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]
);