Using PHP to rename files based on MYSQL query

[jpaek1]

Hello,

 

I am a novice with PHP so what I am trying to do may be easy to do, but I just don't understand how to get it into PHP script to work properly as I mostly work with Javascript.

 

I have a directory on a server with files in it.  I have a database on the same server with the same number of entries that there are files in the directory.  Essentially, what I want to do is rename all of the files in the directory based on the results of the MYSQL query.  I don't really need help with the query itself or the rename function, but rather how to get it to work together using while/for/foreach loops or such.

 

The problem I'm having is how to tell the script to change each file name only once per mysql row return then do it until there are no more entries.

 

The current code I have that works (but doesn't do what I want) is:

 

$dir = '/home/test/';
$files1 = scandir($dir);

$res = mysql_query("SELECT number from test where date = '2012-07-26'  group by number order by date, time");
while($row = mysql_fetch_assoc($res)){
$name = $row['number'];
foreach($files1 as $file1) {
	if (!(is_dir($file1))) {
		rename('/home/test'.$file1, '/home/test'.$name.'.JPG');
	}
}
}

 

Again, I know the code is wrong, just looking for pointers on doing it the right way.

 

Thanks.

[Christian F.]

I think you're looking for continue;, just add that below the rename and you should be good to go.

[Mahngiel]

nvm

[Christian F.]

Ah... Re-reading the code I see I missed out on some details, sorry about that.

 

Considering what I previously missed, it seems like you want to take the first element from the $files1 array, rename it to the first result from the MySQL query. Then take the next element, rename it to the next number, and so forth.

If that's the case, then you'll want to look at  array_shift () instead of foreach () and continue;.

[jpaek1]

Ah... Re-reading the code I see I missed out on some details, sorry about that.

 

Considering what I previously missed, it seems like you want to take the first element from the $files1 array, rename it to the first result from the MySQL query. Then take the next element, rename it to the next number, and so forth.

If that's the case, then you'll want to look at  array_shift () instead of foreach () and continue;.

 

Ok I'll give it a look - thanks!

[Christian F.]

You're welcome.

[jpaek1]

Well, its kinda working now.  It seems to be numbering in a random order though.

 

Here's my code:

 

$dir = '/home/test';
$files1 = scandir($dir);

$res = mysql_query("SELECT number from test where date = '2012-07-26' group by number order by date, time LIMIT 5");
while($row = mysql_fetch_assoc($res)){
$name = $row['number'];
$ret1 = array_shift($files1);
		if (!(is_dir($ret1))) {
		rename('/home/test/'.$ret1, '/home/test/'.$name.'.JPG');
		}

}

 

Here's my query result order:

0029719

0052619

0029725

0040890

0029724

 

However, the first 2 numbers are not being used in the rename - its renaming the first 3 pictures in the directory incorrectly to the last 3 numbers, completely ignoring the first two numbers from the query.  It does the same thing with or without limit - the first 2 numbers from the query are being skipped completely, then the renaming starts on the 3rd number from the query result and it done on the first picture.

 

Thanks in advance for responses.

[jpaek1]

Ok I see the problem now.  Its an issue with the is_dir if check.  The first two "file names" coming from the scandir result are . and .. which are directories.  Just gotta figure out how to do this if check outside the while loop correctly so that the renaming starts with the actual pictures and not the directories.

[Christian F.]

do {array_shift () } while (is_dir ());

[jpaek1]

Ok seems to be working.  I haven't done much testing other than having the exact same number of records and pictures so I will probably add some other checks in there in case the total amount of records doesn't match the total amount of pictures.  But here's the code as is:

 

$dir = '/home/test/';

$files1 = scandir($dir);

$res = mysql_query("SELECT number from test where date = '2012-07-26' group by number order by date, time");

$count = count($files1);

do {
$ret1 = array_shift($files1);
if (is_dir($ret1) ) {
	echo "$ret1 is a directory";
	$count--;
}
else {
	        $row = mysql_fetch_assoc($res);
		$name = $row['number'];
		rename('/home/test/'.$ret1, '/home/test/'.$name.'.JPG');
		echo "ret1 is $ret1 and name is $name";  //debugging to check which were being changed and to what number
		$count--;	
}
}
while ($count >= 1); 

 

Thanks again for the help!

[Christian F.]

Here's how I'd do it, as your code is somewhat wasteful:

$dir = '/home/test';
$files1 = scandir ($dir);

$res = mysql_query ("SELECT number from test where date = '2012-07-26' group by number order by date, time LIMIT 5");
while ($row = mysql_fetch_assoc ($res)) {
$name = $row['number'];
do {
	$ret1 = array_shift ($files1);	
} while (is_dir ($ret1));

rename ('/home/test/' . $ret1, '/home/test/' . $name . '.JPG');
}

As you see, a bit simpler and quite a lot easier to read and maintain.

[Psycho]

I think it would make more sense to LIMIT the query based upon the number of files in the directory. You then know you would never have more DB results than files. Then only loop through the DB results. If you have more files than DB results, then those remaining files would not get renamed. Also, I would use glob() to get the files.

 

Here's my approach whic I think is simpler

$dir = 'home/test/';
$date = '2012-07-26';

$files = glob($dir.'*')                   //Get the contents of directory
$files = array_filter($files, 'is_file'); //Remove directories from array
$fileCount = count($files);               //Set count of files

//Run query to get records up to the number of files
$query = "SELECT number FROM test WHERE date = '{$date}' GROUP BY number ORDER BY date, time LIMIT {$fileCount}";
$result = mysql_query($query);

while($oldName=array_shift($files) && $row=mysql_fetch_assoc($result))
{
    //Create new file name
    $newName = "{$dir}{$row['number']}.jpg";
    rename($oldName, $newName);
}

 

[jpaek1]

Thanks, I will try both suggestions out.  This was just to get things working more than anything.  In our system, I sure hope the number of pictures and database entries are always the same otherwise we have a problem and I wouldn't want anything renamed at all.  I had originally put in the limit so I would only be testing with 5 files at once instead of having to copy/paste/upload again over and over.  I will be putting in a check and throwing an error if the number of files does not match the number of entries in the database so as to alert us of an issue.

 

Thanks again!  Both suggestions look much more efficient than my current code and I'll try playing with them tomorrow.

[Psycho]

In our system, I sure hope the number of pictures and database entries are always the same otherwise we have a problem and I wouldn't want anything renamed at all.

 

Then why didn't you say so. That's easy enough:

$dir = 'home/test/';
$date = '2012-07-26';

//Get the contents of directory & remove folders
$files = array_filter(glob($dir.'*'), 'is_file');

//Run query to get records
$query = "SELECT CONCAT('{$dir}', number, '.jpg') as newName
         FROM test WHERE date = '{$date}' GROUP BY number ORDER BY date, time";
$result = mysql_query($query);

if(mysql_num_rows($result) != count($files))
{
    die('The number of files and DB records do not match');
}
while($oldName=array_shift($files) && $row=mysql_fetch_assoc($result))
{
    //Rename the file
    rename($oldName, $row['newname']);
}

 

Also added a change to format the new file name within the query!

[jpaek1]

In our system, I sure hope the number of pictures and database entries are always the same otherwise we have a problem and I wouldn't want anything renamed at all.

 

Then why didn't you say so. That's easy enough:

$dir = 'home/test/';
$date = '2012-07-26';

//Get the contents of directory & remove folders
$files = array_filter(glob($dir.'*'), 'is_file');

//Run query to get records
$query = "SELECT CONCAT('{$dir}', number, '.jpg') as newName
         FROM test WHERE date = '{$date}' GROUP BY number ORDER BY date, time";
$result = mysql_query($query);

if(mysql_num_rows($result) != count($files))
{
    die('The number of files and DB records do not match');
}
while($oldName=array_shift($files) && $row=mysql_fetch_assoc($result))
{
    //Rename the file
    rename($oldName, $row['newname']);
}

 

Also added a change to format the new file name within the query!

 

Tried this and for some reason, at the rename, the $oldName variable is returning a value of 1.  I spit out the array into a text file just to make sure and it does indeed have the full path listed.  The error I get when running is:

PHP Warning:  rename(1,/home/test/0026524.JPG): No such file or directory in /home/test/pictest2.php on line 33

 

Not sure why.

[Psycho]

Well, it worked for me. You state that the array does have the values you expect. But, apparently, the extracted array value using array_shift() is producing a '1'. Are you sure you implemented the code correctly? There's no logical reason I can think of that that would happen. Where's the exact code you implemented?

[jpaek1]

Well, it worked for me. You state that the array does have the values you expect. But, apparently, the extracted array value using array_shift() is producing a '1'. Are you sure you implemented the code correctly? There's no logical reason I can think of that that would happen. Where's the exact code you implemented?

 

$dir = '/home/test/';
$date = '2012-07-26';

//Get the contents of directory & remove folders
$files = array_filter(glob($dir.'*'), 'is_file');
$f = fopen("file.txt", "w");
fwrite($f, print_r($files, true));
fclose($f);

//Run query to get records
$query = "SELECT CONCAT('{$dir}', number, '.JPG') as newName FROM test WHERE date = '{$date}' and GROUP BY number ORDER BY date, time";
$result = mysql_query($query) or die(mysql_error());

if(mysql_num_rows($result) != count($files))
{
    die('The number of files and DB records do not match');
}
while($oldName=array_shift($files) && $row=mysql_fetch_assoc($result))
{
    //Rename the file
    rename($oldName, $row['newName']);
}

 

The newName works properly and has the correct info (as seen in the error I copy/pasted in the previous post).  But $oldName returns a '1' value.  The only difference should be where I added the part to spit out the array to a text file.

 

But don't fret over it too much - I was able to take the while loop out and replaced it with a do while loop and used a counter like I had previously (and didn't use the $oldName portion at all) and its working, which is pretty much what I needed at this point.

 

Thanks again for the help.