Issues with update logic for mysql db
I have the following PHP form which is returning to mysql database. My problem is that the update request seems to work, but is always overwritten with "checked". What I want to do is check if the current value is getting from the database, and then if there is a value in the message, get that instead. Now ... why isn't this working? Do I need to have an else clause when checking if it is in _POST? If so, I even need to initialize 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 didn't matter ... although it should be?
if (in_array('deleted', $checkboxes)) {
$checkDeleted = 'checked';
} else {
$checkDeleted = '';
}
edit: Ok, I managed to get this to work, but only after switching to
$ checkDeleted = in_array ('deleted', $ checkboxes)? 'checked': '';
as per the answer below, but that still didn't work. To do this, I had to delete the database request and replace it with one in the submitinfo branch and one in the EditStatusData branch ... why? Why can't I get just one request?
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
a source to share
this is pretty much identical to your other question
mysql is not updating from php form
there is nothing wrong with the code, it works exactly the way you want
What I want to do is get the current value from the database and then if there is a value in the column, do that.
case 1: html form without check mark
- read from database $ checkDeleted = 'checked'
- If $ _POST ['checkboxes'] ['deleted'] is not set, leave $ checkDeleted as is
- writes 'checked' to the database
case 2.html form with tick
- read from database $ checkDeleted = 'checked'
- if set $ _POST ['checkboxes'] ['deleted'], change $ checkDeleted = 'checked'
- writes 'checked' to the database
so no matter if you have a tick or not, once you change the database value to check, there is no way to change it.
I am guessing that what you want to do is always overwrite the database value no matter what the checkbox is for, in this case
replace this line
if (in_array('deleted', $checkboxes)) $checkDeleted = 'checked';
with this
$checkDeleted = in_array('deleted', $checkboxes) ? 'checked' : '';
a source to share
This will only work if you receive GET data:
$getformdata = $con->query("select ARTICLE_NO, deleted from STATUS where ARTICLE_NO = '$pk'");
In your code, $ pk is not set if your POST request. You should also avoid the $ pk variable on this line, as the user can put any data they like in $ _GET ['pk'] and this can break your SQL query.
a source to share