Raw SQL queries are executed using the DB Façade class. In this class, several methods are available for each type of query.
$variable = DB::select('QUERY', [values]);
The first argument is the raw query. The second argument is any parameter bindings that need to be bound to the query (typically WHERE clause constraints). This will always return an array of results.
Example:
public function index() {
$users = DB::select('select * from users where active = ?', [1]);
return view('user.index', ['users' => $users]);
}
Question marks are used to represent parameter bindings, but name bindings can be used instead.
$results = DB::select('select * from users where id = :id', ['id' => 1]);
DB::insert('QUERY', [values]);
Like the select statement, the first parameter is the raw query while the second is the bindings.
Example:
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
$affected = DB::update('QUERY', [values]);
In an update query, the number of rows affected will be returned.
Example:
$affected = DB::update('update users set votes = 100 where name = ?', ['John']);
$deleted = DB::delete('QUERY');
Like the update query, the number of rows affected will be returned.
Example:
$deleted = DB::delete('delete from users');
If the database statement does not return any value, a statement method can be used.
Example:
DB::statement('drop table users');