Tuesday 19 September 2017

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."')

Monday 18 September 2017

Yii2 change current user password

Yii2 user change own password

Yii2 framework generate password hashed format.

The following will hash the password. Do what you need with it:


Yii::$app->security->generatePasswordHash($password);

This was taken from the setPassword() function in the advanced app. Source:

Steps

/backend/models/ChangePasswordForm.php

<?php
namespace backend\models;

use Yii;
use yii\base\InvalidParamException;
use yii\base\Model;
use common\models\User;

/**
* Change password form for current user only
*/
class ChangePasswordForm extends Model
{
public $id;
public $password;
public $confirm_password;

/**
* @var \common\models\User
*/
private $_user;

/**
* Creates a form model given a token.
*
* @param string $token
* @param array $config name-value pairs that will be used to initialize the object properties
* @throws \yii\base\InvalidParamException if token is empty or not valid
*/
public function __construct($id, $config = [])
{
$this->_user = User::findIdentity($id);

if (!$this->_user) {
throw new InvalidParamException('Unable to find user!');
}

$this->id = $this->_user->id;
parent::__construct($config);
}

/**
* @inheritdoc
*/
public function rules()
{
return [
[['password','confirm_password'], 'required'],
[['password','confirm_password'], 'string', 'min' => 6],
['confirm_password', 'compare', 'compareAttribute' => 'password'],
];
}

/**
* Changes password.
*
* @return boolean if password was changed.
*/
public function changePassword()
{
$user = $this->_user;
$user->setPassword($this->password);
$user->password_encrypt= base64_encode($this->password);

return $user->save(false);
}
}

/backend/controllers/SiteController.php

add this code

public function actionChangepassword()
{

$id = \Yii::$app->user->id;

//user details..
$user = $this->findModeluser($id);

try {
$model = new \backend\models\ChangePasswordForm($id);
} catch (InvalidParamException $e) {
throw new \yii\web\BadRequestHttpException($e->getMessage());
}

if ($model->load(\Yii::$app->request->post()) && $model->validate() && $model->changePassword()) {
\Yii::$app->session->setFlash('success', 'Password Changed!');
}

return $this->render('changePassword', [
'model' => $model,
'user' => $user,
]);
}

/backend/views/site/changePassword.php

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

use yii\widgets\DetailView;

use backend\modules\tools\models\Center;
$user->subcenter=Center::getCenter($user->ccid);

$this->title = 'View or Change User Password';

$this->params['breadcrumbs'][] = $this->title;

?>
<div class="user-changePassword box box-body table-responsive">

<?= DetailView::widget([
'model' => $user,
'attributes' => [
'id',
'first_name',
'subcenter',
'username',
'email'
],
]) ?>

<div class="lb-box-header lb-with-border">
<h4 class="lb-box-title"><span class="glyphicon glyphicon-user"></span> Change your password</h4>
</div>
<div class="box-body lb-bgcolor" style="padding: 20px 0px 0px 20px;">
<?php $form = ActiveForm::begin(); ?>

<div class="row">
<div class="col-xs-12 col-sm-6 col-lg-6 lb-div-width1">
<?= Html::activeLabel($model, 'password') ?>
</div>
<div class="col-xs-12 col-sm-6 col-lg-6 lb-div-width1">
<?= $form->field($model, 'password')->passwordInput()->label(FALSE) ?>
</div>
</div>

<div class="row">
<div class="col-xs-12 col-sm-6 col-lg-6 lb-div-width1">
<?= Html::activeLabel($model, 'confirm_password') ?>
</div>
<div class="col-xs-12 col-sm-6 col-lg-6 lb-div-width1">
<?= $form->field($model, 'confirm_password')->passwordInput()->label(FALSE) ?>
</div>
</div>

<div class="form-group" align="center">
<?= Html::submitButton('Change', ['class' => 'btn btn-primary']) ?>
</div>

<?php ActiveForm::end(); ?>
</div>

</div>

* Code used in frontend or backend.

Another method for use old password

ChangePasswordForm model like this include old password.

