Convert SQL UPDATE statement to php with if else condition (codeigniter)

Question

I want to make php code with SQL Update Statement in Codeigniter.

If I execution code in Codeigniter, the data in database will be updated too.

I want to update one column (ID_STATUS) but with several condition.

The connection column 'ID_STATUS' with column 'lama' and 'estimasi'

My table name is "pelayanan".

ID_STATUS is PK from table "status".

So, Column ID_STATUS in table "pelayanan" is foreign key from table "status".

I tried with this query, but, It isn't if else condition yet.

condition 1 :

UPDATE `dbhpl`.`pelayanan` 
SET `pelayanan`.`ID_STATUS` = '1' 
WHERE `pelayanan`.`LAMA` <> `pelayanan`.`ESTIMASI`;

condition 2:

UPDATE `dbhpl`.`pelayanan` 
SET `pelayanan`.`ID_STATUS` = '2' 
WHERE `pelayanan`.`LAMA` = `pelayanan`.`ESTIMASI`;

That is the query on mysql. But I want to convert that query to php code (Codeigniter).

How come It will be?

shankar kumar · Accepted Answer · 2015-07-07 05:26:41Z

1

your first query is

UPDATE `dbhpl`.`pelayanan` 
SET `pelayanan`.`ID_STATUS` = '1' 
WHERE `pelayanan`.`LAMA` <> `pelayanan`.`ESTIMASI`;

convert it as follows:

$update_data=array('ID_STATUS'=>'1');
$this->db->where('LAMA <>','ESTIMASI');
$this->db->update('pelayanan',$update_data);

your second query is

UPDATE `dbhpl`.`pelayanan` 
SET `pelayanan`.`ID_STATUS` = '2' 
WHERE `pelayanan`.`LAMA` = `pelayanan`.`ESTIMASI`;

convert it as follows:

$update_data=array('ID_STATUS'=>'2');
$this->db->where('LAMA','ESTIMASI');
$this->db->update('pelayanan',$update_data);

I have remove database name. database name will be selected in connection and and did required to mentioned it here.

answered Jul 7, 2015 at 5:26

shankar kumar

6485 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

spencer7593 · Accepted Answer · 2015-07-03 04:41:41Z

1

condition 3:

a. both LAMA and ESTIMASI are null.
b. LAMA is null
c. ESTIMASI is null

If you want to update all rows in the table, based on the values of LAMA and ESTIMASI, you could do that in one fell swoop with one UPDATE statement.

  UPDATE `dbhpl`.`pelayanan` p 
     SET p.`ID_STATUS`
         = CASE 
             WHEN p.`LAMA`  = p.`ESTIMASI` THEN '2'
             WHEN p.`LAMA` <> p.`ESTIMASI` THEN '1'
             WHEN p.`LAMA` IS NULL AND p.`ESTIMASI` IS NULL THEN p.`ID_STATUS`
             WHEN p.`LAMA` IS NULL THEN p.`ID_STATUS`
             ELSE p.`ID_STATUS`
           END

Note that assigning the current value of the ID_STATUS column back to the ID_STATUS column results in "no update".

Since the last two WHEN conditions return the same values as the ELSE, those could be removed. These were included just to illustrate possible handling of condition 3.

One small difference with this vs. the original is that it will attempt tto update every row in the table, including rows that have a NULL value in LAMA and/or ESTIMASI. That means any UPDATE triggers will be fired for those rows. To get exactly the same result as the original, you'd need to include a WHERE clause that excludes rows where LAMA is null or ESTIMASI is null. For example:

   WHERE p.`LAMA` IS NOT NULL
     AND p.`ESTIMASI` IS NOT NULL

As far as how to accomplish this same thing in PHP, someone else may be able to answer that. Personally, I'd just do it one SQL operation.

The ANSI-standard syntax is a bit verbose. A MySQL specific version that accomplishes the same thing is a bit shorter:

  UPDATE `dbhpl`.`pelayanan` p 
     SET p.`ID_STATUS` = IFNULL((p.`LAMA`=p.`ESTIMASI`)+1,p.`ID_STATUS`)

FOLLOWUP

If LAMA and ESTIMASI are defined as NOT NULL, then you wouldn't have to deal with condition 3. (In the more general case, we don't necessarily have that guarantee, so I think it's better pattern to account for those conditions, even if they won't ever happen in our particular case.

For CodeIgniter ActiveRecord, you'd could try something like this:

 $this->db
   ->set('ID_STATUS', 'IFNULL((`LAMA`=`ESTIMASI`)+1,`ID_STATUS`)', FALSE)
   ->update('`dbhpl`.`pelayanan`');

edited Jul 3, 2015 at 4:41

answered Jul 3, 2015 at 3:57

spencer7593

109k15 gold badges122 silver badges148 bronze badges

3 Comments

user5072610 Over a year ago

LAMA and ESTIMASI is not NULL. I want to make that query become php code (Codeigniter)

user5072610 Over a year ago

when I execute that query in sql the result is ERROR. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BE = p.ESTIMASI THEN '2' WHEN p.LAMA <> p.ESTIMASI THEN '1' ' at line 4

spencer7593 Over a year ago

Curious, I don't see any occurrence of the string BE in the example MySQL query. I can't tell where that string is coming from.

Collectives™ on Stack Overflow

Convert SQL UPDATE statement to php with if else condition (codeigniter)

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related