- Chunking Results
- Aggregates
- Where Clauses
- Advanced where clauses
- Ordering, Grouping, Limit, Offset

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 DB::table() method to begin a query.

Retrieving Results

You can apply and chain more methods to DB::table('table_name') to obtain the results you want.

Get All Rows from a Table

To get all the rows from a table, just apply the get() method
- Example: Simple UserController with an index() method that gets all the users, and returns a view.
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 users.
  //@return view
  public function index(){
    $users = DB::table('users')->get();
    return view('user.index', ['users' => $users]);
The get() method returns an array with objects for each row. The columns name are properties in the object.
You can use a foreach() instruction to parse the results.
foreach($users as $user){
  echo $user->name;
- Or, in the view blade file:
@foreach($users as $user)
- To get distinct results, apply the distinct() method:
$users = DB::table('users')->distinct()->get();

Retrieving a Single Row / Column from a Table

To get a single row from the database table, you may use the first() method. It returns a single object with columns name as properties:
$user = DB::table('users')->where('name', 'MarPlo')->first();

echo $user->name;
- If you need a single value from a record, apply the value('column_name') method. It returns directly the value of the column:
$email = DB::table('users')->where('id', '8')->value('email');

Retrieving a list of Column Values

To get a result with the values from all the rows of a single column, you may use the pluck('column_name') method:
$titles = DB::table('articles')->pluck('title');

foreach($titles as $title){
  echo '<br>'.$title;
- You may also specify a custom key column for the returned Collection:
$titles = DB::table('articles')->pluck('title', 'key');

foreach($titles as $key=>$title){
  echo '<br>'.$title;

Selecting specified columns

To select specified columns from a table, use the select() method with the columns name as arguments (you can even add an Alias, with "as"):
$users = DB::table('users')->select('name', 'emails as mail')->get();
If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect() method:
$query = DB::table('users')->select('name', 'emails as mail');

$users = $query->addSelect('age')->get();

Raw SQL Expressions

To create a raw SQL expression, you may use the DB::raw() method. These expressions will be injected into the query as strings (they are not automatically filtred against SQL injection).
$users = DB::table('users')->select(DB::raw('count(*) as nru, status'))
->where('status', '<>', 1)->groupBy('status')->get();

Chunking Results

The chunk() method is useful when you need to work with thousands of database records.
This method retrieves a small chunk of the results at a time and feeds each chunk into a Callback function for processing.
- Here is an example that selects the entire users table in chunks of 100 records at a time:
DB::table('users')->orderBy('id')->chunk(100, function($users){
  foreach($users as $user){
To stop further chunks from being processed, apply return false; in the Callback function:
DB::table('users')->orderBy('id')->chunk(100, function($users){
  return false;


The Laravel query builder provides a variety of aggregate methods: max(), min(), avg(), and sum(). You may call any of these methods after constructing your query:
$nr_users = DB::table('users')->count();

$price = DB::table('orders')->min('price');
- You may combine these methods with other clauses:
$price = DB::table('orders')->where('finalized', 1)->avg('price');

Where Clauses

To add WHERE clauses to the query, you may use the where() method .
The basic call to where() requires three arguments:
- The name of the column.
- An operator.
- The third argument is the value to evaluate against the column.
If you simply want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where() method.

For example, here is a query that selects the rows where the value of the "votes" column is equal to 10:
$users = DB::table('users')->where('votes', '=', 10)->get();
$users = DB::table('users')->where('votes', 10)->get();
- You may use a variety of other operators when in the WHERE clause:
$users = DB::table('users')->where('votes', '>=', 10)->get();

$users = DB::table('users')->where('votes', '<>', 12)->get();

$users = DB::table('users')->where('name', 'like', 'Ma%')->get();
You may pass an array of conditions to the where() function (they will be joined with the AND operator):
$users = DB::table('users')->where([
['status', '=', '1'], ['pages', '<>', '1']

OR Statements

You may chain where() function together with the orWhere(), which is used for OR statements. The orWhere() method accepts the same arguments as the where():
$users = DB::table('users')->where('votes', '>', 8)
->orWhere('name', 'MarPlo')->get();

Additional Where Clauses

Advanced where clauses

The Laravel query builder can handle nested parameter groupings or "where exists" clauses.

Parameter Grouping

Here is an example of grouping multiple where parameters:
DB::table('users')->where('name', '=', 'MarPlo')->orWhere(function($query){
  $query->where('votes', '>', 10)->where('title', '<>', 'Admin');
The function passed into the orWhere() method receives a query builder instance which you can use to set the conditions that should be contained within the parenthesis group.
- The example above will produce the following SQL:
select * from users where name='MarPlo' or (votes >10 and title <> 'Admin')

Where Exists Clauses

The whereExists() method allows you to write where exists SQL clauses. The whereExists() method accepts a function as argument, which will receive a query builder instance allowing you to define the query that should be placed inside of the SQL "exists" clause:
   ->whereRaw('orders.user_id = users.id');
The query above will produce the following SQL:
select * from users where exists (
select 1 from orders where orders.user_id = users.id

JSON Where Clauses

Laravel supports querying JSON column types on databases that provide support for JSON column types: MySQL 5.7+ and Postgres.
To query the items of a JSON column, use the "->" operator:
$users = DB::table('users')->where('preferences->meal', 'salad')->get();

Ordering, Grouping, Limit, and Offset

- Documentation: Laravel - Database: Query Builder

Daily Test with Code Example

Which attribute indicates that an input field must be filled out before submitting?
required="required" autofocus="autofocus" placeholder="text"
<input type="text" name="name1" required="required" />
Which CSS method increases or decreases the size of the HTML element (including its content)?
translate() scale() skew()
#some_id:hover {
  transform: scale(2, 1.5);
  -ms-transform: scale(2, 1.5);    /* IE 9 */
  -moz-transform: scale(2, 1.5);   /* Firefox */
Click on the Date object method that returns the day of the month.
setDate() getMonth() getDate()
var rightnow = new Date();
var day = rightnow.getDate();
Which function is used to upload a file on server?
is_file() move_uploaded_file() fopen()
if(move_uploaded_file($_FILES["field_name"]["tmp_name"], "dir/file_name")) {
  echo "The file succesfully uploaded";
MySQL Database: Query Builder

Last accessed pages

  1. Add Text in Canvas from Input text field, as it is Typed (6829)
  2. CSS Course - Free lessons (11274)
  3. PHP PDO - Introduction and Connecting to Databases (7712)
  4. SSEP - Site Search Engine PHP-Ajax (8600)
  5. Wake Up! (5605)

Popular pages this month

  1. Making DIV Contents Scroll Horizontally, with multiple Div`s inside (997)
  2. Contact page - CoursesWeb (958)
  3. Tabs effect with CSS (955)
  4. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (402)
  5. Insert, Select and Update NULL value in MySQL (369)