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