how do i make cases in mysql to update fields only if empty, if not empty, go to next field?

Question

i'm searching around but can't decipher the scenarios people are trying to do and adapt to my scenario.

there is one stackoverflow q&a that is close to my question, but the answer is weird and i don't know if the dashes in the answer are supposed to be there. plus i'm not sure how the names before and after the questionmark work and what names in my scenario should go where, either before or after the question marks.

so i need to update 2 fields when submitting a form. if the 2 fields have data, don't use those 2 fields, use another 2 fields, but if those other 2 fields are filled, then use a 3rd set of 2 fields, and so forth.

i know this is a bad technique, and i should do normalization, but this is just a simple app where at the most 2 people might be trying to submit said form at the same time, and the chances of that happening are 100,000 to 1, but still i want to prevent it on the off chance it might happen.

my table is like this:

table name is jobs and these are the fields

 id
 name
 path
 jobid
 note1
 note1user
 note2
 note2user
 note3
 note3user
 note4
 note4user
 note5
 note5user
 note6
 note6user

so if note1 and note1user is not null or empty, use note2 and note2user. if note2 and note2user is not null or empty, use note3 and note3user. and so forth. if note6 and note6user has data, it doesn't matter because the form doesn't show up for submitting.

the code i saw somewhere else goes like this, but wasn't sure how to adapt it to my scenario:

update table_name set  
     col1 = ( case when col1 is null then ? else col1 end )  
    , col2 = ( case when col2 is null then ? else col2 end )  
--  , col3 = ...
;

i'll try and guess something. how does the following chunk of code look? i took off those dashes, cause i didn't like how it looked in there. not even sure the semicolon is needed. and a final thought is that this is for a one field at a time scenario. how do i do 2 fields at a time? in other words, how do i incorporate note1user through note6user?

update jobs set  
    note1 = ( case when note1 is null then ? else note1 end ),
    note2 = ( case when note2 is null then ? else note2 end ),
    note3 = ( case when note3 is null then ? else note3 end ),
    note4 = ( case when note4 is null then ? else note4 end ),
    note5 = ( case when note5 is null then ? else note5 end )

thanks if you provide any tips and tricks and snippets.

UPDATE

this is to Barmar.

this doesn't create errors, but nothing gets written to database. don't worry that it says update files. that's the table i'm trying to write to. i messed up my question when i said it was jobs. also, the following does the same whether i put all the notes together and note users together, or i put one of each going down.

UPDATE files SET 
note_6 = (CASE WHEN note_6 IS NULL AND note_5 IS NOT NULL THEN '$note_6' ELSE note_6 END), 
note_6_user = (CASE WHEN note_6_user IS NULL AND note_5_user IS NOT NULL THEN '$note_6_user' ELSE note_6_user END), 
note_5 = (CASE WHEN note_5 IS NULL AND note_4 IS NOT NULL THEN '$note_5' ELSE note_5 END), 
note_5_user = (CASE WHEN note_5_user IS NULL AND note_4_user IS NOT NULL THEN '$note_5_user' ELSE note_5_user END), 
note_4 = (CASE WHEN note_4 IS NULL AND note_3 IS NOT NULL THEN '$note_4' ELSE note_4 END), 
note_4_user = (CASE WHEN note_4_user IS NULL AND note_3_user IS NOT NULL THEN '$note_4_user' ELSE note_4_user END), 
note_3 = (CASE WHEN note_3 IS NULL AND note_2 IS NOT NULL THEN '$note_3' ELSE note_3 END), 
note_3_user = (CASE WHEN note_3_user IS NULL AND note_2_user IS NOT NULL THEN '$note_3_user' ELSE note_3_user END), 
note_2 = (CASE WHEN note_2 IS NULL AND note_1 IS NOT NULL THEN '$note_2' ELSE note_2 END), 
note_2_user = (CASE WHEN note_2_user IS NULL AND note_1_user IS NOT NULL THEN '$note_2_user' ELSE note_2_user END), 
note_1 = (CASE WHEN note_1 IS NULL THEN '$note_1' ELSE note_1 END), 
note_1_user = (CASE WHEN note_1_user IS NULL THEN '$note_1_user' ELSE note_1_user END) 
WHERE id='$id'

