Mysql Php If Column Is Not Null Update Next Column

[Simber33]

Hello, 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!

[Barand]

First, google "Normalisation".

 

Second, remove those "WHERE username = '$user'" FROM all the case statements and just have it once at the end

[requinix]

What's wrong is you have 20 different columns for the same thing. Why is that?

[PFMaBiSmAd]

This is only 1/5th as bad as the last person who wanted to have a table spread out like this.

[Jessica]

Nth...use a user id, not a username. See: normalization.

[Simber33]

I have a reason to have twenty different columns "for the same thing", there is no need to tidy up my table. I do not exactly understand why it would be more handy to use a user id instead of a username.

[Jessica]

Did you research what normalization is?

[Simber33]

It says that it basically is that you tidy up your table and remove unnecessary aspects of it.

[Christian F.]

This series will explain quite nicely the basics of Normalization, and help you getting started on designing a proper and logical model.

Edited October 20, 2012 by Christian F.

[Simber33]

Thank you very much.

[fenway]

What's wrong is you have 20 different columns for the same thing. Why is that?

Well, for starters, there's no way to ensure that each column contains the same type of information. Then there's the issue of unweildly queries, like the one above. And good luck ever re-partitioning.