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

Question

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();

You should start by writing the query you want with literal values, then replace the values with placeholders. Your query isn't valid SQL syntax. What is `value = ? CASE ...` supposed to mean? You can't use both `?` and a `CASE` expression as the value to assign to the column. — Barmar, Sep 20 '22 at 21:37
Yes, you need to bind `$who` -- all variables should be bound. — Barmar, Sep 20 '22 at 21:39
Whoa, that was a typo. I removed the = ? by CASE, sorry. I'm not sure I am capable of writing the query with literals. The key/value queries have given me trouble when there is more than just one `value` happening, and even then I've not done one with binding. — General Admission, Sep 20 '22 at 21:50
I wondered if I could split them up into individual queries separated by semi-colon, but I don't know how the $stmt's would work with that. — General Admission, Sep 20 '22 at 21:54
If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection. Here are some good video tutorials https://youtu.be/2eebptXfEvw & https://www.youtube.com/watch?v=sVbEyFZKgqk&list=PLr3d3QYzkw2xabQRUpcZ_IBk9W50M9pe- — Dharman, Sep 20 '22 at 22:05
Your main problem is that you're doing `$stmt = $conmember->prepare(...)`. That's not how you initialise a statement. You do so via `stmt_init()` method [as shown in the documentation examples](https://www.php.net/manual/en/mysqli-stmt.prepare.php#example-1502) — Rylee, Sep 21 '22 at 04:13
@Rylee That's not true. See https://www.php.net/manual/en/mysqli.prepare.php — Barmar, Sep 21 '22 at 14:17
New here, so can someone say if the question was unique enough? There is still a blue box up there suggesting I didn't refine it enough.? — General Admission, Sep 21 '22 at 14:40
@Barmar it's just as true - the link you posted was for `mysqli::prepare`, the one I posted was for `mysqli_stmt::prepare`. Based on the error received here we can easily determine that the method I referenced was correct as it returns a boolean value. I know the one you refer to does on *failure* but there's no obvious reason to believe (in this code) that it failed on the prepare stage. — Rylee, Sep 22 '22 at 22:08
@Rylee If it doesn't fail it returns a `mysql_stmt` object. So you won't get the "member function ... on bool" error when it's successful, because the method is valid. Except for the lack of error checking, there's no problem with the way the OP wrote it. — Barmar, Sep 22 '22 at 22:13
@Barmar exactly, if it *doesn't* fail on the call to `prepare`, they wouldn't be getting this error. You've just repeated what I wrote. We have no idea what `$connmember` is. We only know that `$stmt` is a boolean value (from the error). — Rylee, Sep 22 '22 at 22:19

Barmar · Accepted Answer · 2022-09-21T14:15:27.350

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();

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

1 Answers1