-
Updates
-
Deletes
Laravel's database query builder provides a convenient, fluent interface to creating and running database queries.
- It uses PDO parameter binding to protect your application against SQL injection. There is no need to clean strings passed as bindings.
First, add the
DB facade in your controller:
use Illuminate\Support\Facades\DB;
Then, you may use the
table() method on the
DB facade to begin a query.
Inserts
To execute Inserts statements with query builder, use the
insert() method. It accepts an array of column names and values.
DB::table('users')->insert(['name'=>'MarPlo', 'votes'=>0]);
- To insert several records into the table with a single call to insert(), pass an array of arrays.
DB::table('users')->insert([
['name'=>'MarPlo', 'votes'=>0],
['name'=>'PloMar', 'votes'=>0]
]);
Auto-Incrementing ID
If the table has an
AUTO-INCREMENTING id, use the
insertGetId() method to insert a record and then retrieve the ID.
$id = DB::table('users')->insertGetId(['name'=>'MarPlo', 'votes'=>0]);
Updates
To update records in MySQL with query builder, use the
update() method. It accepts an array of column names and values.
You may also use
where() clauses.
DB::table('users')->where('id', 1)->update(['votes'=>1]);
- When
updating a JSON column (
on databases that support JSON columns), use "->" syntax to access the appropriate key in the JSON object.
DB::table('users')->where('id', 1)->update(['options->enabled'=>true]);
Increment / Decrement
For incrementing or decrementing the value of a column, you may use the
increment('col', $nr) / decrement('col', $nr) methods.
The first argument is the 'column name', the second argument is optional, a number to control the amount by which the column should be incremented or decremented.
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
- You may also specify additional columns to update during the operation.
DB::table('users')->increment('votes', 1, ['name'=>'MarPlo']);
Deletes
To delete records in MySQL with query builder, use the
delete() method.
You may also use
where() clauses.
DB::table('users')->where('votes', '>', 10)->delete();
- If you wish to truncate the entire table (
which will remove all rows and reset the auto-incrementing ID to zero), you may use the
truncate() method.
DB::table('users')->truncate();
- Documentation:
Laravel - Database: Query Builder