Laravel Course

- 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 =');
The query above will produce the following SQL:
select * from users where exists (
select 1 from orders where orders.user_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

What attribute makes the input text box to not be modified by the user?
checked="checked" readonly="readonly" disabled="disabled"
<input type="text" value="fixed-value" readonly="readonly" name="a_name" />
What CSS property allows you to create rounded corners in your webpage design?
background-size border-size border-radius
.class {
  border:2px solid blue;
What instruction displays a confirmation dialog box to the viewer, who must then click OK or Cancel to proceed?
indexOf() confirm() prompt()
var ques = window.confirm("The result of 0+0 is 0?");
if (ques) alert("Corect");
else alert("Incorrect");
Indicate the PHP function that returns the lowest number of the parameter values.
floor() ceil() min()
$min_nr = min(12, 8, 25, 13);
echo $min_nr;        // 8
MySQL Database: Query Builder

Last accessed pages

  1. Get and change IFrame content through a JavaScript script created in another IFrame (11835)
  2. PHP MySQL - INSERT INTO (13234)
  3. querySelector and querySelectorAll (18511)
  4. JavaScript Course - Free lessons (23063)
  5. jQuery Drag and Drop Rows between two similar Tables (9692)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (84)
  2. Read Excel file data in PHP - PhpExcelReader (65)
  3. PHP-MySQL free course, online tutorials PHP MySQL code (55)
  4. SHA1 Encrypt data in JavaScript (54)
  5. querySelector and querySelectorAll (48)