Yii2 controller not saving all data

Question

I am working on yii2. I have a form in which there are 3 dropdowns.

use kartik\select2\Select2;
<div class="allow-area-form">

<?php $form = ActiveForm::begin(); ?>
<?=

$form->field($model, 'city_name')
    ->widget(Select2::className(),[
            'data' => \common\models\AllowArea::toCityArrayList(),
            'options' => ['placeholder'=>'Select Area'],
        'pluginOptions' => [
            'allowClear' => true,
            'multiple' => true
        ],
    ]);

?>


<?=

$form->field($model, 'user_id')
    ->widget(Select2::className(),[
        'data' => \app\models\User::toArrayList(),
        'options' => ['placeholder'=>'Select a User'],
        'pluginOptions' => [
            'allowClear' => true

        ],
    ]);

?>



<?=

$form->field($model, 'salesman_code')
    ->widget(Select2::className(),[
            'data' => \common\models\AllowArea::toArrayList(),
            'options' => ['placeholder'=>'Select A Booker'],
        'pluginOptions' => [
            'allowClear' => true,

        ],
    ]);

?>

<div class="form-group">
    <?= Html::submitButton(Yii::t('app', 'Save'), ['class' => 'btn btn-success']) ?>
</div>

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

Interface

In, the above the areas can be selected multiple. Users can only be one and a booker is also one. A single area have multiple sub-areas. So when the main area is selected all of its sub-areas are allotted to a user. So for example, if I select 3 Main-Areas and each of them has 10 sub-areas, a single user is allowed 30 sub-areas. For this purpose, I have implemented the following in my controller

public function actionCreate()
{
    $model = new AllowArea();


    if (isset($_REQUEST['AllowArea'])) {

        try {
            $m = new AllowArea();

            foreach ($_REQUEST['AllowArea']['city_name'] as $a=>$b)
            {

                $sql = "select AreaCode from area where CityNameFull = '$b'";
                $res = Yii::$app->sds->createCommand($sql)->queryAll();

                foreach ($res as $r)
                {
                    $m->load(Yii::$app->request->post());
                    $areacode = $r['AreaCode'];
                    $query = "select AreaNameFull from area where AreaCode = '$areacode'";
                    $rst = Yii::$app->sds->createCommand($query)->queryOne();
                    $areaname = $rst['AreaNameFull'];

                    $m->area_code = $areacode;
                    $m->area_name = $areaname;
                    $m->user_id = $m['user_id'];
                    $m->user_name = AllowArea::idTouser($m['user_id']);
                    $m->salesman_code = $m['salesman_code'];
                    $m->salesman_name = AllowArea::idTousersalesman($m['salesman_code']);
                    $m->city_name =$b;
                    $m->created_by = Yii::$app->user->id;
                    $m->created_at = date('Y-m-d H:i:s');

                    $m->save();

                }

            }

        }catch (Exception $e) {

            return $this->redirect(['index']);
        }
        return $this->redirect(['index']);
    }

    return $this->render('create', [
        'model' => $model

    ]);
}

Data

Below is the database table result of a single selected main area.

So if I select ALLAMA IQBAL TOWN-01(ALL-BLOCK) in the main area, all of the 20 sub-areas should be saved. But my controller only saves the last ZEENAT BLOCK AIT sub-area and leaves the rest of the remaining.

I have also checked the data inside the 3rd foreach loop, it does get the first item but saves the last one so it means that all of the 20 sub-areas are inside the loop.

There must be some mistake that my code is not saving the data and I am stuck in it. How to save all the data?

Any help would be highly appreciated.

Answer 1

You are reusing the same AllowArea model $m so it is adding a single entry to your table and updating it 19 times. Move the $m = new AllowArea() into the loop:

 try {
            foreach ($_REQUEST['AllowArea']['city_name'] as $a=>$b)
            {

                $sql = "select AreaCode from area where CityNameFull = ':cityName'";
                $res = Yii::$app->sds->createCommand($sql, [':cityName' => $b])->queryAll();

                foreach ($res as $r)
                {
                    $m = new AllowArea();
                    ...

As an aside, your code is vulnerable to SQL injection since you aren't validating the value of $b so you should use a prepared statement instead as in the code above or consider using the ActiveRecord functions to fetch the AreaCodes.