MySQL Not Executing Update

Question

I am trying to submit an AJAX request to update a row in my MySQL database. Whenever I try to submit the request via AJAX the row is not updating, but whenever I test the query and parameter values directly in the database the row is updated.

This is what the markup and AJAX look like:

$('body').on('change', '.cb_exempt', function() {
  var checked = this.checked;
  var business_id = $(this).attr('data-id');
  var jqxhr = $.post("php/update_exempt.php", {
      exempt: checked,
      business_id: business_id
    })
    .done(function(data) {
      alert("The business successfully updated");
    })
    .fail(function(jqXHR, textStatus, errorThrown) {
      alert(errorThrown);
    });
});

<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.3.1.min.js"></script>
<table id="table_businesses" role="grid" aria-describedby="table_businesses_info">
  <thead>
    <tr role="row">
      <th aria-controls="table_businesses">Name</th>
      <th aria-controls="table_businesses">Active</th>
      <th aria-controls="table_businesses">Exempt from Billing</th>
      <th aria-controls="table_businesses">Billing History</th>
    </tr>
  </thead>
  <tbody>
    <tr role="row" class="odd">
      <td>[removed]</td>
      <td><input class="mx-auto cb_active" checked="checked" data-id="1" type="checkbox"></td>
      <td><input class="mx-auto cb_exempt" data-id="1" type="checkbox"></td>
      <td><a href="business.php?business_id=1">View Billing History</a></td>
    </tr>
  </tbody>
</table>

And this is what my PHP looks like:

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST' || empty($_POST['exempt']) || empty($_POST['business_id'])) {
  // Get the database object from the configuration file
  $db;
  try {
    $db = include('config_consumer_database.php');
  } catch(PDOException $ex) {
    throw new Exception('Database exception.');
  }

  // Update the business' exempt status
  $stmt = $db->prepare('UPDATE IGNORE `business` SET `is_exempt` = :exempt WHERE `business_id` = :business_id;');

  // Execute the query passing the new exempt value and the business_id
  $stmt->execute(array(
    ':exempt' => $_POST['exempt'],
    ':business_id' => $_POST['business_id']
  ));
} else {
  throw new Exception('The server understood the request, but is refusing to fulfill it. Authorization will not help and the request SHOULD NOT be repeated.');
}
?>

As I mentioned, it will not work whenever I try to use the AJAX request, but I've echoed the query String as well as the $_POST so that I could copy/paste the output directly in my MySQL database to confirm that the query would run and it does.

UPDATE

I did notice an issue with my PHP code in that I was using OR statements instead of AND statements in my conditional statement, so I changed that. This did not solve my issue.

I also took the advice to use isset or strlen($_POST['...']) > 0 instead of empty. I tried both, neither of which solved my issue.

I also tried to use regular parameters instead of named parameters and passing my $_POST directly into the execute like the following:

// Update the business' exempt status
$stmt = $db->prepare('UPDATE IGNORE `business` SET `is_exempt` = ? WHERE `business_id` = ?;');

// Execute the query passing the new exempt value and the business_id
$stmt->execute(array_values($_POST));

This did not solve my issue either.

Whenever I check my JavaScript console log, nothing shows up after I submit the AJAX request. Whenever I check my error.txt log (xampp > apache > logs > error.txt), nothing shows up after I submit the AJAX request.

The parameters sent in the $.POST request are as I expect them to be.

Answer 1

did you check mysql query log to ensure what is the query being executed.

you can check sql log file by query:

show variables like '%log%';

you can enable sql log by executing:

set global general_log=1;

Can set log file by:

set global general_log_file='/var/log/mysql/mysql.log';

please check sql log once.

Answer 2

I was able to figure out my problem and it has to deal with PHP being a loosely typed language. To fix the issue, I used bindValue explicitly converting the $_POST values by using boolval and intval, and then I also specified the PDO parameter type:

// Update the business' exempt status
$stmt = $db->prepare('UPDATE IGNORE `business` SET `is_exempt` = :exempt WHERE `business_id` = :business_id;');

// Add the parameters
$stmt->bindValue(':exempt',      boolval($_POST['exempt']),     PDO::PARAM_INT);
$stmt->bindValue(':business_id', intval($_POST['business_id']), PDO::PARAM_INT);

// Execute the query passing the new exempt value and the business_id
$stmt->execute();

I would not have figured this out without Abhishek Sharma's suggestion of checking the SQL's general log and recognizing that SQL was actually passing String values rather than Integer values:

Execute UPDATE IGNORE `business` SET `is_exempt` = 'true' WHERE `business_id` = '1'

UPDATE

Actually, boolval didn't work. I had to use this solution to convert the $_POST values: https://stackoverflow.com/a/38616262/1920035

Answer 3

generally you should use empty for only array. Use strlen($_POST['exempt']) > 0 instead of empty($_POST['exempt]) in you condition.