TABLE OF CONTENTS
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 withPDO::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]
);