Select - adampatterson/Dingo-Framework GitHub Wiki

A Simple Select

Here is an example of a simple seelct query:

// Query the database
$data = db('mytable')->select('*')
                 ->where('user','=','Evan')
                 ->execute();

The above works exactly the same as the following SQL:

SELECT * FROM `mytable` WHERE `user`='Evan'

Because our query was such a simple one we can do it the shorthand way:

// Query the database
$data = db('mytable')->select('user','=','Evan');

Select All

Selecting all the rows of a table is easy:

$data = db('mytable')->all();

A More Complex Example

Here we are going to make a slightly more complex query:

// Select the table
$table = db('mytable');

// Query the database
$data = $table->select('user','id')
          ->where('user','=','Evan')
          ->clause('AND')
          ->where('id','!=',123456)
          ->execute();

The above is the same as this SQL:

SELECT `user`,`id` FROM `mytable` WHERE `user`='Evan' AND `id`!='123456'

Now let's organize our results by the user's name in descending order:

// Select the table
$table = db('mytable');

// Query the database
$data = $table->select('user','id')
          ->where('user','=','Evan')
          ->clause('AND')
          ->where('id','!=',123456)
          ->order_by('user','DESC')
          ->execute();

And here is the SQL version:

SELECT `user`,`id` FROM `mytable` WHERE `user`='Evan' AND `id`!='123456' ORDER BY `user` DESC

To round things up let's organize our results by the user's name and ID in acsending order. Just for fun :)

// Select the table
$table = db('mytable');

// Query the database
$data = $table->select('user','id')
          ->where('user','=','Evan')
          ->clause('AND')
          ->where('id','!=',123456)
          ->order_by('user')
          ->order_by('id','ASC')
          ->execute();

SELECT `user`,`id` FROM `mytable` WHERE `user`='Evan' AND `id`!='123456' ORDER BY `user` AND `id` ASC

Limit

You can limit the number of results returned by a select query with the limit method.

// Select the table
$table = db('mytable');

// Only returns at max 5 rows
$data = $table->select('*')
          ->limit(5)
          ->execute();

Offset

You can set the number of results to skip returned by a select query with the offset method.

// Select the table
$table = db('mytable');

// Returns all rows after row 5 $data = $table->select('*') ->offset(5) ->execute();

Pagination

Dingo makes it really easy to paginate your queries:

// Select the table
$table = db('mytable');

// Limit number of results
$limit = 10;

// Current page
$current_page = 2;

// Returns 10 rows starting at row 10
$data = $table->select('*')
          ->paginate($current_page,$limit,$page)
          ->execute();

An instance of the Pagination Library class is stored in the $page variable for future use. If you do not intend to use any of the useful pagination class methods, simply ommit the third argument.