CODEWITHSUNDEEP
mysqlcodeignitervariables
scoohh
scoohh

Reputation: 375

codeigniter and mysql user-defined variables not working

CodeIgniter Version: 2.1.3
MySQL Version: 5.5.30
MySQL Engine: MyISAM

Query:

$query = "INSERT INTO new_table
      (
        user_id,
        cut_off_pay,
        total_days,
        rate_per_day,
        rate_per_hour,
      )
      (
        SELECT
          u.id,
          @cut_off_pay := (u.current_salary / 2) ,
          @total_days := 10,
          @rate_per_day := (@cut_off_pay / @total_days),
          @rate_per_hour := (@rate_per_day / 8)
        FROM attendance a
        LEFT JOIN users u
          ON a.user_id = u.id
        WHERE a.user_id = u.id
        GROUP BY a.user_id
      )";              
$this->db->query($query);

The user-defined variables (@cut_off_pay, @total_days, etc..) are not working, it returns 0/NULL values

Upvotes: 0

Views: 229

Answers (1)

peterm
peterm

Reputation: 92805

IMHO

  1. You don't need any user variables for this
  2. You don't need WHERE clause in your case that duplicates a join condition
  3. And you don't even need to join users with attendance table since you don't use any values from it and a choice of a LEFT JOIN and attendance table being on the left of it is highly questionable

That being said either do

$query = "INSERT INTO new_table
          (
            user_id,
            cut_off_pay,
            total_days,
            rate_per_day,
            rate_per_hour
          )
          SELECT u.id,
                 u.current_salary / 2          cut_off_pay,
                 10                            total_days,
                 u.current_salary / 2 / 10     rate_per_day,
                 u.current_salary / 2 / 10 / 8 rate_per_hour
            FROM attendance a LEFT JOIN users u
              ON a.user_id = u.id
           GROUP BY a.user_id";

You don't even need to give aliases to derived columns in your select since you insert them, but that just improves readability and you can always use that select on its own e.g. for testing purposes

or simply

$query = "INSERT INTO new_table
          (
            user_id,
            cut_off_pay,
            total_days,
            rate_per_day,
            rate_per_hour
          )
          SELECT id,
                 current_salary / 2          cut_off_pay,
                 10                            total_days,
                 current_salary / 2 / 10     rate_per_day,
                 current_salary / 2 / 10 / 8 rate_per_hour
            FROM users";

Upvotes: 1

Related Questions