0

I've been trying to delete rows from my handsontable (which is displaying rows from my database), but I can't find a solution to delete them using the "remove row" option. The plan is when I click on a row and remove the row, I want it to go into the delete_com.php and delete this row from the database. This should be done by getting the ID on the row you're removing on the handsontable and then using the ID it should remove it from the database.

I've tried using ajax and passing the ID in the table into another file to delete in the database, but nothing is getting deleted.

function deleteRowsFromDatabase(rowToDelete) {
    var idToDelete = rowToDelete[0];
    $.ajax({
        type: 'POST',
        url: 'delete_com.php',
        data: { ids: [idToDelete] }, 
        success: function(response) {
            console.log('Rows deleted successfully:', response);

            if (response.length > 0 && response[0].ID) {
                var deletedID = response[0].ID;
                var rowIndex = hot.getData().findIndex(function(row) {
                    return row[0] === deletedID;
                });

                if (rowIndex !== -1) {
                    hot.alter('remove_row', rowIndex);
                }
            }
        },
        error: function(xhr, status, error) {
            console.error('Error deleting rows:', error);
        }
    });

    var rowIndex = hot.getSourceData().indexOf(rowToDelete);
    if (rowIndex !== -1) {
        hot.alter('remove_row', rowIndex);
    }
}
    
    hot.addHook('afterRemoveRow', function(index, amount) {
    var removedRows = loadedData.slice(index, index + amount); 
    console.log('ammount', amount, 'index',  index)
    deleteRowsFromDatabase(removedRows);
});

EDIT:

I've found out that if i delete a row that has another row below it, it'll send this data through but it sends the row below the removed row instead.

For example: I deleted a row with the ID: 5. It'll remove this from the table (temporarily) and in the console log it shows it's trying to remove the row with ID: 6.

SOLVED INDEX ROW PROBLEM:

hot.addHook('beforeRemoveRow', function (index, amount) {
    var removedRows = loadedData.slice(index, index + amount);
    console.log('amount', amount, 'index', index);

    hot.addHookOnce('afterRemoveRow', function () {
        deleteRowsFromDatabase(removedRows);
    });
});

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['ids'])) {
        $ids = $_POST['ids'];
        
        $idList = implode("','", $ids);
        $stmt = $conn->prepare("DELETE FROM com WHERE ID IN ('$idList') AND company = ?");
        $stmt->bind_param("i", $compid);
        $stmt->execute();
        $stmt->close();

        echo "Rows deleted successfully", $idList;
    } else {
        echo "No IDs sent";
    }
}

This the PHP code inside of the delete_com.php. It should be deleting the row with the ID that's been selected, but it just passes through this code and says "Rows deleted succesfullyArray" in the console, but it hasn't.

