mysql not updating from php form

Question

I have a very simple PHP form, which shows a checkbox, and will store if it is checked or not in a database. This works for the initial inserting, but not for updating. I have tested cases where $saleid equals $pk and it does not enter the if branch to update...why?

<?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"]; }

$checkfield = "";

$checkboxes = (isset($_POST['checkboxes'])? $_POST['checkboxes'] : array());

if (in_array('field', $checkboxes)) $checkfield = 'checked';

$con = mysqli_connect("localhost","user","", "db");
if (!$con) { echo "Can't connect to MySQL Server. Errorcode: %s\n". mysqli_connect_error(); exit; }

$con->set_charset("utf8");

$getformdata = $con->query("select saleid, field from STATUS where saleid = '$pk'");
$saleid = "";
while ($row = mysqli_fetch_assoc($getformdata)) {
    $saleid = $row['saleid'];
    $checkfield = $row['field'];
}

if($cmd=="submitinfo") {
    if ($saleid == null) {
       $statusQuery = "INSERT INTO STATUS VALUES (?, ?)";
        if ($statusInfo = $con->prepare($statusQuery)) {
                $statusInfo->bind_param("sssssssssssss", $pk, $checkfield);
                $statusInfo->execute();
                $statusInfo->close();
        } else {
                print_r($con->error);
        }
    } else if ($saleid == $pk) {
        $blah = "what";
     $statusQuery = "UPDATE STATUS SET field = ? WHERE saleid = ?";
        if ($statusInfo = $con->prepare($statusQuery)) {
                $statusInfo->bind_param("ss", $checkfield, $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\">
                <h1>Editing information for Auction No: ".$pk."</h1>
                        <input type=\"checkbox\" name=\"checkboxes[]\" value=\"field\" ".$checkfield." />
                        <label for=\"field\">Test</label>
                        <br />
                        <input type=\"hidden\" name=\"cmd\" value=\"submitinfo\" />
                        <input name=\"Submit\" type=\"submit\" value=\"submit\" />
        </form>";
}
?>

Answer 1

not sure if you can mix GET and POST type requests

maybe change this so that pk is passed back as a hidden field ?

 echo "<form name=\"statusForm\" action=\"test.php?pk=".$pk."\" method=\"post\" enctype=\"multipart/form-data\">

eg, sort of like this

echo "<form name=\"statusForm\" action=\"test.php\" method=\"post\" enctype=\"multipart/form-data\">
<input type=\"hidden\" name=\"pk\" value=\"".$pk."\">

Answer 2

Here is what your HTML should look like:

    <form id="aform" action="thisform.php" method="post">
       <input type="checkbox" name="agree" value="yes" />
       <input type="hidden" name="secret" value="shhh" />
       <input type="submit" value="do it" />
    </form>

With the above if you do:

  print_r($_POST);

you will get an array that either has [agree] => 'yes' or nothing, depending on if they check the box, so no need to put the array brackets unless you have tons of boxes.

As for the SQL part, I suggest making the column a single integer type, where it can have either a 0 or 1. 0 for unchecked, 1 for checked. For the insert you would do something like:

   $check_value = ($_POST['agree'] == 'yes') ? 1 : 0;
   $secret_stuff = $_POST['secret'];
   mysqli_query("Insert INTO sales_table (secret_column, agree_column)
                 VALUES ('$secret_stuff', '$check_value')");

That will get your checkbox into the table. To get it out, you should go with:

  $results = mysqli_query("SELECT * from sales_table where secret_column = $secret_stuff")

  while($row = mysqli_fetch_assoc($results)) {
    $checked = ($row['agree_column'] == 1) ? "checked=\"checked\"" : "";
    $secret_stuff = $row['secret_column];
   }

   ?>
   <form action=blah method=post id=blah>
   <input type="checkbox" name="agree" value="yes" <?php echo $checked;?> />
   </form>

Sorry, lost steam at the end. But that covers the front end and back end. Use a 1/0 switch, and just set some variable like $checked to the "checked='checked'" if it's a 1.

Answer 3

You're not setting the $pk variable unless isset($_GET["pk"]), yet you're still using it later in the query. This isn't a good idea, since depending on other circumstances, this can lead to bugs. What you want your logic to look like is this:

if pk is not set in form
    insert new record
    deal with error if insert failed
else
    update existing record
    check update count and deal with error if 0 records were updated
        (perhaps by doing an insert of the missing record)
end

Answer 4

Just as a side note, it looks like the mysql REPLACE function would come in handy for you.

Also, when a checkbox is not checked, the value can be a tricky thing. I have written a function that sets the value to one, if the posted value is set, and zero if not...

function checkbox_value($name) {
    return (isset($_POST[$name]) ? 1 : 0);
}

You can run your posted checkbox value throught that query and always get a one or a zero.

Answer 5

well i created a table and ran your code and it works fine for me

the reason why it doesn't "look" like update is working, is because you are reading $saleid and $checkfield from the database then building an update statement that puts the same two values back into the database

which probably isn't what you are wanting to do

this line here sets $checkfield to 'checked',

 if (in_array('field', $checkboxes)) $checkfield = 'checked';

then you set $checkfield from the database (overwriting the value 'checked' )

while ($row = mysqli_fetch_assoc($getformdata)) {
   $saleid = $row['saleid'];
   $checkfield = $row['field'];

then you write the original value of checkfield back to the database

$statusInfo->bind_param("ss", $checkfield, $pk);