Unable To Update SQL

Question

<?php

$staffid = $_GET['staffid'];

$query = "SELECT active FROM staff WHERE staffid = '$staffid'";

$result = mysqli_query($link, $query) or die(mysqli_error($link));
$status = mysqli_fetch_array($result);

//echo $status['active'];

if ($status['active'] = '0') {
    "UPDATE staff
        SET active ='1'
        WHERE staffid = '$staffid' ";
} else {
    "UPDATE staff 
        SET active ='0'
        WHERE staffid = '$staffid'";
}

Here is my code, I'm able to echo the "Active Status" 0 Or 1 But I'm unable to update based on the if else statement.

It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 20 '19 at 20:10

Dharman · Answer 1 · 2019-10-20T20:28:12.157

Your code should really be using prepared statements. If all you want to do is flip a boolean flag in the database, you can use one query.

// enable error reporting and connect
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = new mysqli($host, $user, $pass, $db);
$link->set_charset('utf8mb4');

// prepare -> bind -> execute
$stmt = $link->prepare('UPDATE staff SET active=!active WHERE staffid=?');
$stmt->bind_param('s', $_GET['staffid']);
$stmt->execute();

If you also would like to fetch the value, you can also use prepared statement.

$stmt = $link->prepare('SELECT active FROM staff WHERE staffid=?');
$stmt->bind_param('s', $_GET['staffid']);
$stmt->execute();
$result = $stmt->get_result();
$status = $result->fetch_assoc();
echo $status['active'];

You should also learn more about MySQLi error reporting: How to get the error message in MySQLi?

By the way, your original problem was that you used single equals, which is an assignement operator, not comparison, in the if statement $status['active'] = '0'

score -1 · Accepted Answer · answered Oct 20 '19 at 19:02

Although I would recommend using prepared statements for safety, you can do this in 1 update rather than 2 out of 3 statements, just update the status to 1 minus the current status

$query = "UPDATE staff SET active = 1 - active WHERE staffid = '$staffid'";
$result = mysqli_query($link, $query) or die(mysqli_error($link));

Unable To Update SQL

2 Answers2