Querying

Queries can be performed by calling methods on an instance of p810\MySQL\Connection. The returned value is a subclass of p810\MySQL\Query\Statement, and can be chained with supported clauses.

When your query is written, you may call execute() to perform it. Select statements will return an empty array if the query failed or returned no results, or a list of instances of p810\MySQL\Model\Row. All other statements currently return the number of rows affected by the query.

Statements

Select

$db->select('*', 'users'); // SELECT * FROM `users`
$db->select(['username', 'password'], 'users'); // SELECT `username`, `password` FROM `users`

Handling results

Each result is an instance of p810\MySQL\Model\Row.

To access columns, use Row::get(), and supply either an array of column names or a single column name.

$user->get(['username', 'password']);

Updates may be committed to the database by calling Row::set(). This method takes an associative array of columns to update and their values. Updates are only possible if a unique key and value are set on the object.

$user->set([
    'username' => 'Payton',
    'about' => 'Lorem ipsum dolor sit amet...'
]);

mysql-helper will look for a primary key to associate with the table when the object is constructed, following the pattern of the table's singular name suffixed with _id (eg user_id). This can only be found for queries if the column is apart of the result data.

To specify a key to use to identify the row and enable updating through set(), call Row::identifyBy() and supply the column name, and value (if the column is not in the result data).

$row->identifyBy('unique_column');
$row->identifyBy('unique_column', 'unique_value'); // Assuming that unique_column was not selected

Conditionally finding rows

$db->select('username', 'users')->where('user_id', '>', 1); // SELECT `username` FROM `users` WHERE `user_id` > 1;

Ordering results

Available ordering methods are orderAsc() and orderDesc() for ascending and descending respectively.

$db->select('*', 'users')->orderAsc('user_id'); // SELECT * FROM `users` ORDER BY `user_id` ASC;

Limiting results

$db->select('*', 'users')->limit(50); // SELECT * FROM `users` LIMIT 50;

Update

$db->update('users', ['username' => 'Payton'])->where('username', 'NotPayton'); // UPDATE `users` SET `username` = 'Payton' WHERE `username` = 'NotPayton'

Insert

// INSERT INTO `users` (`username`, `password`) VALUES 'Payton', 'password'
$db->insert('users', [
    'username' => 'Payton',
    'password' => 'password'
]);

Delete

$db->delete('users'); // DELETE FROM `users`
$db->delete('users')->where('user_id', 1); // DELETE FROM `users` WHERE `user_id` = 1