使用php MVC / ajax / json删除多条记录时的foreach问题

Question

I am listing a table which shows records from the database and each row has a checkbox with an ID value associated with it.

<label class="m-checkbox">
 <input type="checkbox" name="order_id[]" value="<?php echo sanitize($failed->order_id); ?>">
 <span></span>
</label>

Once the user has selected the checkboxes of the records they want to delete, the submit button is pressed and that triggers an ajax request.

$( '.delete-failed' ).on('click', function(e) {
    e.preventDefault();
    var form = $( '#delete-failed' ).serialize();
    $.ajax({
        url: url + '/TransactionsAjax/deleteFailedTransactions',
        type: 'POST',
        data: form,
        dataType: 'json',
        beforeSend: function() {
            $( 'delete-failed' ).prop('disabled', true);
        }
    })
    .done(function (data) {
        if(!data.success) {
            $( '.alert-danger' ).append(data.message).fadeIn();

        } else {


            $( '.alert-success' ).append(data.message).fadeIn();
        }
    })
            .fail(function (jqXHR, textStatus, errorThrown) {
            console.log(textStatus + ': ' + errorThrown);
            console.warn(jqXHR.responseText);
        });
});

That then goes to the controller:

if($_SERVER['REQUEST_METHOD'] === 'POST') {

    $response = array();
    $message = '';


    $orderId = $_POST['order_id'];

    $data = [

        'order_id' => $orderId
    ];

    if($this->TransactionsModel->deleteFailedTransactions($data)) {
        $response['success'] = true;
        $response['message'] = 'Failed transactions deleted';

    } else {

        $response['success'] = false;
        $response['message'] = 'Something went wrong. Please try again later.';
    }

    echo json_encode($response);    
}

Which is supposed to send the data to the model:

public function deleteFailedTransactions($data)
    {
        $this->db->beginTransaction();

        try {

            $this->db->query("DELETE FROM `order_summary` WHERE `order_id` = :order_id");
            $this->db->bind(":order_id", $order_id);

            foreach($data as $item) {
                $order_id = $item['order_id'];
                $this->db->execute();
            }


            $this->db->query("DELETE FROM `order_detail` WHERE `order_id` = :order_id");
            $this->db->bind(":order_id", $order_id);

            foreach($data as $item) {
                $order_id = $item['order_id'];
                $this->db->execute();
            }

            $this->db->commit();
            return true;
        }

        catch(Exception $e) {
            $this->db->rollBack();
            echo $e;
            return false;
        }
    }

The error I am getting in console is:

undefined index: order_id

It is referring to this line in the model:

$order_id = $item['order_id'];

I am not sure at which point the problem is, is it in the data being sent via ajax, should the foreach loop be in the controller instead.. I am not sure.

Answer 1

The problem stems from the mishandling of data and variable names; that is, consider how you are wrapping and passing data. Are you intending to pass one order id from the checkboxes at a time in the ajax, or multiple? It's unclear, so I've modified the code to handle one order id. If it's multiple, you need to loop in the controller and properly create an array of order ids from the post, and then properly foreach loop over each order id in the model and bind and execute the query for each. Make sure you're looping over the object actually holding the list of ids.

For one issue, in your model $order_id doesn't exist, you only passed in the $data variable, you might be looking for $data['order_id'], or simply passing in $order_id without encompassing it in the $data array.

Similarly, this $data variable seems to be just an array that contains one order_id. It makes no sense to foreach loop on $data when it is hardcoded to contain one key,value pair.

I would try this:

if($_SERVER['REQUEST_METHOD'] === 'POST') {

  $response = array();
  $message = '';

  $orderId = $_POST['order_id'];

  if($this->TransactionsModel->deleteFailedTransactions($orderId)) {
    $response['success'] = true;
    $response['message'] = 'Failed transactions deleted';

  } else {

    $response['success'] = false;
    $response['message'] = 'Something went wrong. Please try again later.';
  }

  echo json_encode($response);    
}

And then the model:

public function deleteFailedTransactions($order_id)
{
    $this->db->beginTransaction();

    try {

        $this->db->query("DELETE FROM `order_summary` WHERE `order_id` = :order_id");
        $this->db->bind(":order_id", $order_id);

        $this->db->execute();

        $this->db->query("DELETE FROM `order_detail` WHERE `order_id` = :order_id");
        $this->db->bind(":order_id", $order_id);

        $this->db->execute();

        $this->db->commit();
        return true;
    }

    catch(Exception $e) {
        $this->db->rollBack();
        echo $e;
        return false;
    }
}

Seeing your html have order_id[] indicates you may be trying to send multiple. Depending on how data is passing, you may need to explode/implode the list differently than I am. Consider this:

if($_SERVER['REQUEST_METHOD'] === 'POST') {

  $response = array();
  $message = '';

  $orderIds = $_POST['order_id']; //explode maybe

  if($this->TransactionsModel->deleteFailedTransactions($orderIds)) {
    $response['success'] = true;
    $response['message'] = 'Failed transactions deleted';

  } else {

    $response['success'] = false;
    $response['message'] = 'Something went wrong. Please try again later.';
  }

  echo json_encode($response);    
}

And then the model:

public function deleteFailedTransactions($order_id)
{
    $this->db->beginTransaction();

    try {
        foreach($order_id as $id) {
          $this->db->query("DELETE FROM `order_summary` WHERE `order_id` = :order_id");
          $this->db->bind(":order_id", $id);

          $this->db->execute();
        }

        foreach($order_id as $id) {
          $this->db->query("DELETE FROM `order_detail` WHERE `order_id` = :order_id");
          $this->db->bind(":order_id", $id);

          $this->db->execute();
        }

        $this->db->commit();
        return true;
    }

    catch(Exception $e) {
        $this->db->rollBack();
        echo $e;
        return false;
    }
}

And you might consider optimizing the foreach queries into one each, like this:

  $this->db->query("DELETE FROM `order_summary` WHERE `order_id` IN (:order_id)");
  $this->db->bind(":order_id", implode(",",$order_id); //whatever gives you comma separated string
  $this->db->execute();

Note that I'm not running this code, so consider it guidance/PHP oriented pseudocode for you to figure it out yourself. Feel free to ask clarifying questions via DM.