Matthew Davis Matthew Davis - 3 months ago 10
MySQL Question

PHP move file using part of a known file name

I have a directory full of images (40,000 +) that I need sorted. I have designed a script to sort them into knew proper directories, however, I am having issues with the file name.

The images urls with the id they belong to are stored in a database, and I am using the database in conjunction with the script to sort the images.

My Problem:

The image url's in the database are shortened. An example of such corresponding images are like this:

dsc_0107-367.jpg
dsc_0107-367-5478-2354-0014.jpg


The first part of the filenames are the same, but the actual file contains more info. I'd like a way to move the file from the database with the known part of the file name.

I have a basic code:

<?php

$sfiles = mysqli_query($dbconn, "SELECT * FROM files WHERE gal_id = '$_GET[id']");

while($file = mysqli_fetch_assoc($sfiles)){

$folder = $file['gal_id'];
$fileToMove = $file['filename'];

$origDir = "mypath/to/dir";

$newDir = "mypath/to/new/dir/$file['gal_id']";

mkdir "$newDir";

mv "$fileToMove" "$newDir";

}


Im just confused on how to select the file based on the small part from the database.

NOTE: It's not as simple as changing the number of chars in the db, because the db was given to me from an external site thats been deleted. So this is all the data I have.

Answer

PHP can open files using the function glob() . Glob searches your server, or specified directory, for any files containing a "match" to a pattern you specify.

Using glob() like this will pull your images from a partial name.

  1. Run this query separate from the second:

    $update = mysqli($dbconn, "UPDATE files SET filename = REPLACE(filename, '.info', ''));
    

filename should be the column in your database that contains the list of images. The reason we are removing the .jpg from the db columns is if your names are partial, the .jpg may not match with the given name in your directory. With it removed, we can search solely for the pattern of the name.

  1. Build the query to select and move the folders

    $sfiles = mysqli_query($dbconn, "SELECT * FROM files");

    while($file = mysqli_fetch_assoc($sfiles)){

    $fileToMove = $file['filename'];

    //because glob outputs the result set into an array, we will use foreach to run each result from the array individually.

    foreach(glob("$fileToMove*") as filename){

    echo "$filename
    "; // I'm echoing this out to see that the results are being run one line at a time and to confirm the photo's are matching the pattern.

    $folder = $file['gal_id']; // pulling the id from the db of the gallery the photo belongs to. This will specify which folder to move the pic to. Replace gal_id with the name of your column.

    $newDir = $_SERVER['DOCUMENT_ROOT']."/admin/wysiwyg/kcfinder/upload/images/gallery/old/".$folder;

    copy($filename,$newDir."/".$filename); //I would recommend copy rather than move. This will leave the original photo in its place. This measure is to ensure the photo made it to the new directory so you don't lose it. You could go back and delete the photos after if you'd prefer.

    }

    }