Core Boxx – Database Library Reference

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

Leave a Comment

Your email address will not be published.