Understanding Raw SQL Queries:
Raw SQL queries allow developers to execute SQL statements directly within Laravel applications.
Suitable for scenarios where Eloquent ORM or query builder limitations are encountered, complex queries are required, or performance optimization is paramount.
Executing Raw SQL Queries:
Using DB Facades: Laravel provides the DB facade for executing raw SQL queries.
Example:
// Execute a raw SQL query
$users = DB::select('SELECT * FROM users WHERE role = ?', ['admin']);
Parameter Binding:
Parameter binding helps prevent SQL injection by automatically escaping user input.
Pass parameters as an array as the second argument to the select() method.
Example:
// Execute a raw SQL query with parameter binding
$role = 'admin';
$users = DB::select('SELECT * FROM users WHERE role = ?', [$role]);
Retrieving Results:
Raw SQL queries return results as an array of stdClass objects. Iterate over the result set to access individual rows and columns.
Example:
// Iterate over the result set
foreach ($users as $user) {
echo $user->name;
}
DB Methods
The DB facade provides methods for each type of query: select, update, insert, delete, and statement.
1.. Selecting Data:
select() Method: Used to execute a raw SQL SELECT query and retrieve data.
$users = DB::select('SELECT * FROM users');Syntax:
$records = DB::select($query, $bindings);
- $query: The raw SQL query to execute.
- $binding: (optional): An array of parameters to bind to the query.
Parameter binding helps prevent SQL injection attacks and improves code readability.
Example 1:
$users = DB::select('SELECT * FROM users');This examples executes a raw SQL SELECT query to retrieve all records from the users table. The result is returned as an array of stdClass objects, with each object representing a row from the database.
Example 2: Parameter Binding:
$role = 'admin';
$users = DB::select('SELECT * FROM users WHERE role = ?', [$role]);In this example, parameter binding is used to pass the value of the $role variable into the SQL query safely. This helps prevent SQL injection attacks by automatically escaping user input.
2.. Inserting Data:
insert() Method: Executes a raw SQL INSERT query to insert data into a table.
DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'john@example.com']);Syntax:
DB::insert($query, $bindings);
- $query: The raw SQL INSERT query to execute.
- $bindings: (optional): An array of parameters to bind to the query.
Example 1:
DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['Array Singh ', 'singh@example.com']);
This example executes a raw SQL INSERT query to insert a new record into the users table. The values Array Singh and singh@example.com are inserted into the name and email columns, respectively.
Example 2: Parameter Binding:
$name = 'Array Singh';
$email = 'singh@example.com';
DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', [$name, $email]);
In this example, parameter binding is used to pass the values of the $name and $email variables into the SQL query safely. This helps prevent SQL injection attacks by automatically escaping user input.
Transaction Handling:
DB::transaction(function () {
// Execute raw SQL INSERT query within the transaction
DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['Array Singh', 'singh@example.com']);
});
Laravel provides method for handling database transactions when executing raw SQL queries to ensure data integrity. Wrapping the insert operation within a transaction ensures that all changes are either committed together or rolled back in case of an error.
3.. Updating Data:
update() Method: Executes a raw SQL UPDATE query to update existing records in a table.
DB::update('UPDATE users SET email = ? WHERE id = ?', ['newemail@example.com', 1]);Syntax:
DB::update($query, $bindings);
- $query: The raw SQL UPDATE query to execute.
- $bindings (optional): An array of parameters to bind to the query.
Example 1:
DB::update('UPDATE users SET email = ? WHERE id = ?', ['newemail@example.com', 1]);
This example executes a raw SQL UPDATE query to update the email column of a user with id equal to 1. The value newemail@example.com is set for the email column.
Example 2: Parameter Binding
$newEmail = 'updated@example.com';
$userId = 1;
DB::update('UPDATE users SET email = ? WHERE id = ?', [$newEmail, $userId]);
In this example, parameter binding is used to safely pass the values $newEmail and $userId into the SQL query. Parameter bindings helps prevent SQL injection attacks by automatically escaping user input.
Transaction Handling:
DB::transaction(function () {
// Execute raw SQL UPDATE query within the transaction
DB::update('UPDATE users SET email = ? WHERE id = ?', ['newemail@example.com', 1]);
});
Transaction handling methods to ensure data integrity when executing raw SQL queries. Wrapping the update operation within a transaction ensures that all changes are either committed together or rolled back in case of an error.
4.. Deleting Data:
delete() Method: Executes a raw SQL DELETE query to remove records from a table.
DB::delete('DELETE FROM users WHERE id = ?', [1]);Syntax:
DB::delete($query, $bindings);
- $query: The raw SQL DELETE query to execute.
- $bindings (optional): An array of parameters to bind to the query.
Example 1:
DB::delete('DELETE FROM users WHERE id = ?', [1]);
This examples executes a raw SQL DELETE query to remove the record from the users table where the id column is equal to 1.
Example 2: Parameter Binding
$userId = 1;
DB::delete('DELETE FROM users WHERE id = ?', [$userId]);
This example, parameter binding is used to safely pass the value of $userId into the SQL query. Parameter binding helps prevent SQL injection attacks by automatically escaping user input.
Transaction Handling:
DB::transaction(function () {
// Execute raw SQL DELETE query within the transaction
DB::delete('DELETE FROM users WHERE id = ?', [1]);
});
Wrapping the delete operation within a transaction ensures that all changes are either committed together or rolled back in case of an error.
5.. Executing Arbitrary Statements:
statement() Method: Used to execute any arbitrary SQL statement that does not return data, such as DDL (Data Definition Language) statements.
DB::statement('ALTER TABLE users ADD COLUMN age INT');
Syntax:
DB::statement($query, $bindings);
- $query: The raw SQL statement to execute.
- $bindings (optional): An array of parameters to bind to the query. Parameter binding helps prevent SQL injection attacks and improves code readability.
Example 1:
DB::statement('ALTER TABLE users ADD COLUMN age INT');
This example executes a raw SQL statement to add a new column named age of type INT to the users table. Since the statement does not return any data, the statement() method is used.
Example 2: Parameter Binding
$columnName = 'age';
$columnType = 'INT';
DB::statement('ALTER TABLE users ADD COLUMN ? ?', [$columnName, $columnType]);
In this example, parameter binding is used to safely pass the values of $columnName and $columnType into the SQL statement.
Transaction Handling:
DB::transaction(function () {
// Execute raw SQL statement within the transaction
DB::statement('ALTER TABLE users ADD COLUMN age INT');
});
Wrapping the statement execution within a transaction ensures that all change are either committed together or rolled back in case of an error.