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.
<?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 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)
  {{$user->name}}
@endforeach
- 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;
});

Aggregates

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();
//or:
$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']
])->get();

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');
})->get();
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:
DB::table('users')->whereExists(function($query){
  $query->select(DB::raw(1))->from('orders')
   ->whereRaw('orders.user_id = users.id');
})->get();
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

HTML
CSS
JavaScript
PHP-MySQL
Which HTML element can be used to embed a SWF flash content?
<object> <div> <script>
<object type="application/x-shockwave-flash" data="file.swf" width="500" height="250">
 <param name="src" value="file.swf" />
 Your browser not support SWF.
</object>
Which CSS pseudo-class adds a style to an input form field that has keyboard input focus?
:active :focus :hover
input:focus {
  background-color: #88fe88;
}
Click on the instruction which converts a JSON string into a JavaScript object.
JSON.stringify(javascript_object) object.toString() JSON.parse(json_string)
var jsnstr = '{"url": "http://coursesweb.net/", "title": "Web Development Courses"}';
var obj = JSON.parse(jsnstr);
alert(obj.url);
Indicate the PHP function which can be used to create or write a file on server.
fopen() file_put_contents() file_get_contents()
if (file_put_contents("file.txt", "content")) echo "The file was created";
else echo "The file can not be created";
MySQL Database: Query Builder

Last accessed pages

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (142308)
  2. Get and Modify content of an Iframe (32367)
  3. $_GET, $_POST and $_REQUEST Variables (33884)
  4. Ajax-PHP Chat Script (49508)
  5. JavaScript Course - Free lessons (31647)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (311)
  2. The Mastery of Love (48)
  3. CSS cursor property - Custom Cursors (36)
  4. Read Excel file data in PHP - PhpExcelReader (35)
  5. PHP-MySQL free course, online tutorials PHP MySQL code (31)