CODEWITHSUNDEEP
phpmysqlsql
tooba
tooba

Reputation: 63

update multiple columns with condition

I have 4 columns. I am trying to update only that values of the columns which is like(equal) to the value come in loop.

for ($a=0; $a<$i ; $a++) {  
            $sql= "update table1 set 
            col1= CASE when col1 like '$distval[$a]' then col1='$arr[$a]' else col1 end,
            col2=CASE when col2 like '$distval[$a]' then col2='$arr[$a]' else col2 end,
            col3=CASE when col3 like '$distval[$a]' then col3='$arr[$a]' else col3 end, 
            col4=CASE when col4 like '$distval[$a]' then col4='$arr[$a]' else col4 end";
            mysqli_query($conn, $sql);
        }

Let me explain the code:

$distval: is the array which contains the distinct values of all the four columns which i want to update with values entered by the users.

$arr: is another array of the text fields of same number as '$distval'. Now the value enter in the first text field should update all the values in table like(equals to) first value of '$distvalue'.

The code doesn't update anything without any error.

Upvotes: 2

Views: 135

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

remove the colX='$arr[$a]' inside the then you need only '$arr[$a]' 

$sql= "update table1 set 
          col1= CASE when col1 like '%". $distval[$a]. "%' then '$arr[$a]' else col1 end,
          col2= CASE when col2 like '%". $distval[$a]. "%' then '$arr[$a]' else col2 end,
          col3= CASE when col3 like '%". $distval[$a]. "%' then '$arr[$a]' else col3 end, 
          col4= CASE when col4 like '%". $distval[$a]. "%' then '$arr[$a]' else col4 end";

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

The = does not make sense for the then. The correct case would look like:

update table1
   set col1 = (CASE when col1 like '$distval[$a]' then '$arr[$a]' else col1 end),
       col2 = (CASE when col2 like '$distval[$a]' then '$arr[$a]' else col2 end),
       col3 = (CASE when col3 like '$distval[$a]' then '$arr[$a]' else col3 end), 
       col4 = (CASE when col4 like '$distval[$a]' then '$arr[$a]' else col4 end);

I would strongly encourage you to use parameterized queries, rather than dumping the values directly in the query string.

Upvotes: 0

Related Questions