Database: Query Builder
- Introduction
- Retrieving Results
- Selects
- Raw Expressions
- Joins
- Unions
- Where Clauses
- Ordering, Grouping, Limit, & Offset
- Conditional Clauses
- Inserts
- Updates
- Deletes
- Pessimistic Locking
Introduction
Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems.
The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.
Retrieving Results
Retrieving All Rows From A Table
You may use the table
method on the DB
facade to begin a query. The table
method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally get the results using the get
method:
<?php namespace App\Http\Controllers; use Illuminate\Support\Facades\DB; use App\Http\Controllers\Controller; class UserController extends Controller { /** * Show a list of all of the application's users. * * @return Response */ public function index() { $users = DB::table('users')->get(); return view('user.index', ['users' => $users]); } }
The get
method returns an Illuminate\Support\Collection
containing the results where each result is an instance of the PHP StdClass
object. You may access each column's value by accessing the column as a property of the object:
foreach ($users as $user) { echo $user->name; }
Retrieving A Single Row / Column From A Table
If you just need to retrieve a single row from the database table, you may use the first
method. This method will return a single StdClass
object:
$user = DB::table('users')->where('name', 'John')->first(); echo $user->name;
If you don't even need an entire row, you may extract a single value from a record using the value
method. This method will return the value of the column directly:
$email = DB::table('users')->where('name', 'John')->value('email');
Retrieving A List Of Column Values
If you would like to retrieve an array containing the values of a single column, you may use the pluck
method. In this example, we'll retrieve an array of role titles:
$titles = DB::table('roles')->pluck('title'); foreach ($titles as $title) { echo $title; }
You may also specify a custom key column for the returned array:
$roles = DB::table('roles')->pluck('title', 'name'); foreach ($roles as $name => $title) { echo $title; }
Please login to continue.