CODEWITHSUNDEEP
mysqlselectyii2
gdel
gdel

Reputation: 25

Yii2 Update records resulting from query

I need to update month_no fields of the records resulting from a select query using some variables passed from controller but every field is written with the same value.

These are the variables:

    $id_calc = 27
    $from_y = "2013-05-01"
    $to_y = "2015-11-31"

The table result:

   id   id_paid   year_ref     month_no
    1        26      2012         12
    2        26      2013         12
    4        27      2013         12  (should be 8)
    5        27      2014         12
    6        27      2015         12  (should be 11)

This is model:

 public function CorrectTable($id_calc, $from_y, $to_y)
    {  
        $connection = Yii::$app->db;
        $query = "SELECT * FROM my_table where id_paid='$id_calc'";
        $data = $connection->createCommand($query)->queryAll();
        // calculate no. of month first and last year
        $month_no_begin = (13 - date("m",strtotime($from_y)));   
        $month_no_end = (date("m",strtotime($to_y)));
        // 
        foreach($data as $row) 
        {
          $id = $row['id'];
          // calculate number of month per year
          if ($row['year_ref'] = date("Y",strtotime($from_y))) 
          {
            $month_no = $month_no_begin;
          } elseif  ($row['year_ref'] = date("Y",strtotime($to_y))) 
          {
            $month_no = $month_no_end;
          } else
          {
            $month_no = 12;
          }
            Yii::$app->db->createCommand()
            ->update('my_table', ['month_no' => $month_no], ['id' => $id])
            ->execute();
        }

I tried to put the above code in controller with the same result.

Upvotes: 2

Views: 3177

Answers (2)

Paul
Paul

Reputation: 1677

A careless mistake once I had made before. You should use == or === in the if statement to test equal condition, instead of =

public function CorrectTable($id_calc, $from_y, $to_y)
{  
    $connection = Yii::$app->db;
    $query = "SELECT * FROM my_table where id_paid='$id_calc'";
    $data = $connection->createCommand($query)->queryAll();
    // calculate no. of month first and last year
    $month_no_begin = (13 - date("m",strtotime($from_y)));   
    $month_no_end = (date("m",strtotime($to_y)));
    // 
    foreach($data as $row) 
    {
        $id = $row['id'];

        // use `==` instead of `=`
        if ($row['year_ref'] == date("Y",strtotime($from_y))) 
        {
            $month_no = $month_no_begin;
        }

        // use `==` instead of `=`
        elseif ($row['year_ref'] == date("Y",strtotime($to_y))) 
        {
            $month_no = $month_no_end;
        }
        else
        {
            $month_no = 12;
        }

        Yii::$app->db->createCommand()
            ->update('my_table', ['month_no' => $month_no], ['id' => $id])
            ->execute();
    }
}

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

our if ... esleif ... else sequence seems not logig as you done the else if is never executed

so you should change the condition this way the e

 foreach($data as $row) 
    {
        $id = $row['id'];

        // assigne default value
        $month_no = 12;

        // check for month_begin
        if ($row['year_ref'] == date("Y",strtotime($from_y))) 
        {
            $month_no = $month_no_begin;
        }

        //check for month_end    
        if ($row['year_ref'] == date("Y",strtotime($to_y))) 
        {
            $month_no = $month_no_end;
        }


        Yii::$app->db->createCommand()
            ->update('my_table', ['month_no' => $month_no], ['id' => $id])
            ->execute();
    }

Upvotes: 1

Related Questions