-3

I have a problem deleting all child nodes and parent nodes using with PHP recursive function. The database table structure is followed the tree structure.

Below is sample table structure:

MySQL Table: folders

id    name    parentid
1      A1         0
2      A1-1       1
3      A1-2       2
4      A1-3       3
5      A2         0
6      A2-1       5
7      A2-2       6
8      A2-3       7

id = the id of the category
name= folder name
parent = the id of the parent category

According to the table: folders. For example, if I want to delete id is 1, following the child nodes (id are 2,3,4) should be deleted.

I am followed the below sample coding to do the delete recursive function, but can't work and crashes.

<?php
function remrecurs($id) {
    $qlist= mysqli_query($sql_connection,"SELECT * FROM folders WHERE parentid='$id'");
    if (mysqli_num_rows($qlist)>0) {
        while($curitem = mysqli_fetch_assoc($qlist)) {
remrecurs($curitem['id']);
} 
} mysqli_query($sql_connection,"DELETE FROM folders WHERE id='$id'"); 
}
  remrecurs(1);
?>
Improve this question
1

1 Answer 1

Reset to default
0

The logic of the question you mention is correct, but the implementation on your code is not.

You're using $sql_connection but that variable is not available in your function. Assuming $sql_connection, to use it in a function you need to make it global

Furthermore as @Dharman mentioned in their comment, you should always use prepared statements when using parameters in your query.

Also while debugging, always ensure that you add the proper error reporting information with mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); so that you can quickly identify the issues with your queries.

I've refactored your code to have the recursion work correctly:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
function remrecurs($id) {
    global $sql_connection;
    $query = "SELECT id FROM folders WHERE parentid=?";
    if ($stmt = $sql_connection->prepare($query)) {
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($data = $result->fetch_assoc()) {
            remrecurs($data['id']);
        }
    }
    $delete = 'DELETE FROM folders WHERE id = ?';
    $stmt = $sql_connection->prepare($delete);
    $stmt->bind_param('i', $id);
    $stmt->execute();
}
remrecurs(1);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.