Hello guys,
Sometimes you have raw SQL query which you want to execute in your Laravel application. In this article, I will show you how you can execute raw SQL query in Laravel application.
Laravel DB facade provides select, insert, update, delete and statement methods for running each type of query. We will discuss all methods with example.
select() method
Laravel's select method allows you to execute SELECT method.
/**
* get all users
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$users = \DB::select('SELECT * from users');
dd($user); // 1
}
If you are injecting data into query, you can pass in second parameter in array.
/**
* get user by email
*
* @return void
*/
public function index(Request $request)
{
$users = \DB::select('SELECT * from users WHERE email = ?', [$request->email]);
dd($users);
}
array:1 [▼
0 => {#282 ▼
+"id": 1
+"name": "Harsukh"
+"email": "harsukh21@gmail.com"
+"email_verified_at": null
+"password": "$2y$10$XwA4wnJ/2O"
+"remember_token": null
+"created_at": "2020-08-11 12:40:50"
+"updated_at": "2020-08-11 12:40:50"
}
]
The select() method always return array of records.
insert() method
Laravel's DB::insert() method allows you to SQL insert query. You can define insert data in second parameter.
/**
* insert new user
*
* @return void
*/
public function store(Request $request)
{
$users = \DB::insert('INSERT into users (name, email) VALUES (?, ?)', [$request->name, $request->email]);
dd($users); // true
}
update() method
To use SQL UPDATE method, Laravel provides DB facades update method.
/**
* update user
*
* @return void
*/
public function update(Request $request)
{
$users = \DB::update('UPDATE users set name = ? WHERE id = ?', ['manish', '1']);
dd($users); // true
}
delete() method
If you want to run SQL DELETE statement, you can use DB::delete() method.
/**
* delete user
*
* @return void
*/
public function delete(Request $request)
{
\DB::delete('DELETE from users WHERE id = ?', ['2']);
}
statement() method
This method is used to run general query which doesn't return any value.
/**
* destroy user
*
* @return void
*/
public function destroy(Request $request)
{
\DB::statement('DROP table users');
}
unprepared() method
These are all prepared statement to prevent SQL injection into database. However you might needed to run query without binding value. To run query, use unprepared() method.
/**
* view user
*
* @return void
*/
public function view(Request $request)
{
\DB::unprepared('INSERT into users (name, email) VALUES ("Harsukh", "harsukh21@gmail.com")');
}
Note: Avoid run unprepared() method on user input value, this can be risk for SQL injection.
This way, you can run SQL query into Laravel application.
