problems with update logic for a mysql db

Question

I have the following PHP form, which posts back to a mysql database. My problem is that the update query seems to work, but is always overwritten with "checked". What I want to do is check is get the current value from the database, and then if there is a value in post, get that instead. Now...why is this not working? Do I need to have an else clause when checking if it is in _POST? If that's the case, do I even need to initilise the variable with $checkDeleted = "";?

<?php
error_reporting(E_ALL);
if (isset($_GET["cmd"]))
  $cmd = $_GET["cmd"]; else
if (isset($_POST["cmd"]))
  $cmd = $_POST["cmd"]; else die("Invalid URL");
if (isset($_GET["pk"])) {
    $pk = $_GET["pk"];
}
$checkDeleted = "";
$con = mysqli_connect("localhost","user","pw", "db");
$getformdata = $con->query("select ARTICLE_NO, deleted from STATUS where ARTICLE_NO = '$pk'");
while ($row = mysqli_fetch_assoc($getformdata)) {
    $ARTICLE_NO = $row['ARTICLE_NO'];
    $checkDeleted = $row['deleted'];
}
$checkboxes = (isset($_POST['checkboxes'])? $_POST['checkboxes'] : array());
if (in_array('deleted', $checkboxes)) $checkDeleted = 'checked';
if($cmd=="submitinfo") {
    if ($ARTICLE_NO == null) {
     $statusQuery = "INSERT INTO STATUS VALUES (?, ?)";
     if ($statusInfo = $con->prepare($statusQuery)) {
      $statusInfo->bind_param("ss", $pk, $checkDeleted);
      $statusInfo->execute();
      $statusInfo->close();
     } else {
      print_r($con->error);
     }
    } else if ($ARTICLE_NO == $pk) {
     $statusQuery = "UPDATE STATUS SET deleted = ? WHERE ARTICLE_NO = ?";
     if ($statusInfo = $con->prepare($statusQuery)) {
      $statusInfo->bind_param("ss", $checkDeleted, $pk);
      $statusInfo->execute();
      $statusInfo->close();
     } else {
      print_r($con->error);
     }
    }
}
if($cmd=="EditStatusData") {
    echo "<form name=\"statusForm\" action=\"test.php?pk=".$pk."\" method=\"post\" enctype=\"multipart/form-data\">
                        <input type=\"checkbox\" name=\"checkboxes[]\" value=\"deleted\" ".$checkDeleted." />
                        <label for=\"deleted\">Delete</label>
                        <input type=\"hidden\" name=\"cmd\" value=\"submitinfo\" />
                        <input name=\"Submit\" type=\"submit\" value=\"submit\" />
        </form>";
}
?>

I tried changing the line to set checkDeleted to the following, which made no difference..although it should?

if (in_array('deleted', $checkboxes)) {
$checkDeleted = 'checked';
} else {
$checkDeleted = '';
}

edit: OK, I have managed to get this to work, but only after changing to

$checkDeleted = in_array('deleted', $checkboxes) ? 'checked' : '';

as per the answer below, but this still did not work. For it to work I had to remove the database query, and replace it with one within the submitinfo branch, and one within the EditStatusData branch...why? Why is it not possible to have only one query?

if($cmd=="submitinfo") {

$getformdata = $con->query("select ARTICLE_NO from STATUS where ARTICLE_NO = '$pk'");

while ($row = mysqli_fetch_assoc($getformdata)) {
    $ARTICLE_NO = $row['ARTICLE_NO'];
}    
    if ($ARTICLE_NO == null) { etc

and

if($cmd=="EditStatusData") {
$getformdata = $con->query("select deleted from STATUS where ARTICLE_NO = '$pk'");

while ($row = mysqli_fetch_assoc($getformdata)) {
    $checkDeleted = $row['deleted'];
} etc

Answer 1

This will only work if you're GET'ing data:

$getformdata = $con->query("select ARTICLE_NO, deleted from STATUS where ARTICLE_NO = '$pk'");

In your code $pk isn't set if your request is POST. You should also escape the $pk variable in this line as a user could put any data they liked in $_GET['pk'] and it could break your SQL query.

Answer 2

this is pretty much identical to your other question

http://stackoverflow.com/questions/904879/mysql-not-updating-from-php-form/905834#905834

there is nothing wrong with the code, it is working exactly as you want

What I want to do is get the current value from the database, and then if there is a value in post, get that instead.

case 1: html form with no tick

1. read from database $checkDeleted = 'checked'
2. if $_POST['checkboxes']['deleted'] is not set, leave $checkDeleted as is
3. writes 'checked' to database

case 2. html form with tick

1. read from database $checkDeleted = 'checked'
2. if $_POST['checkboxes']['deleted'] is set, change $checkDeleted = 'checked'
3. writes 'checked' to database

so no matter if you have a tick or not, once you have changed the database value to checked then, there is no way to change it

I will assume that what you want to do is always overwrite the database value with whatever the tick box is set to, in that case

replace this line

 if (in_array('deleted', $checkboxes)) $checkDeleted = 'checked';

with this

 $checkDeleted = in_array('deleted', $checkboxes) ? 'checked' : '';