<?php
    namespace app\models;

    use Yii;
    use yii\base\Model;
    use app\models\Login;

    class ChangePasswordForm extends Model{
        public $oldpass;
        public $newpass;
        public $repeatnewpass;

        public function rules(){
            return [
                [['oldpass','newpass','repeatnewpass'],'required'],
                ['oldpass','findPasswords'],
                ['repeatnewpass','compare','compareAttribute'=>'newpass'],
            ];
        }

        public function findPasswords($attribute, $params){
            $user = Login::find()->where([
                'username'=>Yii::$app->user->identity->username
            ])->one();
            $password = $user->password;
            if($password!=$this->oldpass)
                $this->addError($attribute,'Old password is incorrect');
        }

        public function attributeLabels(){
            return [
                'oldpass'=>'Old Password',
                'newpass'=>'New Password',
                'repeatnewpass'=>'Repeat New Password',
            ];
        }
    }

in add siteController

public function actionChangepassword(){
       $model = new ChangePasswordForm;
       $modeluser = Login::find()->where([
           'username'=>Yii::$app->user->identity->username
       ])->one();

       if($model->load(Yii::$app->request->post())){
           if($model->validate()){
               try{
                   $modeluser->password = $_POST['PasswordForm']['newpass'];
                   if($modeluser->save()){
                       Yii::$app->getSession()->setFlash(
                           'success','Password changed'
                       );
                       return $this->redirect(['index']);
                   }else{
                       Yii::$app->getSession()->setFlash(
                           'error','Password not changed'
                       );
                       return $this->redirect(['index']);
                   }
               }catch(Exception $e){
                   Yii::$app->getSession()->setFlash(
                       'error',"{$e->getMessage()}"
                   );
                   return $this->render('changepassword',[
                       'model'=>$model
                   ]);
               }
           }else{
               return $this->render('changepassword',[
                   'model'=>$model
               ]);
           }
       }else{
           return $this->render('changepassword',[
               'model'=>$model
           ]);
       }
    }

Wednesday 6 September 2017

Yii2 Masked Input

Class yii\widgets\MaskedInput

MaskedInput generates a masked text input.

MaskedInput is similar to yii\helpers\Html::textInput() except that an input mask will be used to force users to enter properly formatted data, such as phone numbers, social security numbers.

Yii2 MaskedInput to collect phone numbers:

echo MaskedInput::widget([
    'name' => 'phone',
    'mask' => '999-999-9999',
]);

You can also use this widget in an yii\widgets\ActiveForm using the widget() method, for example like this:

<?= $form->field($model, 'from_date')->widget(\yii\widgets\MaskedInput::className(), [
    'mask' => '999-999-9999',
]) ?>

Demonstration and usage examples for the Yii 2.0 Masked Input widget.

Masked input to collect date from users

More Masked Inputs

Code paste to your view
use yii\widgets\MaskedInput;

echo MaskedInput::widget([
'name' => 'input-31',
'clientOptions' => ['alias' => 'date']
]);

Example

Masked date input use in active form.

<?= $form->field($model, 'date_of_birth')->widget(\yii\widgets\MaskedInput::className(), [
'name' => 'input-31',
'clientOptions' => ['alias' => 'dd-mm-yyyy'],

]) ?>

Retype your date format in 'clientOptions' => ['alias' => 'dd-mm-yyyy']

Masked Amount input

echo MaskedInput::widget([
'name' => 'input-33',
'clientOptions' => [
'alias' => 'decimal',
'groupSeparator' => ',',
'autoGroup' => true
],
]);

Masked URL input

echo MaskedInput::widget([
'name' => 'input-35',
'clientOptions' => [
'alias' => 'url',
],
]);

Masked Email input

echo MaskedInput::widget([
'name' => 'input-36',
'clientOptions' => [
'alias' => 'email'
],
]);

Masked Other Phone | US Phone input

echo MaskedInput::widget([
'name' => 'input-5',
'mask' => ['99-999-9999', '999-999-9999']
]);

2amigos/yii2-date-picker-widget

Improve Your Coding Skills

Menus