Update column value to NULL based on $_POST values for both columns?

Question

Trying to update a column ($_POST['dtf']) value to null based on another columns value ($_POST['pid']), but its not working. I have tried different versions of the MySQL statement but with no avail.
AJAX

      $(function() {
      $(".remove-tf").click(function() {
      $.ajax({
          type: 'POST',
          url: 'tf-delete.php',
          data: 'pid=' + $(".pid").attr("value") + '&dtf=' + $(this).val()
      });
      });
      });

PHP

<?php
include 'sqlconnection.php';
$conn = OpenCon();
$sql = "UPDATE tasks SET '".$_POST['dtf']."'=NULL WHERE pid=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $_POST['pid']);
if ($stmt->execute()){
    header('Location: listings.php');
};
$stmt->close();
CloseCon($conn);
?>

HTML

<input type="hidden" class="pid" name="pid" value="">
<button class="remove-tf" name="submit" type="button" value="taskimg0">Remove</button>

`$(".remove-tf").attr("value")` will be the value of the first `.remove-tf` element in the DOM, not the one that the user clicked on. — Barmar, Mar 11 '20 at 18:15
You should use `$(this).val()` to get the clicked element's value. — Barmar, Mar 11 '20 at 18:15
And `$(".pid").attr("value")` should be `$(this).prev(".pid").val()` — Barmar, Mar 11 '20 at 18:16
@Suboptimizer He's using a prepared statement. But you can't use a placeholder for column names. — Barmar, Mar 11 '20 at 18:17
why `$(this).prev(".pid").val()`? can I not just get the value of the element based on its class name? they are not directly next to each other in my real code — Dimitri Bostrovich, Mar 11 '20 at 18:21
how do i structure the statement so that i can set the column value to the $_POST value? — Dimitri Bostrovich, Mar 11 '20 at 18:26
You have the column name wrapped in single quotes '".$_POST['dtf']."' change it to ".$_POST['dtf']." — Funk Doc, Mar 11 '20 at 18:29

score 1 · Answer 1 · edited Mar 11 '20 at 18:32

This is your code:

$sql = "UPDATE tasks SET '".$_POST['dtf']."' = NULL WHERE pid = ?";

This produce a query like:

UPDATE tasks set 'foo' = null where pid = ?

This is not valid MySQL. The column name should not be be surrounded with single quotes (in ANSI SQL, single quotes are there for strings litterals).

Just change your code to:

$sql = "UPDATE tasks SET ".$_POST['dtf']." = NULL WHERE pid = ?";

Or if you want to use quoted identifiers, use backticks:

$sql = "UPDATE tasks SET `".$_POST['dtf']."` = NULL WHERE pid = ?";

Update column value to NULL based on $_POST values for both columns?

1 Answers1