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.
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": "[email protected]"
+"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.
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
}
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
}
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']);
}
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');
}
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", "[email protected]")');
}
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.
Hi, My name is Harsukh Makwana. i have been work with many programming language like php, python, javascript, node, react, anguler, etc.. since last 5 year. if you have any issue or want me hire then contact me on [email protected]
How to create Event Emitter in Node.js
Node.js is perfect for event driven appl...How to remove empty values from an array in PHP
Use the PHP array_filter() fun...How to check if record exists or not in Laravel
In this article, I will show you how you...Laravel 8 - Create Custom Helper Functions
This web article is focused on laravel 8...How to assign block of HTML code to a JavaScript variable
Use the concatenation operator (+) Th...