Yii2 DatePicker

How to add a date range picker to filter for dates on a GridView for Yii2

The ModelSearch class
Your model Students has a date_of_join attribute and we want to add a date range filtering on that value.

declare variable
    public $date_from;
    public $date_to;
add in search function

if($this->date_from && $this->date_to) {
$query->andFilterWhere(['between', 'date_of_join', date('Y-m-d',strtotime($this->date_from)),  date('Y-m-d',strtotime($this->date_to))]);
}


Configuring GridView column

use widget
use dosamigos\datepicker\DateRangePicker;

filtering column

          [ 
                'format'=>'raw',                
                'filter'=>  DateRangePicker::widget([
                                                        'model' => $searchModel,
                                                        'attribute' => 'date_from',
                                                        'attributeTo' => 'date_to',
                                                       'clientOptions' => [
                                                            'autoclose' => true,
                                                            'format' => 'd-m-yyyy',  
                                                            'todayBtn' => true,
                                                        ]
                                                    ]),                                              
                'attribute' => 'date_of_join',           
       'format' =>  ['date', 'php:d-m-Y'],
       'options' => ['width' => '1000'],                
   ],   

Related site - http://www.2amigos.us/blog/how-to-add-a-date-range-picker-to-filter-for-dates-on-a-gridview-for-yii2

Yii2 date range filter

'columns' => [     [          'attribute' => 'date',          'filter' => DateRangePicker::widget(...),     ] ] 



Related Links

Yii2 gridview widget filter

Yii2 Text Input Field

Yii Framework 2.0 ActiveForm Input Fields

'yii\widgets\ActiveForm' class is used to create a form and 'yii\helpers\Html' class is used to display the different type of HTML input fields like buttons, textbox, select box etc.

ActiveForm::begin() - creates a form instance and  beginning of the form.
ActiveForm::begin() and ActiveForm::end() - All of the content placed between this.

Use the namespace For ActiveForm

<?php
use yii\helpers\Html;
use yii\widgets\ActiveForm;
?>

'ActiveForm' namespace is very important to create the a active form and 'Html' namespace is very useful to display the different html input fields.

Active Form Begin And End

<?php
use yii\helpers\Html;
use yii\widgets\ActiveForm;

//$form = ActiveForm::begin(); //Default Active Form begin
$form = ActiveForm::begin([
    'id' => 'active-form',
    'options' => [
    'class' => 'form-horizontal',
    'enctype' => 'multipart/form-data'
    ],
])
/* ADD FORM FIELDS */
ActiveForm::end();
?>

Yii2 Text Input Field

//Format 1
<?= $form->field($model,'name'); ?>
//Format 2
<?= $form->field($model, 'name')->textInput()->hint('Please enter your name')->label('Name') ?>



yii2 ActiveForm numeric textfield

yii2 textinput to accept only numeric input.

You can use ->textInput(['type' => 'number']

HTML example

<input type="number" min="0" max="10"></input>


<?= $form->field($modelSalesitem, "qty")->textInput(['type' => 'number', 'maxlength' => true, 'onChange' => 'checkStock(this.id);',])->label(false)->error(FALSE) ?>                                   



Yii2 Textinput set as min value

<?= $form->field($modelSalesitem, "qty")->textInput(['type' => 'number', 'min'=>1, 'maxlength' => true])->label(false)->error(FALSE) ?>                                  

Mysql join query on three tables

Mysql join query on three tables with multiple columns

MySQL: Joins

MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever two or more tables are joined in a SQL statement.

There are different types of MySQL joins:

• MySQL INNER JOIN (or sometimes called simple join)
• MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

Example 1

I have three tables like this:

MySql join three table

Specialisation

sid | s_name
--------------
 1  | test 1
 2  | test 2  

Person

pid | name | sid
------------------
 1  | ABC  |  1
 2  | XYZ  |  2

Timing

tid | time_from | time_to  | pid
----------------------------------
 1  | 08:00:00  | 10:00:00 |  1
 2  | 20:00:00  | 22:00:00 |  1
 3  | 09:00:00  | 14:00:00 |  2
 4  | 19:00:00  | 20:00:00 |  2

Get result something like this form

pid | name | s_name | time_from  | time_to
--------------------------------------------
 1  | ABC  | test 1 | 08:00:00   | 10:00:00

Query

SELECT  a.pid, a.name,
        b.sname,
        c.time_from,
        c.time_to
FROM    person a
        INNER JOIN specialisation b
            ON a.sid = b.sid
        INNER JOIN Timing c
            ON a.pid = c.pid
WHERE   a.sid = 1 and 
        a.name='ABC'  AND 
        c.time_from >= '08:00:00' AND c.time_to <= '10:00:00'

Join tables to person, person table index to specialisation and timing table.

Try

SELECT t.pid, p.name, s.s_name, t.time_from, t.time_to 
     FROM   Specialisation AS s
    left join  Person AS p ON p.sid = s.sid
    left join  Timing AS t ON t.pid = p.pid
    WHERE  s.sid = 1
        AND t.time_from >= 08:00:00' AND t.time_to <= 10:00:00
group by t.tid 

Example 2

I have four table

MySql join four table

receipt

receipt_no | ccid | students_admission_number | students_ccid | reference_number | receipt_date | type | ...
------------------------------------------------------------------------------------------------------------

receiptitems

id | receipt_no | ccid | receipt_head_id | ..... | amount
---------------------------------------------------------

receipthead

id | receipt_no | ccid | receipt_head_id | ..... | amount
---------------------------------------------------------




students

admission_number | ccid | student_name | application_reference_no | ..... 
-------------------------------------------------------------------------




Get result something like this form

Query

select r.receipt_no as receipt_no, r.ccid as ccid,r.students_admission_number as admission_number, (select CONCAT(application_reference_no, ' \n ', student_name) from students where admission_number = r.students_admission_number and ccid = r.students_ccid) as student_name, r.reference_number as reference_number, DATE_FORMAT(receipt_date, '%d-%m-%Y') AS date,receipt_head, amount
from receiptitems
LEFT JOIN receipt as r on (receiptitems.receipt_no = r.receipt_no and receiptitems.ccid = r.ccid)
LEFT JOIN receipthead ON (receipthead.id = receiptitems.receipt_head_id)
where r.type = 'STUDENTS' and r.students_admission_number > 0 and r.ccid = ". $ccid ." and (r.receipt_date BETWEEN '" .$m1."' AND '".$m2."')