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

Which tag adds a new line into a paragraph?
<b> <br> <p>
First line ...<br>
Other line...
Which CSS property can be used to add space between letters?
text-size word-spacing letter-spacing
#id {
  letter-spacing: 2px;
What JavaScript function can be used to get access to HTML element with a specified ID?
getElementById() getElementsByTagName() createElement()
var elm = document.getElementById("theID");
var content = elm.innerHTML;
Click on the "echo" correct instruction.
echo "" echo ""; echo """;
echo "Address URL:";
MySQL Database: Query Builder

Last accessed pages

  1. PHP-MySQL free course, online tutorials PHP MySQL code (69086)
  2. Add sounds and audio effects in Flash (3492)
  3. Detect when ScrollBar reaches the bottom of the page (4384)
  4. JQZoom Image Magnifier (12996)
  5. Simple arithmetic calculator in PHP (2104)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (326)
  2. PHP Unzipper - Extract Zip, Rar Archives (86)
  3. Read Excel file data in PHP - PhpExcelReader (82)
  4. The Mastery of Love (78)
  5. PHP-MySQL free course, online tutorials PHP MySQL code (66)