PHP: Tricky Nested Loop with select

Question

I have a problem at my current project.

I have this in my DB:

ID ---- FOLDERID ---- Description

1 ---- 0 ---- => Root Folder (can't be deleted)

2 ---- 1 ---- =>First Folder (references to Root Folder)

3 ---- 1 ---- => Second Folder (also references to Root)

4 ---- 2 ---- => Sub folder in First Folder

5 ---- 4 ---- => Sub folder in Sub Folder

this will be displayed like this:

2. First Folder
   4. Sub Folder
      • 5. Sub Folder in Sub Folder
3. Second Folder

Ok, So when i want to delete the First Folder, i want to delete all subfolders, too.

How can i handle this?

i know i need a loop with counter, but i don't know how to do it.

this is what i have: but only works for 3 folders:

     $del_id = [];

    do {
        $count = \DB::table('files')
            ->select('id', 'foldersID')
            ->where('userID', '=', \Auth::user()->id)
            ->where('foldersID', '=', $id)
            ->count();

        //count = 1

        if ($count == 0) {

            \DB::table('files')
                ->where('userID', '=', \Auth::user()->id)
                ->where('id', '=', $id)
                ->delete();

        } else {
            //hier
            $_id = $id; //2

            for ($i = 0; $i <= $count; $i++) { //1 mal

                $_files = \DB::table('files')
                    ->select('id')
                    ->where('userID', '=', \Auth::user()->id)
                    ->where('foldersID', '=', $_id)
                    ->get();

                //3

                $files = json_decode($_files, true);

                //return print_r($files);
                // return count($files);
                for ($i = 0; $i <= count($files) - 1; $i++) {
                    array_push($del_id, $files[$i]);
                }
               //3 && 4
            }

            for ($i = 0; $i <= count($del_id) - 1; $i++) {
                $_files = \DB::table('files')
                    ->select('id')
                    ->where('userID', '=', \Auth::user()->id)
                    ->where('foldersID', '=', $del_id[$i])
                    ->get();

                $files = json_decode($_files, true);
                return $files;

                /*   \DB::table('files')
                    ->where('userID', '=', \Auth::user()->id)
                    ->where('id', '=', $del_id[$i])
                    ->delete();
            */
             }

            \DB::table('files')
                ->where('userID', '=', \Auth::user()->id)
                ->where('id', '=', $id)
                ->delete();

        }
    } while ($count >= 1);

Can anyone please help me?

Thanks in advance.

Answer 1

You need to use recursion to do this. Basically, create a function to delete a folder. When the function is called it checks to see if it has any subfolders, if so, it calls itself with the id of the sub folder.

Below I have included SQL to test this as well as a PHP script showing the the recursion method.

SQL to create a "testing" database

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testing`;

USE `testing`;

DROP TABLE IF EXISTS `files`;
CREATE TABLE `files` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `folderid` int(11) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

LOCK TABLES `files` WRITE;
INSERT INTO `files` VALUES (1,0,'root folder'),(2,1,'first folder'),(3,1,'second folder'),(4,2,'sub of first folder'),(5,4,'sub of sub folder');
UNLOCK TABLES;

PHP script using recursion

<?php

delete_folder(2);


function delete_folder($folder_id) {
    $servername = "localhost";
    $username = 'root';
    $password = 'my_password';
    $dbname = "testing";

    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $table  = "files";

    // find subfolders
    $sub_folders = "select id from $table where folderid = $folder_id";
    $result = $conn->query($sub_folders);

    if($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            echo "found sub folder with id: ".$row["id"]." - recursing\n";
            delete_folder($row["id"]); // calls itself to delete subfolder.
        }
    }

    // delete this folder
    $delete_sql = "delete from $table where id = $folder_id";
    echo "deleting folder with id: $folder_id";
    if($conn->query($delete_sql) === TRUE) {
        echo " - success\n";
        return;
    } else {
        echo " - failed\n";
        die("Failed to delete folder");
    }
    return;
}

?>

Example of creating the DB and running the script

Importing our data:

root@zim:~/testing# mysql -p < testing.sql
Enter password:

Seeing whats in the table:

root@zim:~/testing# mysql -p -e 'select * from files' testing;
Enter password:
+----+----------+---------------------+
| id | folderid | description         |
+----+----------+---------------------+
|  1 |        0 | root folder         |
|  2 |        1 | first folder        |
|  3 |        1 | second folder       |
|  4 |        2 | sub of first folder |
|  5 |        4 | sub of sub folder   |
+----+----------+---------------------+

Running our script:

root@zim:~/testing# php db_recursion.php
found sub folder with id: 4 - recursing
found sub folder with id: 5 - recursing
deleting folder with id: 5 - success
deleting folder with id: 4 - success
deleting folder with id: 2 - success

Checking whats now in the table:

root@zim:~/testing# mysql -p -e 'select * from files' testing;
Enter password:
+----+----------+---------------+
| id | folderid | description   |
+----+----------+---------------+
|  1 |        0 | root folder   |
|  3 |        1 | second folder |
+----+----------+---------------+