Create an array of missing userId's previously removed from mysql database

Question

Okay. I've got a mysql database full of users and they're corresponding information. They are assigned an auto incremented userId and directory in a users folder when account is created. Through out the past few months many of these rows have been deleted (accounts removed) from the database. My problem is when the row is removed from the database the directory for that user remains. I now have possibly thousands of folders for users that will never be used. Ive got an array of useless directories. Problem I have is there is a difference of 16 that will be deleted. Where is this difference coming from??

<?php
ini_set('display_errors',1);
$pdo=new PDO('mysql://hostname=localhost;dbname=channel1_db', 'channel1_user', 'test123');
$data=$pdo->query('select user_id from accounts');
$data=$data->fetchAll(PDO::FETCH_ASSOC);
$alterIds = array();

    foreach ($data as $id) {
        $newName = "user-".$id['user_id'];
        $alterIds[] = ($newName);
    }

$idCount= count($alterIds);

$iterator = new DirectoryIterator(dirname(__FILE__));
$user_directories = array();

    foreach ($iterator as $fileinfo) {
        $user_directories[] = $fileinfo->getFilename();
    } 

array_shift($user_directories);//remove "."
array_shift($user_directories);//remove ".."
$fileCount = count($user_directories);
$diff = $fileCount-$idCount;
$useless_directories = array_diff($user_directories, $alterIds);
$uselessCount = count($useless_directories);
$unhandled = $uselessCount-$diff;

        //display totals
        echo("".$fileCount." Total Files");
        echo("<br>");
        echo("".$idCount." Total Ids");
        echo("<br>");
        echo("".$diff." projected useless");
        echo("<br>");   
        echo("".$uselessCount." Useless Files Results");
        echo("<br>");
        echo("".$unhandled." Difference between projected and results");
        echo("<br>");
        ?>

Reports... 19672 Total Files, 11038 Total Ids, 8634 Projected Useless, 8652 Useless Files Results, 18 difference between projected and actual

assuming that you names the directories with your users id.. cant you create an array of the number of directories and an array of the number of ids then remove the ids found in array b using array a. then you can create a while loop to remove whats left.. you can check out the first answer on this question to see how to remove the ids of one array using another array: http://stackoverflow.com/questions/10589921/remove-elements-of-one-array-if-it-is-found-in-anotherl you can use scandir('PARENT DIRECTORY'); to get the list of directories. — edwinj, Feb 07 '16 at 18:05

makmesh · Accepted Answer · 2016-02-07T16:08:33.557

0

// scan the user director
$directories = scandir('users');

// connect to the database
$mysqli = new mysqli('localhost', 'root', '', 'test');

$user_directories = array();

// get user directories
$query = 'SELECT directory FROM user';

$results = $mysqli->query($query);

// populate the array with user directories
while ($row = $results->fetch_object()) {
    $user_directories[] = $row->directory;
}

 $useless_directories = array();

// loop through the directories
foreach ($directories as $directory) {
// check is a directory is not in the user directories retrieved from the database
    if (!in_array(trim($directory), $user_directories)) {
        // check if its a directory, scandir() scanss both directories and files
        if (is_dir('users/' . $directory)) {
            // populate the useless_directories
            $useless_directories[] = $directory;
        }
    }
 }

 foreach ($useless_directories as $useless_directory) {
     // create an array of files in the directories
     $files = scandir('users/' . $useless_directory);
     foreach ($files as $file) {
         // delete any files in the directory
         unlink('users/' . $useless_directory . '/' . trim($file));
    }
    // remove the directory
    rmdir($useless_directory);
 }

edited Feb 07 '16 at 16:08

answered Feb 07 '16 at 15:37

makmesh

30
3

1

Note - according to the docs for [`rmdir()`](http://php.net/manual/en/function.rmdir.php) - `... The directory must empty, ...`, so if there are any files in the directory, `unlink()` needs to be used first. – Sean Feb 07 '16 at 15:43
I'm using pdo btw. Also question? the users directory is in my public_html dir. Mysql can access any directory? – Robert Goodrick Feb 07 '16 at 17:11
@RobertGoodrick PHP accesses folders, not MySQL/PDO. – Funk Forty Niner Feb 07 '16 at 17:28
My problem to this point is here...$query = 'SELECT directory FROM user'; $results = $mysqli->query($query); // populate the array with user directories while ($row = $results->fetch_object()) { $user_directories[] = $row->directory; } – Robert Goodrick Feb 07 '16 at 17:55
'SELECT directory FROM user' The table only contains a userId and the dir on account creation is mkdir("user - ".$userId.) i.e.(public_html/users/user-2095) – Robert Goodrick Feb 07 '16 at 17:57
1

@RobertGoodrick You would need to edit your question with the code you're using in trying to achieve what you want. You shouldn't be dropping code in comments. That belongs in your question. – Funk Forty Niner Feb 07 '16 at 18:07
Okay at this point the array comparasion is returning $useless_directories with all of the directories including the ones in userId results – Robert Goodrick Feb 07 '16 at 19:04

Create an array of missing userId's previously removed from mysql database

1 Answers1