Discover Cygnite Framework To Make Your Job Simple And Better.

Framework
A Modern PHP Framework For Web Developers.
Latest Stable Version Total Downloads

Query Builder

Documentation

Active Record

  1. Query Builder
  2. Dynamic Finders
  3. Create
  4. Read
  5. Update
  6. Delete

Basic CRUD

CRUD defined by Wikipedia:

CRUD is the day to day task for every developers. Writing queries and running against the database may be painful at some point of time for developers, where Cygnite Framework takes your pain and provides you convenient way of running database queries with the ActiveRecord. Cygnite Framework makes your Create, Read, Write, Delete operations extremely simple and expressive.

Create or Save Records Into Database

This is where Cygnite makes your job so simple, it follows activerecord style. Every model class act as a database table object. Save records into a table using instance of your model. Here we register a new user for our blog by simply instantiating a object of Users model and finally save it into a table.

 

 use Apps\Models\User;

 $user = new User();
 $user->first_name = 'Sanjoy';
 $user->last_name = 'Dey';
 $user->email_address = 'sanjoy09@hotmail.com';
 $user->gender = 'Male';
 $user->save();

The above code is equivalent of writing.


#sql => INSERT INTO `users` (first_name, last_name, email_address, gender) VALUES('Sanjoy', 'Dey', 'sanjoy09@hotmail.com', 'Male');

Isn't much easier to create a record into the database.

We recommend you to have auto increment primary field name as 'id'. Get the last inserted id by your user object "$user->id;";

Getting Last Inserted Id


  echo $user->id;

Read or Select From Table

Retrieving rows from a table is very easy and simple by using Cygnite Dynamic finders and ActiveRecord.

Query Builder

Select a particular column or all columns of the table with single line of code.



 $data = $this->select('first_name,last_name,email_address')->findAll();

 or

 $model = new User;

 $data = $model->select('*')->findAll();


Using Where Condition

Single where method does multiple tasks. For example.

Using Single Where condition



  $this->where('name', 'Sanjoy', '=');
  #sql => WHERE `name` = 'Sanjoy';


Using multiple where conditions with AND



 //Where with AND conditions
  $where = array(
               'name =' => 'Sanjoy',
               'id >'  =>  '4'
         );
  $this->where($where);
  #sql => WHERE `name` = 'Sanjoy' AND `id` > 4;

  $whereAnd = array(
                  'entry_date LIKE'  => '%2013-08-23%',
                  'name ='           => 'Sanjoy'            
             );
  $this->where($whereAnd);

  $whereLike = array(
              'name LIKE' => '%Cygnite',
              'comment LIKE'    => '%ORM%'            
           );


  $whereDate = array(
              'entry_date >=' => '2013-08-24 05:00:00',
              'entry_date <=' => date('Y-m-d'),            
           );
  $this->where($whereDate);
  #sql => WHERE `entry_date` >= '2013-08-24 05:00:00' AND `entry_date` <= CURDATE();

Using Where In

  
  $whereIn = array(
              'name IN' => '#"Cygnite","Orm","ActiveRecord"',            
             );

  $this->where($whereIn);
  #sql => WHERE `name` IN ('Cygnite', 'ORM', 'ActiveRecord');

[Note: Limitation - You cannot single condition as an array format into the where method. It will give you unknown result.]

Using Order By


$this->orderBy('id','DESC');

Using Group By

 
$this->groupBy(array('name'));

Group By with multiple fields

 
$this->groupBy(array('name','id'));

Limit Your Queries

 
 $this->limit(3);
 #sql => LIMIT 0,3

 $this->limit(1,3);
 #sql => LIMIT 1,3

All together in a single line of code
You can now execute queries in a single line of code, as shown below.

 
 $this->select("first_name, last_name, email_address")
       ->where($where)
       ->groupBy(array('name'))
       ->orderBy('id','DESC')
       ->limit(3)
       ->findAll();

Result-Set Types

By default Cygnite ActiveRecord will return you the results in the form of object. You can also get the results in different ways, simply by passing result type into findAll() method. Currently there are GROUP, BOTH, JSON, ASSOC, OBJ, COLUMN different types are supported by ActiveRecord.

For example

 
 $this->select('*')->findAll('JSON');

[Note: $this should be used only if you are building query inside in your model class. If you are trying to access the query builder from the controller then you need to create an model instance to access fluent query builder. $user->select("*")->findAll(); ]

Updating Record In A Table

We access model instance to update the records in a table. It's very simple than you think. It works similar as INSERT/CREATE works.

 
use Apps\Models\User;

 $user = User::find(20);

 $user->first_name = 'Shane';
 $user->last_name = 'Watson';
 $user->email_address = 'shanewatson@gmail.com';
 $user->gender = 'Male';
 $user->save();

You will still have last id echo $user->id; //20

Deleting Records

Deleting a record from the Table

 
 use Apps\Models\User;

 $user = new User();
 $user->trash(23);


Deleting multiple records from the Table

 
  $user->trash(array(21,22,23,34), true);

We are still under the active development to bring much more features.

Follow Us On Facebook Twitter Google+ Linkedin
Released Under The MIT Public License. Copyrights @2013-2014. Powered by- Sanjoy Dey Productions.