MySQL query with PHP and WHERE specification

Question

With this update query if the id is in chk_list then I set checked=1. I want if the id isn't in the list to set chk_list=0

$update = "UPDATE data SET checked=1 WHERE id IN($chk_list)";

$qry = $db->query($update);

Is there any simple way after WHERE to set if?

Can't you just do `NOT IN`? https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_not-in — user3783243, Sep 24 '18 at 18:19
Either of the 2 answers will work. I'm curious though, why do you want to update every record on a table? I've never seen a scenario like this — dustytrash, Sep 24 '18 at 18:20
Hi Raul, could you clarify your question? Perhaps add some context as to what you're trying to achieve on your table. — Lex, Sep 24 '18 at 23:09

Madhur Bhaiya · Accepted Answer · 2018-09-24T18:34:02.720

2

You can use IF() function to determine value to be set. Do the following:

$update = "UPDATE data SET checked = (IF(id IN ($chk_list), 1, 0))";
$qry = $db->query($update);

Note: Please use Prepared Statements to prevent SQL injection related issues

edited Sep 24 '18 at 18:34

answered Sep 24 '18 at 18:13

Madhur Bhaiya

28,155
10
49
57

i want when is in list set 1 and where is not set 0 – raul paulsen Sep 24 '18 at 18:14
@raulpaulsen please check updated answer. Use if function – Madhur Bhaiya Sep 24 '18 at 18:16
if(!$qry) { echo "Updation Failed";} – raul paulsen Sep 24 '18 at 18:24
is this correct?$update = "UPDATE data SET checked = (IF(id IN $chk_list, 1, 0))"; or is missing something? – raul paulsen Sep 24 '18 at 18:24
please echo the $update before executing it and see what you are getting. Try to run the raw query directly using phpmyadmin or mysql workbench and check its output – Madhur Bhaiya Sep 24 '18 at 18:27

score 1 · Answer 2 · answered Sep 24 '18 at 18:19

If you want to explicitly set checked as 1 or 0, then the if should be in the set area Notice that its very dangerous to update all of the table to the extent that MySQL by default prevents this from happening, ans you should set safe updates to 0

UPDATE data SET checked = IF(id IN ($chk_list), 1,0)

score 1 · Answer 3 · answered Sep 24 '18 at 18:19

1

What about this with CASE? Not sure pretty neat or not but seems it'll work :)

UPDATE data SET checked= 
CASE
  WHEN id IN ($chk_list) THEN 1
  WHEN id NOT IN ($chk_list) THEN 0
END

answered Sep 24 '18 at 18:19

A l w a y s S u n n y

36,497
8
60
103

it's ok to use it in php code?it works great but i dont know if it is proper – raul paulsen Sep 24 '18 at 18:31
@raulpaulsen glad it works for you. Best of luck buddy :) Happy coding – A l w a y s S u n n y Sep 24 '18 at 18:32
@raulpaulsen I don't see any problem on it if you use it with php – A l w a y s S u n n y Sep 24 '18 at 18:33

MySQL query with PHP and WHERE specification

3 Answers3