README

Roadmap

Make chained join clauses more fluent, after on() add a then($callback) (instead of current behaviour of multiple seperate calls and usage of through($table)) ?
Add prefix(string $prefix) function to set a table prefix to automatically append.

Documentation

Requires PHP 8.0

Setup:

Autoload Koss using Composer.
Initiate a new Koss instance by passing your MySQL login and database information.
Parameters:
- 1: Hostname
- 2: Port
- 3: Database name
- 4: Username
- 5: Password

Note: Your order of functions does not matter. You can limit() and then choose to select more columns via columns() after.

Core Functions:

Functions which are available in both Selection and Update/Insert queries.

execute()
- Execute compiled Koss MySQL code and output results
- Note: Without calling this function at the end of your code, nothing will output!
when(Closure | bool $expression, Closure $callback)
- Only execute $callback function when $expression is true.
- Note: $expression can be either a boolean value (5 < 10) or an anonymous function which returns a boolean value
unless(Closure | bool $expression, Closure $callback)
- Only execute $callback function when $expression is false.
- Note: $expression can be either a boolean value or an anonymous function which returns a boolean value
where(string $column, string $operator, string $matches)
- Select rows in $table (must be previously provided via a select statement) with values in $column that are $operator to $match
- Note: If $operator is not provided, '=' will be assumed
- Example SQL code: WHERE username <> 'Aberdeener'
like(string $column, string $like)
- Select rows in $table (must be previously provided via a select statement) with values in $column that are similar to to $like.
- You must provide the % where you want them to be, Koss cannot assume anything.
- Note: Multiple like and where clauses can be passed and Koss will handle compiling the correct MySQL code
- Example SQL code: WHERE first_name LIKE %Tadhg%

Selection Functions:

getAll(string $table)
- Select all columns in $table
- Example SQL code: SELECT * FROM users
getSome(string $table, array | string $columns)
- Select specific $columns (or just one column if a string is provided) in $table
- Example SQL code: SELECT username, first_name, last_name FROM users
groupBy(string $column)
- Group together rows with same $column values
- Example SQL code: GROUP BY age
orderBy(string $column, string $order)
- Sort output by $column either ASC or DESC
- Example SQL code: ORDER BY first_name DESC
limit(int $limit)
- Only return $limit rows.
- Example SQL code: LIMIT 3
columns(array $columns)
- Also select $columns as well as whatever was passed in the original getSome()
- column(string $column) allows for selecting a single column.
- Example SQL code: SELECT username, first_name
cast(string $column, string $type)
- Cast a specific $column's data to $type when it is retreived from the database.

casts(array $casts)

Cast multiple columns at the same time in SelectQuery.

$casts must be an array in the format:

// Column name => Type
$casts = array(
  'id' => 'int',
  'username' => 'string',
  'money' => 'float'
);

Update/Insert Functions:

update(string $table, array $values)
- Updates any rows in the $table to new $values.
- $values must be an array in the format:
```
// Column name => Value
$values = array(
  'username' => 'Aber'
);
```
  - Example SQL code: UPDATE users SET username = 'Aberdeener' WHERE ...
insert(string $table, array $row)
- Inserts a new row into $table.
- $row must be an array in the format:
```
// Column name => Value
$row = array(
  'username' => 'Aberdeener',
  'first_name' => 'Tadhg',
  'last_name' => 'Boyle'
);
```
- Example SQL code: INSERT INTO users (username, first_name, last_name) VALUES ('Aberdeener', 'Tadhg', 'Boyle')
onDuplicateKey(array $values)
- Upon an insertion, if there is a unique column and it is overridden, run this code instead.
- $values must be an array in the format:
```
// Column name => New value
$values = array(
  'username' => 'Aber'
);
```
- Example SQL code: ON DUPLICATE KEY UPDATE username = 'Aber'

Other Functions:

Functions which are not in Selection or Update/Insert queries

execute(string $query)
- Execute provided $query and output results.
- Common usage would be raw queries where Koss does not have functionality to help.
- Note: Cannot be mixed with other functions

Examples

All assuming you have autoloaded Koss.php and created a new instance of it with your database credentials.

Selecting information

// Get the "username" and "first_name" column in the "users" table, limit to only the first 5 rows, and sort by their username descending.
$results = $koss->getSome('users', ['username', 'first_name'])->limit(5)->orderBy('username', 'DESC')->execute();
// MySQL Output: SELECT `username`, `first_name` FROM `users` ORDER BY `username` DESC LIMIT 5

// Get all columns in the "users" table, and when they're logged in, limit to only the first 5 rows.
// Note the usage of new variable, $query in anonymous function. This will be passed by Koss.
$results = $koss->getAll('users')->when(fn() => isset($_SESSION['logged_in']), fn(SelectQuery $query) => $query->limit(5))->execute();
// MySQL Output: SELECT * FROM `users` LIMIT 5

// Get the "username" column in the "users" table, but also select the "last_name" column.
$results = $koss->getSome('users', 'username')->columns(['last_name'])->execute();
// MySQL Output: SELECT `username`, `last_name` FROM `users`

Inserting information

// Insert a new row into the "users" table, if there is a unique row constraint, update only the username to "Aber"
$koss->insert('users', ['username' => 'Aberdeener', 'first_name' => 'tadhg', 'last_name' => 'boyle'])->onDuplicateKey(['username' => 'Aber'])->execute();
// MySQL Output: INSERT INTO `users` (`username`, `first_name`, `last_name`) VALUES ('Aberdeener', 'tadhg', 'boyle') ON DUPLICATE KEY UPDATE `username` = 'Aber'

Updating information

// Update any existing rows in the "users" table which match the following criteria, update the username to "Aber" and the first_name to "Tadhg" where their "id" is 1 and their last_name is "Boyle"
$koss->update('users', ['username' => 'Aber', 'first_name' => 'Tadhg'])->where('id', 1)->where('last_name', '=', 'Boyle')->execute();
// MySQL Output: UPDATE `users` SET `username` = 'Aber', `first_name` = 'Tadhg' WHERE `id` = '1' AND `last_name` = 'Boyle'

aberdeener / koss

Maintainers

Details