-2

I recently started working with PHP and MySQL and I have written a code to insert a variable into a MySQL column and if this column is already filled (Not NULL) that it would then continue on and try to update the next column. Here is my code:

$result=mysql_query("
UPDATE user_info SET   
 Amount20 = ( case when ( Amount19 is not null and Amount20 is null ) then ‘$amount’ WHERE Username ='$user' else Amount20 end )

,   Amount19 = ( case when ( Amount18 is not null and Amount19 is null ) then ‘$amount’ WHERE Username ='$user' else Amount19 end )

,   Amount18 = ( case when ( Amount17 is not null and Amount 18 is null ) then ‘$amount’ WHERE Username ='$user' else Amount18 end )

,   Amount17 = ( case when ( Amount16 is not null and Amount17 is null ) then ‘$amount’ WHERE Username ='$user' else Amount17 end )

,   Amount16 = ( case when ( Amount15 is not null and Amount16 is null ) then ‘$amount’ WHERE Username ='$user' else Amount16 end )

,   Amount15 = ( case when ( Amount14 is not null and Amount15 is null ) then ‘$amount’ WHERE Username ='$user' else Amount15 end )

,   Amount14 = ( case when ( Amount13 is not null and Amount14 is null ) then ‘$amount’ WHERE Username ='$user' else Amount14 end )

,   Amount13 = ( case when ( Amount12 is not null and Amount13 is null ) then ‘$amount’ WHERE Username ='$user' else Amount13 end )

,   Amount12 = ( case when ( Amount11 is not null and Amount12 is null ) then ‘$amount’ WHERE Username ='$user' else Amount12 end )

,   Amount11 = ( case when ( Amount10 is not null and Amount11 is null ) then ‘$amount’ WHERE Username ='$user'else Amount11 end )

,   Amount10 = ( case when ( Amount9 is not null and Amount10 is null ) then ‘$amount’ WHERE Username ='$user' else Amount10 end )

,   Amount9 = ( case when ( Amount8 is not null and Amount9 is null ) then ‘$amount’ WHERE Username ='$user' else Amount9 end )

,   Amount8 = ( case when ( Amount7 is not null and Amount8 is null ) then ‘$amount’ WHERE Username ='$user' else Amount8 end )

,   Amount7 = ( case when ( Amount6 is not null and Amount7 is null ) then ‘$amount’ WHERE Username ='$user' else Amount7 end )

,   Amount6 = ( case when ( Amount5 is not null and Amount6 is null ) then ‘$amount’ WHERE Username ='$user' else Amount6 end )

,   Amount5 = ( case when ( Amount4 is not null and Amount5 is null ) then ‘$amount’ WHERE Username ='$user' else Amount5 end )   

,   Amount4 = ( case when ( Amount3 is not null and Amount4 is null ) then ‘$amount’ WHERE Username ='$user' else Amount4 end )

,   Amount3 = ( case when ( Amount2 is not null and Amount3 is null ) then ‘$amount’ WHERE Username ='$user' else Amount3 end )  

, Amount2 = ( case when ( Amount1 is not null and Amount2 is null ) then ‘$amount’ WHERE Username ='$user' else Amount2 end )  

, Amount1 = ( case when ( Amount1 is null ) then '$amount' else Amount1 WHERE Username ='$user' end )  
");

I have no idea what is wrong, any help would be great! Note: I have a reason to have 20 different columns, please do not refer me to "normalization".

Alessandro Minoccheri
  • 35,521
  • 22
  • 122
  • 171
user1760791
  • 403
  • 3
  • 12

1 Answers1

0

I modified your query as follows :

$result=mysql_query("
UPDATE user_info SET   
Amount20 = ( case when ( Amount19 is not null and Amount20 is null ) then ‘$amount’    else Amount20 end )
,Amount19 = ( case when ( Amount18 is not null and Amount19 is null ) then ‘$amount’ else Amount19 end )
,Amount18 = ( case when ( Amount17 is not null and Amount 18 is null ) then ‘$amount’ else Amount18 end )
,Amount17 = ( case when ( Amount16 is not null and Amount17 is null ) then ‘$amount’ else Amount17 end )
,Amount16 = ( case when ( Amount15 is not null and Amount16 is null ) then ‘$amount’ else Amount16 end )
,Amount15 = ( case when ( Amount14 is not null and Amount15 is null ) then ‘$amount’ else Amount15 end )
,Amount14 = ( case when ( Amount13 is not null and Amount14 is null ) then ‘$amount’ else Amount14 end )
,Amount13 = ( case when ( Amount12 is not null and Amount13 is null ) then ‘$amount’ else Amount13 end )
,Amount12 = ( case when ( Amount11 is not null and Amount12 is null ) then ‘$amount’ else Amount12 end )
,Amount11 = ( case when ( Amount10 is not null and Amount11 is null ) then ‘$amount’ else Amount11 end )
,Amount10 = ( case when ( Amount9 is not null and Amount10 is null ) then ‘$amount’ else Amount10 end )
,Amount9 = ( case when ( Amount8 is not null and Amount9 is null ) then ‘$amount’ else Amount9 end )
,Amount8 = ( case when ( Amount7 is not null and Amount8 is null ) then ‘$amount’ else Amount8 end )
,Amount7 = ( case when ( Amount6 is not null and Amount7 is null ) then ‘$amount’ else Amount7 end )
,Amount6 = ( case when ( Amount5 is not null and Amount6 is null ) then ‘$amount’ else Amount6 end )
,Amount5 = ( case when ( Amount4 is not null and Amount5 is null ) then ‘$amount’ else Amount5 end )   
,Amount4 = ( case when ( Amount3 is not null and Amount4 is null ) then ‘$amount’ else Amount4 end )
,Amount3 = ( case when ( Amount2 is not null and Amount3 is null ) then ‘$amount’ else Amount3 end )  
,Amount2 = ( case when ( Amount1 is not null and Amount2 is null ) then ‘$amount’ else Amount2 end )  
,Amount1 = ( case when ( Amount1 is null ) then '$amount' else Amount1 end )
 WHERE Username ='$user'");
Ertunç
  • 819
  • 1
  • 9
  • 21