Reading contents of a folder and putting the filenames into a db

[bradholland]

Hey Guys,

 

I want to be able to upload a large amount of mp3's to a private area on our website and have a script that will scan the folder for all the filenames and then create a record in the db of all the filenames. It is to help with creating the playlist for my friends radio show. To manually insert all the filenames each month woudl be a nightmare!

I have been messing with a partial script I found on the web, and it kind of does something but not what I want it to.

 

heres the db

 

id  int(4)       

title varchar(250)  

mp3 varchar(250)  

sort int(4)  

visible int(1)  

added datetime

 

heres the script i am trying to use:

 <?
$listDir = array();
$dir = "./music";
         if($handler = opendir($dir)) {
             while (($sub = readdir($handler)) !== FALSE) {
                 if ($sub != "." && $sub != ".." && $sub !=  
"Thumb.db") {
                     if(is_file($dir."/".$sub)) {
                         $listDir[] = $sub;
                     }elseif(is_dir($dir."/".$sub)){
                         $listDir[$sub] = $this->ReadFolderDirectory($dir."/".$sub);
                     }
                 }
             }
             closedir($handler);
         }

		$db = mysql_connect("localhost","gfab_ml","xxxxxxx");
		mysql_select_db("gfab_ml",$db);
		$colors=serialize($listDir); //takes the data from a post  
		$sql="INSERT INTO ml_music (ID, title, added)  
VALUES('','$colors','')";
		$result = mysql_query($sql) or die (mysql_error());



		?>

 

it does insert the values into the db but what it does is creates 1 record and spits all the results in a really weird way like this. into the title field.

 

a:18:{i:0;s:23:"14 Mr Magic Premier.mp3";i:1;s:23:"track1.mp3";i:2;s:15:"track2.jpg";i:3;s:15:"track3.fxp";i:4;s:12:"track4.mp3";i:5;s:7:"track5.mp3";i:6;s:7:"track6.jpg";i:7;s:7:"track7.mp3";i:8;s:12:"track8.mp3"

 

 

Could anyone please help me to make it work? im a bit of a noob. but i do like to tinker!

 

[wildteen88]

This

a:18:{i:0;s:23:"14 Mr Magic Premier.mp3";i:1;s:23:"track1.mp3";i:2;s:15:"track2.jpg";i:3;s:15:"track3.fxp";i:4;s:12:"track4.mp3";i:5;s:7:"track5.mp3";i:6;s:7:"track6.jpg";i:7;s:7:"track7.mp3";i:8;s:12:"track8.mp3"

Is the result of an array being serialized, which is the contents of the $listDir variable.

 

If you want to insert each track as a new entry into your database you don't want to be serializing your array. You'll want to loop through your array instead. So instead of

			$colors=serialize($listDir); //takes the data from a post  
		$sql="INSERT INTO ml_music (ID, title, added)  VALUES('','$colors','')";
		$result = mysql_query($sql) or die (mysql_error());

 

You'd want to be dynamically generating your SQL Query. An example would be

$sql = 'INSERT INTO ml_music  (title, added) VALUES';

foreach($listDir as $mp3_entry)
    $sql .= " ('$mp3_entry', NOW()),";

$sql = substr($sql, 0, strlen($sql)-1);

echo "Generated SQL Query:<br />$sql";
$result = mysql_query($sql) or die (mysql_error());

[bradholland]

Thankyou very very much for the help and explanation. i love the NOW() thing too that works a treat. I didnt know about that.

 

Something I have just thought is that I might need to run the script more than once, in which case it would add all the tracks to the db again with new id's etc.

If i upload a couple of more files to the folder and want the script to add those, should I just change the "insert into" to "update" or would that not work?

I guess the other way is to somehow check to see if the same filename already exists in the table?

 

Thanks for your help and sorry for being such a noob.

Brad

[wildteen88]

What you'll want to do then is first get all filenames from your database and add them to an array. This array will be used latter to see if the track has already been inserted into the database.

$sql = 'SELECT title FROM ml_music';
$result = mysql_query($sql);

$mp3s = array();
while(list($track) = mysql_fetch_row($result))
    $mp3s[] = $track;

 

Now change this

foreach($listDir as $mp3_entry)
    $sql .= " ('$mp3_entry', NOW()),";

 

to

foreach($listDir as $mp3_entry)
{
    // check that mp3 hasn't beed added already.
    if(!in_array($mp3_entry, $mp3s))
        $sql .= " ('$mp3_entry', NOW()),";
}

Here were checking to see if the music track hasn't been added to the database yet.