how to use a specific UPDATE query if existing row is empty mysql

Question

I have searched far and wide for a specific case were i could get this answer but what i have seems to me like it should work. but it doesn't.

I basically want to concatenate data into a row in my database. Put simply if there is no existing data it injects the filenames variable into the row.

If there is existing data in the row i need to append a "," to the start so that i can break the filenames out later with php on my page.

Here's what i have that doesn't seem to work.

  $query  = "UPDATE plan 
             SET plan_files = if(plan_files is null,concat(plan_files,
                   '{$filenames}'),concat(plan_files, ',{$filenames}')) 
             WHERE plan_id='{$planid}'";

Bilal Rao · Accepted Answer · 2014-11-11 06:55:43Z

3

try this

$query = "update plan  SET plan_files = (case when plan_files IS NULL then '".$filenames."' else concat(plan_files , ',".$filenames."') end ) where plan_id =". $planid;

Please make sure that $filenames is string and $planid is int.

answered Nov 11, 2014 at 6:55

Bilal Rao

1548 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

22Ryann Over a year ago

Tryed the query and it added the "," even tho there was no data inside the row.

Bilal Rao Over a year ago

plz check the data type of the column plan_files. it may not be allowed null, or default null. please do these two thing. then i am sure it will work.

22Ryann Over a year ago

Thanks Bilal Works a treat, wasn't savvy on that setting on my table row. Thanks

Sharry India · Accepted Answer · 2014-11-11 07:12:34Z

2

You can do like this :

    "UPDATE `plan` SET 
     plan_files=CASE
     WHEN (plan_files='') THEN  '".$filenames."'
     ELSE concat(name,',".$filenames."')
     END
     WHERE plan_id='".$planid."'"

answered Nov 11, 2014 at 7:12

Sharry India

3512 silver badges9 bronze badges

Collectives™ on Stack Overflow

how to use a specific UPDATE query if existing row is empty mysql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related