PHP: Tricky Nested Loop with select [duplicate]

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 |
+----+----------+---------------+