UPDATE

the final thing that allowed it to work is making the fields in the database have a default of NULL. i couldn't get it to work with empty.

The basic problem with that code is that it will update ALL the non-null fields, not just the first one. I don't think that's what you want, is it? — Barmar, Oct 17 '13 at 18:28
http://stackoverflow.com/questions/11176410/update-mysql-field-if-field-is-not-empty-go-to-next-one — leoarce, Oct 17 '13 at 18:30
You incorporrate `note#user` the same way, with `note1user = (case when note1user is null then ? else note1user end),` and so on. — Barmar, Oct 17 '13 at 18:30
Look at the "Update" part of that answer. It shows how to do it right, by testing multiple fields in reverse order. — Barmar, Oct 17 '13 at 18:31
I know you said this is just a simple app, but database normalization has less to do with performance than it does with avoiding exactly this sort of situation. You may make this work, but you'll make yourself crazy doing it. — Ian McLaird, Oct 17 '13 at 18:35

Barmar · Accepted Answer · 2013-10-17T19:13:07.770

2

Update jobs SET
    note_6 = (CASE WHEN note_6 IS NULL AND note_5 IS NOT NULL THEN '$note' ELSE note_6 END),
    note_5 = (CASE WHEN note_5 IS NULL AND note_4 IS NOT NULL THEN '$note' ELSE note_5 END),
    note_4 = (CASE WHEN note_4 IS NULL AND note_3 IS NOT NULL THEN '$note' ELSE note_4 END),
    note_3 = (CASE WHEN note_3 IS NULL AND note_2 IS NOT NULL THEN '$note' ELSE note_3 END),
    note_2 = (CASE WHEN note_2 IS NULL AND note_1 IS NOT NULL THEN '$note' ELSE note_2 END),
    note_1 = (CASE WHEN note_1 IS NULL THEN '$note' ELSE note_1 END),
    note_6_user = (CASE WHEN note_6_user IS NULL AND note_5_user IS NOT NULL THEN '$noteuser' ELSE note_6_user END),
    note_5_user = (CASE WHEN note_5_user IS NULL AND note_4_user IS NOT NULL THEN '$noteuser' ELSE note_5_user END),
    note_4_user = (CASE WHEN note_4_user IS NULL AND note_3_user IS NOT NULL THEN '$noteuser' ELSE note_4_user END),
    note_3_user = (CASE WHEN note_3_user IS NULL AND note_2_user IS NOT NULL THEN '$noteuser' ELSE note_3_user END),
    note_2_user = (CASE WHEN note_2_user IS NULL AND note_1_user IS NOT NULL THEN '$noteuser' ELSE note_2_user END),
    note_1_user = (CASE WHEN note_1_user IS NULL THEN '$noteuser' ELSE note_1_user END)
WHERE ...

edited Oct 17 '13 at 19:13

answered Oct 17 '13 at 18:38

Barmar

