Query Builder

Introduction

Query Builder - a query designer - provides a convenient, expressive interface for creating and executing database queries. It can be used to perform most types of operations and works with all supported DBMSs.

Using

Running Database Queries

To run a query, use the Table method, which will return an instance of the Query Builder class; at the end of the request, use the Run method to get the result.

namespace App\Controllers;

use App\Models\User;
use Src\Support\Database\DB;

class UserController extends Controller
{
    public function Index()
    {
        $id = DB::table('users')->select(['id'])->run();

        return response($id);
    }
}

Methods

Select

To select specific columns from the database, you can use the Select method.

$id = DB::table('users')->select(['id'])->run();

Insert

To write data to a table, use the Insert method, to set the values that will be written to the field, use the Set method.

The Set method takes 3 parameters: Column - the column in which the value will be written; Value – What will be written to the specified column; Type — optional parameter, the data type of the value.

DB::table('users')->insert()
    ->set('name', 'SomeName')
    ->set('age', 20, PDO::PARAM_INT)
    ->run();

Update

The update method is used to update records in a table.

The Where method takes 4 parameters: Column - the column in which the value will be written; Sign - A sign indicating >, <, =, or !=; Value – What will be written to the specified column; Type — optional parameter, the data type of the value.

DB::table('users')->update()
    ->set('name', 'OtherName')
    ->set('age', 21, PDO::PARAM_INT)
    ->where('name', '=', 'SomeName')
    ->where('age', '>', 19, PDO::PARAM_INT)
    ->run();

Delete

The Delete method is used to delete records from a table.

DB::table('users')->delete()
    ->where('name', '=', 'SomeName')
    ->where('age', '>', 19, PDO::PARAM_INT)
    ->run();