mysql.md

MySQL

• Introduction
• Select
• Filter
• Sorting
• Other

Introduction

/** @var \TinyFramework\Database\MySQL\Query $query */
$query = container('database')->query();

Select

use TinyFramework\Database\MySQL\Query;
/** @var Query $query */
$query->select(['field1', 'field2']);

SELECT field1, field2...

From

use TinyFramework\Database\MySQL\Query;
/** @var Query $query */
$query->table('tablename');

... FROM tablename

Filter

AND filter

use TinyFramework\Database\MySQL\Query;
/** @var Query $query */
$query
    ->where($field = 'field1', $operation = '=', $value = 'peter')
    ->where($field = 'field2', $operation = '=', $value = 'lustig');

... WHERE field1 = "peter" and field2 = "lustig"

OR Filter

use TinyFramework\Database\MySQL\Query;
/** @var Query $query */
$query
    ->where($field = 'field1', $operation = '=', $value = 'peter')
    ->orWhere($field = 'field2', $operation = '=', $value = 'lustig');

... WHERE field1 = "peter" or field2 = "lustig"

Combines

use TinyFramework\Database\MySQL\Query;
/** @var Query $query */
$query
    ->where($field = 'field1', $operation = '=', $value = 'peter')
    ->whereNested(function(Query $query){
        $query
            ->where($field = 'field2', $operation = '=', $value = 'lustig')
            ->orWhere($field = 'field3', $operation = '=', $value = 'mayer');
    });

... WHERE field1 = "peter" and (field2 = "lustig" or field3 ? "mayer")

Sorting

use TinyFramework\Database\MySQL\Query;
/** @var Query $query */
$query
    ->orderBy('field1', 'desc')
    ->orderBy('field2', 'asc');

... ORDER BY field1 desc, field2 asc

Other

use TinyFramework\Database\MySQL\Query;
/** @var Query $query */
$query->each(function($row) { /* ... */ });
$query->first(); // first row (LIMIT 1)
$query->count(); // integer (SELECT COUNT(1))
$query->delete(); // DELETE FROM ...
$query->firstOrFail(); // first row or \RuntimeException if empty
$query->limit(5); // LIMIT 5
$query->offset(3); // OFFSET 3
$query->paginate(20, 1); // [data => [], paging => [...]]