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

811

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, ColumnID_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?

787

Answer

Solution:

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.

827

Answer

Solution:

condition 3:

  • a. bothLAMA andESTIMASI are null.
  • b.LAMA is null
  • c.ESTIMASI is null

If you want to update all rows in the table, based on the values ofLAMA andESTIMASI, 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 theID_STATUS column back to theID_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 inLAMA and/orESTIMASI. 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 aWHERE clause that excludes rows whereLAMA is null orESTIMASI 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

IfLAMA andESTIMASI are defined asNOT 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`');

People are also looking for solutions to the problem: How can I detect a visitor's browser using PHP?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.