What is the proper way to bind_parameters when using UPDATE and CASE WHEN?

I have never done a bind_param() on a key/value table. So, I'm not sure that CASE WHEN can even be applied this way.

The variable column holds all those "cust_xxx" items while their actual values sit in the value column.

Also, I wasn't sure if $who (which is an INT variable) needed bound. Also, I wasn't sure if I should be using sssss or ddddd. They're doubles as entered in the form, but their fields (profile fields on a web forum) are just VARCHAR.

In any case I get the

PHP Fatal error: Uncaught Error: Call to a member function bind_param() on bool

$stmt = $connmember->prepare(" UPDATE smfqg_themes  SET 


                
                `value` =  CASE(   WHEN (`variable` = 'cust_novpil') THEN ?
                                    WHEN (`variable` = 'cust_decpil') THEN ?
                                    WHEN (`variable` = 'cust_janpil') THEN ?
                                    WHEN (`variable` = 'cust_febpil') THEN ?
                                    WHEN (`variable` = 'cust_marpil') THEN ?
                           END)
                                    WHERE id_member = '$who' ");
  
$stmt->bind_param('ddddd', $novpil, $decpil, $janpil, $febpil, $marpil); 
$stmt->execute();

Solution

Use a placeholder for id as well.

CASE doesn't have parentheses around the WHEN clauses. You can also use the shorter CASE column WHEN value syntax.

In case there are rows where variable isn't one of those values, it's a good idea to have an ELSE clause to keep the value of variable the same. Otherwise it will be set to NULL.

$stmt = $connmember->prepare(" UPDATE smfqg_themes  SET 
                `value` = CASE `variable`
                            WHEN 'cust_novpil' THEN ?
                            WHEN 'cust_decpil' THEN ?
                            WHEN 'cust_janpil' THEN ?
                            WHEN 'cust_febpil' THEN ?
                            WHEN 'cust_marpil' THEN ?
                            ELSE `value`
                           END
                WHERE id_member = ? ");
  
$stmt->bind_param('ddddd', $novpil, $decpil, $janpil, $febpil, $marpil, $who); 
$stmt->execute();