Mysql not updating from php form
I have a very simple PHP form that shows a checkbox and will be stored if it is validated or not in the database. This works for the initial insert, but not for the update. I've tested cases where $ saleid is $ pk and it doesn't 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>";
}
?>
a source to share
Well I created a table and ran your code and it works fine for me
the reason why it doesn't look like the update works is because you are reading $ saleid and $ checkfield from the database, after which an update statement is generated that returns the same two values to the database
which is probably not what you want to do
this line here sets $ checkfield to 'checked',
if (in_array('field', $checkboxes)) $checkfield = 'checked';
then you are setting $ checkfield from the database (overwriting the "checked" value)
while ($row = mysqli_fetch_assoc($getformdata)) {
$saleid = $row['saleid'];
$checkfield = $row['field'];
then you write the original checkfield value back to the database
$statusInfo->bind_param("ss", $checkfield, $pk);
a source to share
not sure if you can mix GET and POST 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\">
for example like this
echo "<form name=\"statusForm\" action=\"test.php\" method=\"post\" enctype=\"multipart/form-data\">
<input type=\"hidden\" name=\"pk\" value=\"".$pk."\">
a source to share
This is how your HTML looks 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>
If you do above:
print_r($_POST);
you will end up with an array that is either [agree] => "yes" or nothing, depending on whether they are checked, so there is no need to put the array brackets if you don't have chunks of boxes.
As far as the SQL part goes, I suggest making the column a single integer type where it can be 0 or 1.0 for unchecked, 1 for checked ones. To insert you have to 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')");
This will set your checkbox in the table. To get it you have to 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 this applies to the front and back. Use the 1/0 radio button and just set some $ checked variable to "checked = 'checked" if it's equal to 1.
a source to share
You don't set the $ pk variable, unless isset($_GET["pk"])
, but you still use it later in the request. This is not a good idea, as it can lead to errors depending on other circumstances. What you want your logic to look like is:
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
a source to share
As a side note, it looks like the mysql REPLACE function will come in handy for you.
Also, if the box is not checked, the value can be tricky. I wrote a function that sets the value to one if the set value is set, and zero if not ...
function checkbox_value($name) {
return (isset($_POST[$name]) ? 1 : 0);
}
You can run your posted checkbox value through this request and always get one or zero.
a source to share