zahq
  • 17
  • 2
  • Welcome to StackOverflow. You're expected to [try to solve the problem first](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users). Please update your question to show what you have already tried in a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). For further information, please see [How to Ask](https://stackoverflow.com/questions/how-to-ask), and take the [tour](https://stackoverflow.com/tour) :) – Alive to die - Anant Sep 01 '23 at 07:13
  • You probably should start debugging at `var removedRows = loadedData.slice(index, index + amount); `. Do index and amount contain what you expect? Does `removedRows` contain what you expect afterwards? What's in `loadedData`? There's a lot missing from your question. Remember we can't run your code unless you provide a [mre] of the issue (and even then, not always). – ADyson Sep 01 '23 at 07:22
  • And this really has nothing to do with PHP. As you said yourself, the problem is that the IDs are not being sent to PHP properly. Therefore the problem occurs before the PHP code is involved. All the code you've shown us is JavaScript, so I've re-tagged it with that. If you tag your question accurately, you're more likely to get an audience of people who are a) interested in your topic and b) have the expertise to help you. See also the [tour], [ask] and [What are tags, and how should I use them?](https://stackoverflow.com/help/tagging) – ADyson Sep 01 '23 at 07:23
  • Thank you for your response @ADyson . The index and amount both contain what I expect (Index being which row in the table is being deleted and amount being how many). loadedData contains the rows of the table excluding the row that's been deleted. – zahq Sep 01 '23 at 07:31
  • _"loadedData contains the rows of the table excluding the row that's been deleted"_ - but the row that _has_ been deleted is what you are trying to find - so why are you looking for it by trying to slice a data set that already doesn't contain it any more at that point? – CBroe Sep 01 '23 at 08:14
  • Doesn't the `physicalRows ` mentioned here, https://handsontable.com/docs/javascript-data-grid/api/hooks/#afterremoverow, already give you access to exactly what you need- the _removed_ rows? _"physicalRows | Array | An array of physical rows removed from the data source."_ – CBroe Sep 01 '23 at 08:16
  • @CBroe It was a bit silly of me, I've fixed this now by replacing"beforeRemoveRow" instead of "afterRemoveRow" and inside of that I've included the "afterRemoveRow". I'll add this to the post – zahq Sep 01 '23 at 08:16
  • @CBroe I'm able to get the rows removed on the frontend until the i refresh and they come back. This is because earlier in the code (not shown for confidential reasons) I've imported these rows from the database. To remove these rows, I have to remove them from the database. – zahq Sep 01 '23 at 08:18
  • Glad you fixed it...but: You need to post your solution in the Answers section below. The answer is not part of the question! Right now, no-one can search for your solution or vote on it, because to the search engine it looks like (part of) a question. Please take the [tour] to ensure you understand how Stackoverflow's question-and-answer format works. The [Can I answer my own question?](https://stackoverflow.com/help/self-answer) and [answer] articles from the help centre are also relevant. Once you've added an answer properly, you should also roll back the edit to the question. Thanks. – ADyson Sep 01 '23 at 08:19
  • @ADyson sorry! I've not got the solution exactly for the original question right now. The one I've added is for the problem I came across during this. – zahq Sep 01 '23 at 08:21
  • Ah ok. So what issue is still remaining? Are you saying that `idToDelete` is null in the `deleteRowsFromDatabase` function, is that the issue? – ADyson Sep 01 '23 at 08:22
  • @ADyson I'll have to show my PHP code as I'm sure this is where the problem is. It's all passing into delete_com.php correctly, but it's not deleting the row i've deleted in the database. Should I add the PHP code on the solution or just here in the comments? – zahq Sep 01 '23 at 08:26
  • `It's all passing into delete_com.php correctly`...that's not what you said before (you said "When the ids get sent to the delete_com.php, it does not send anything, just NULL"). But ok, yes you will need to add the code to the question. You can't add large chunks of code into the comments (it won't all fit, it can't be formatted properly, and it doesn't show in searches, and it's out of context with the rest of the question anyway). – ADyson Sep 01 '23 at 08:27
  • @ADyson This was because of the problem I figured out during this. It was trying to send the ID that's still in the table instead of the one that's being deleted because I had it be sliced from the table before it was sent rather than after. – zahq Sep 01 '23 at 08:29
  • Ok so, in theory then you _have_ answered your original question, because that was about not sending the ID properly to the server. Now the fact it's not deleting in the database is, arguably, a separate issue (as a programmer, you should always break the task/problem down into small pieces as much as possible!). But as it's all part of the same feature, we could just about count it as the same question here. So go ahead and post the PHP code. Have you tried to debug the PHP yet? – ADyson Sep 01 '23 at 08:31
  • Ah, again, I'm sorry. Quite new to stackoverflow, as you've probably guessed. – zahq Sep 01 '23 at 08:32
  • `WHERE ID IN ('$idList')` ...oh dear. 1) It's inside quote marks, so the list of IDs will be treated by SQL as a single string, not a list of separate values. 2) It's vulnerable to SQL injection because you haven't parameterised the values (and incidentally, to syntax problems such as in point 1) because with parameters you don't need to worry about such niceties as quote marks). – ADyson Sep 01 '23 at 08:35
  • See [How can I bind an array of strings with a mysqli prepared statement?](https://stackoverflow.com/questions/17226762/how-can-i-bind-an-array-of-strings-with-a-mysqli-prepared-statement) for how to do it properly. – ADyson Sep 01 '23 at 08:36
  • Thank you so much for the help @ADyson I've got this sorted now, I've changed a bit, so would like your input on it. I'll add it as a solution – zahq Sep 01 '23 at 09:24

1 Answers1

0
    function deleteRowsFromDatabase(idToDelete) {
        $.ajax({
            type: 'POST',
            url: 'delete_com.php',
            data: { ids: idToDelete }, // Send only the ID
            success: function (response) {
                console.log('Row deleted successfully:', response);
            },
            error: function (xhr, status, error) {
                console.error('Error deleting row:', error);
            }
        });
    }

    hot.addHook('beforeRemoveRow', function (index, amount) {
        var removedRowIds = [];
        for (var i = index; i < index + amount; i++) {
            var row = hot.getDataAtRow(i);
            removedRowIds.push(row[0]);
        }

        hot.addHookOnce('afterRemoveRow', function () {
            for (var i = 0; i < removedRowIds.length; i++) {
                deleteRowsFromDatabase(removedRowIds[i]);
            }
        });
    });

I've changed hot.addHook('afterRemoveRow') to hot.addHook('beforeRemoveRow') as the the row I wanted to delete was being sliced before it was being sent to delete_com.php. I've done it so it slices it after the data has been sent to delete_com.php. I've also made it so it only gathers the ID from the row instead of the whole row as the ID is the only thing that's needed.

include "sessions.php";
include "../../db/conn.php";
$compid = $_SESSION['company'];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['ids'])) {
        $idToDelete = $_POST['ids'];
        
        $stmt = $conn->prepare("DELETE FROM com WHERE ID = ? AND company = ?");
        $stmt->bind_param("si", $idToDelete, $compid);
        $stmt->execute();
        $stmt->close();

        echo "Row deleted successfully: $idToDelete";
    } else {
        echo "No ID sent";
    }
}
$conn->close();

This is the delete_com.php and I've removed the WHERE IN in the sql statement as it wasn't needed. It will now use the data being sent in $_POST['ids'] (which be the ID of the row only). If this ID is in the database, it will be deleted.

zahq
  • 17
  • 2