CODEWITHSUNDEEP
mysql
Vulcan
Vulcan

Reputation: 3

Update query to switch values in database

I need help with updating database. My PHP code is shown below. The $sql isn´t working, I want to set user_blocked to Yes if there is No and set it to No if there is Yes. Sorry for my English and sorry for my code but I'm a beginner.

<?php 
require('dbconnect.php');

if(isset($_POST['block_user'])) {

$user_id = $_POST['user_id_block'];
$sql = "UPDATE users SET user_blocked='No' WHEN user_blocked='Yes' AND 
user_blocked='Yes' WHEN user_blocked='No' WHERE user_id='$user_id'";
$retval = mysql_query($sql, $conn);

if(! $retval) {
    die('Could not block/unblock user: ' . mysql_error());
} else {
    header('Refresh:0');
}
mysql_close($conn);

}
?>

The error is :

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 'WHEN user_blocked='Yes' AND user_blocked='Yes' WHEN user_blocked='No' WHERE user' at line 1.

I don´t know what is the right syntax.

$sql = "UPDATE users SET user_blocked='No' WHEN user_blocked='Yes' AND 
user_blocked='Yes' WHEN user_blocked='No' WHERE user_id='$user_id'";

Upvotes: 0

Views: 29

Answers (1)

Jirka Hrazdil
Jirka Hrazdil

Reputation: 4021

$sql = "UPDATE `users` SET `user_blocked` = CASE
    WHEN `user_blocked` = 'No' THEN 'Yes'
    WHEN `user_blocked` = 'Yes' THEN 'No'
    END WHERE user_id='$user_id'";

Also: you use mysql_*() functions, which were deprecated as of PHP 7. Use mysqli_*() OR PDO instead. Your code is vulnerable to SQL injection attack, use prepared statements.

Upvotes: 1

Related Questions