741,623
53
500
612

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 '? ELSE note_6 END), note_5 = (CASE WHEN note_5 IS NULL AND note_4 IS NOT NUL' at line 2 – leoarce Oct 17 '13 at 18:55
my real field names had underscores, which i added. i just didn't write all those underscores in my question. so the underscores is not what's causing error. – leoarce Oct 17 '13 at 18:56
The `?` assumes you're using prepared statements to subtitute the actual values into the query. – Barmar Oct 17 '13 at 18:59
this was my original update statement. on the right side of each equal sign are variable names with dollar signs. am i supposed to add those dollar signs in your chunk of code somewhere? – leoarce Oct 17 '13 at 19:07
UPDATE files SET note_1='$note_1', note_1_user='$note_1_user', note_2='$note_2', note_2_user='$note_2_user', note_3='$note_3', note_3_user='$note_3_user', note_4='$note_4', note_4_user='$note_4_user', note_5='$note_5', note_5_user='$note_5_user', note_6='$note_6', note_6_user='$note_6_user' WHERE id='$id' – leoarce Oct 17 '13 at 19:07
in your code, note1user has an END but note1 doesn't have an END. could that be not letting it work? – leoarce Oct 17 '13 at 19:12
If you're using variable interpolation, then you need to replace all the `?` with the appropriate variables. – Barmar Oct 17 '13 at 19:14
I've updated the answer to use variable interpolation instead of a placeholder. – Barmar Oct 17 '13 at 19:16
i updated my question to show full version of code i'm using. it doesn't write to database. – leoarce Oct 17 '13 at 19:40
What are `$note_6`, `$note_5`, etc.? I thought you just had two input fields, and you needed to put them in the appropriate columns of the database. – Barmar Oct 17 '13 at 19:42
ooooh. so i need to change my form and php to accomodate this technique. i was dynamically changing the fields depending on what the database had in it. so i can change that to do it with 2 fields instead of 12 fields. ok let me try that. – leoarce Oct 17 '13 at 19:44
Right. If your form has all 12 fields, you could just assign them directly to the database columns, you don't need to do anything fancy. – Barmar Oct 17 '13 at 19:46
well, the database has the 12 fields, but the page should only have 2 fields. one note should only be written at a time by a user, and it has to be in order from 1 through 6. by the way i just changed it and the database doesn't get written to. – leoarce Oct 17 '13 at 19:56
I only put checks for null in the query. If your existing database has empty strings instead of null, you need to check for that as well. – Barmar Oct 17 '13 at 19:58
i just need to check for empty then. not null. how is that done? change IS NULL and IS NOT NULL to == '' and !== '' ? – leoarce Oct 17 '13 at 20:05
mysql error is now this: "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 '== '' AND note_5 !== '' THEN 'text to be written' ELSE note_6 END), note_6_user ' at line 1" – leoarce Oct 17 '13 at 20:12

Jose Samaniego · Answer 2 · 2013-10-17T18:48:37.417

0

i think you need to use empty php function

note that this $field can be from database or from textboxes

$field1="1";
$field2="2";
$field3="3";
$field4="4";
$field5="5";
$field6="6";


if( empty($field1) and empty($field2) )
{
//insert into database field 3 and 4
}
else if( empty($field3) and empty($field4) )
{
//insert into database field 5 and 6
}else{
//insert into database field 1 and 2
}

i think this is what you mean?

edited Oct 17 '13 at 18:48

answered Oct 17 '13 at 18:34

Jose Samaniego

110
9

cool, but where does that go? wrap the mysql_query line or wrap the form elements? – leoarce Oct 17 '13 at 18:39
I don't think you understand the question. – Barmar Oct 17 '13 at 18:39
im just giving him tools/function he can use not the exact code for his problem cause we only have bits of the system and doesnt understand the whole program – Jose Samaniego Oct 17 '13 at 18:50
But you're checking the wrong thing for being empty. He wants to check the previous contents of the row, not the input data from the user. – Barmar Oct 17 '13 at 18:59

score 0 · Answer 3 · answered Oct 17 '13 at 18:37

0

If it is a small app and you aren't worried about optimizing the number of database calls, you can just loop over the POST/GET array in PHP and do your updates based on the array key.

ex.

foreach ($notes as $k => $v) {
    if ($v != "") {
        //Do update where your field is equal to the array key
    }
}

answered Oct 17 '13 at 18:37

Ethan

2,754
1
21
34

He's not looping over input, the issue is which column in the table to store the input in. – Barmar Oct 17 '13 at 18:39

how do i make cases in mysql to update fields only if empty, if not empty, go to next field?

3 Answers3