Directory File List — Eliminating all files found in a MySQL database

[k3v1n]

I'm relatively new to PHP; probably 3 months, but I'm learning rather quickly.

To put into context what I'm trying to accomplish:

 

I am part of the staff on a Roller Coaster fan site. We have a photo gallery. We keep all of our pictures in one directory, under sub folders for each amusement park. There is an SQL database table with an entry for each image. The column of the table that contains the url of the files is 'url'. There are approximatly 20,000 files in the 'pics' folder and all of its sub folders, but only around 12,000 entries in the data base table. I'm making this page to show the 8,000 files that AREN'T in the data base. I've already got a list of all of the files in the directory and its subdirectories. Now, I need to eliminate the files from the list that are in the database table.

 

When answering, assume that the database connection has already been established. We use an .inc file that connects with the database.

What I need to know is: How would I and where would I put code to eliminate all files that are found in the database?

 

Note: The variable $url is the variable that I want to check against the database.

 

Current Code:

<?php 
function getDirectory( $path = '.', $level = 0 ){ 

    $ignore = array( 'cgi-bin', '.', '..', 'rideglossary', 'rct3cspicsv2', 'rct3cspics', 'hd', 'banners', 'mysterio', 'coasterquiz', 'weeklycoastercritiqueblog', 'news', 'navigation', 'editorialsandarticles', 'dlp', 'GLA', 'geap', 'paramountparks', 'blogs', 'universalparks', 'buttons', 'staffpasses' ); 
    // Directories to ignore when listing output. Many hosts 
    // will deny PHP access to the cgi-bin. 

    $dh = @opendir( $path ); 
    // Open the directory to the handle $dh 
$int=1;
    while( false !== ( $file = readdir( $dh ) ) ){ 
    // Loop through the directory 

        if( !in_array( $file, $ignore ) ){ 
        // Check that this file is not to be ignored 
            $withextra=$path;
    $snippit = substr("$withextra", 25);
            // Just to add spacing to the list, to better 
            // show the directory tree. 
             
            if( is_dir( "$path/$file" ) ){ 
            // Its a directory, so we need to keep reading down... 
             	
                echo "<tr><td></td><td><font size=\"4\"><b><center>$file</center></b></font></td><td></td></tr>";            
	getDirectory( "$path$file/", ($level+1) ); 
                // Re-call this same function but on a new directory. 
                // this is what makes function recursive. 
             
            } 
    else { 
             	$image = $path . $file;
	$link = $snippit . $file;
	$url = substr("$link", 6);
                echo "<tr><td><center>$int</center></td><td><a href=\"$link\" target=\"_blank\"><center>$image</center></a></td><td><center>$url</center></td></tr>";
	$int++;
                // Just print out the filename 
             
            } 
         
        } 
     
    } 
     
    closedir( $dh ); 
    // Close the directory handle 

}

print("<table cellspacing=\"0\" cellpadding=\"3\" border=\"1\"><tr><td><center>Number</center></td><td><center>Image/Folder Path</center></td><td><center>Database Compliant URL</center></td></tr>");
getDirectory( "/home/coaster/public_html/pics/" );
print("</table>");
?>

 

Thanks in advance.

[k3v1n]

Anyone?

I still need help with this.

[sohaibshaheen]

As much as I can understand -- you just want to display files that are not in database but in directory.

 

To this question I know the answer: 

 

Ok: Lets start..

 

Keeping in mind the function you are using to print directory files and the fact that you have basic knowledge of PHP. This must be simple to implement.

 

Now in your display fucntion when you are about to print the file name. Use something like this

$result = mysql_query(" SELECT * FROM table WHERE image_name='$file_name' ") 
					or die(mysql_error());  

if(mysql_num_rows($result)>0){

// dont print

}else{

print file

}

 

 

It is that simple. Bye

[jcbones]

That is the simple way out, but you will have tons of overhead checking 20,000 queries.  Quite simply there is no good way of doing what you want.  My suggestion would be to take sohaibshaheen's advice, and implement that advice into a script that will enter the 8,000 file names into the database.  That way the script will only run once, and your existing scripts will take over from there.

[jmr3460]

Are you trying to remove all the files that do not have a matching name in your database?

[k3v1n]

Yes. I want a list of all the files that are in the directory but not in the database. I know anyway I do it will be resource intensive.