yii2 Working with Databases Using Yii2 query builder

Example

Yii2 provides efficient ways to retrieve data from the database.Consider an example of a simple employee table having fields emp_id, emp_name and emp_salary. In order to retrieve the employee names and their salaries, we use the query.

select emp_name,emp_salary from employee

To generate the above query in Yii2, there are a lot of methods.One of the method is to use a yii\db\Query object.

//creates a new \yii\db\Query() object
$query=new \yii\db\Query(); 

$rows=$query->select(['emp_name','emp_salary']) //specify required columns in an array
             ->from('employee') //specify table name
             ->all(); //returns an array of rows with each row being an associative array of name-value pairs.

We can make use of a foreach loop to loop through each name-value pair in the $rows array.

foreach ($rows as $row) {
    echo "Employee Name: ".$row['emp_name'].",Employee Salary: ".$row['emp_salary']."<br>";
}

This will output

Employee Name: Kiran,Employee Salary: 25000

Employee Name: Midhun,Employee Salary: 50000

Employee Name: Jishnu,Employee Salary: 20000

Employee Name: Ajith,Employee Salary: 25000

Employee Name: Akshay,Employee Salary: 750000

More Examples

Suppose we need to find the name of employees whose salary is equal to 25000.We can write the query in sql as

select emp_name from employee where salary=25000

In Yii2, the code for generating the above query

$query=new \yii\db\Query(); 

$rows=$query->select(['emp_name']) 
            ->from('employee')
            ->where(['emp_salary'=>25000]) //specify the condition as an associative array where key is column name
            ->all(); 

If we need to find employee names whose salary is greater than 25000,We can write the code in Yii2 as

  $rows=$query->select(['emp_name']) 
        ->from('employee')
        ->where(['>','emp_salary', 25000]) 
//Here first element in the above array specify relational operator used, second element specify the table name and third the value itself.
        ->all();