Laravel A Framework For Web Artisans

https://tower.la.utexas.edu/docs/database/fluent#dynamic

Requirements

• Apache, nginx, or another compatible web server.
• Laravel takes advantage of the powerful features that have become available in PHP 5.3. Consequently, PHP 5.3 is a requirement.
• Laravel uses the FileInfo library to detect files' mime-types. This is included by default with PHP 5.3. However, Windows users may need to add a line to their php.ini file before the Fileinfo module is enabled. For more information check out the installation / configuration details on PHP.net.
• Laravel uses the Mcrypt library for encryption and hash generation. Mcrypt typically comes pre-installed. If you can't find Mcrypt in the output of phpinfo() then check the vendor site of your LAMP installation or check out the installation / configuration details on PHP.net.

The Basics

The Fluent Query Builder is Laravel's powerful fluent interface for building SQL queries and working with your database. All queries use prepared statements and are protected against SQL injection.

You can begin a fluent query using the table method on the DB class. Just mention the table you wish to query:

$query = DB::table('users');

You now have a fluent query builder for the "users" table. Using this query builder, you can retrieve, insert, update, or delete records from the table.

Retrieving Records

Retrieving an array of records from the database:

$users = DB::table('users')->get();

Note: The get method returns an array of objects with properties corresponding to the column on the table.

Retrieving a single record from the database:

$user = DB::table('users')->first();

Retrieving a single record by its primary key:

$user = DB::table('users')->find($id);

Note: If no results are found, the first method will return NULL. The getmethod will return an empty array.

Retrieving the value of a single column from the database:

$email = DB::table('users')->where('id', '=', 1)->only('email');

Only selecting certain columns from the database:

$user = DB::table('users')->get(array('id', 'email as user_email'));

Retrieving an array with the values of a given column:

$users = DB::table('users')->take(10)->lists('email', 'id');

Note: Second parameter is optional

Selecting distinct results from the database:

$user = DB::table('users')->distinct()->get();

Building Where Clauses

where and or_where

There are a variety of methods to assist you in building where clauses. The most basic of these methods are the where and or_where methods. Here is how to use them:

return DB::table('users')
    ->where('id', '=', 1)
    ->or_where('email', '=', 'example@gmail.com')
    ->first();

To do the equivalent of an AND where, simply chain the query with another where:

return DB::table('users')
    ->where('id', '=', 1)
    ->where('activated', '=', 1)
    ->first();

Of course, you are not limited to simply checking equality. You may also use greater-than,less-than, not-equal, and like:

return DB::table('users')
    ->where('id', '>', 1)
    ->or_where('name', 'LIKE', '%Taylor%')
    ->first();

As you may have assumed, the where method will add to the query using an AND condition, while the or_where method will use an OR condition.

where_in, where_not_in, or_where_in, and or_where_not_in

The suite of where_in methods allows you to easily construct queries that search an array of values:

DB::table('users')->where_in('id', array(1, 2, 3))->get();
 
DB::table('users')->where_not_in('id', array(1, 2, 3))->get();
 
DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_in('id', array(1, 2, 3))
    ->get();
 
DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_not_in('id', array(1, 2, 3))
    ->get();

where_null, where_not_null, or_where_null, and or_where_not_null

The suite of where_null methods makes checking for NULL values a piece of cake:

return DB::table('users')->where_null('updated_at')->get();
 
return DB::table('users')->where_not_null('updated_at')->get();
 
return DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_null('updated_at')
    ->get();
 
return DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_not_null('updated_at')
    ->get();

where_between, where_not_between, or_where_between, and or_where_not_between

The suite of where_between methods makes checking if values fall BETWEEN a minimum and maximum super easy :

return DB::table('users')->where_between($column, $min, $max)->get();   
 
return DB::table('users')->where_between('updated_at', '2000-10-10', '2012-10-10')->get();
 
return DB::table('users')->where_not_between('updated_at', '2000-10-10', '2012-01-01')->get();
 
return DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_between('updated_at', '2000-10-10', '2012-01-01')
    ->get();
 
return DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_not_between('updated_at', '2000-10-10', '2012-01-01')
    ->get();