Location: PHPKode > projects > Axis Groupware > musiclib/unused/tunez/admin_updateDb.php
<?
# updateDB.php
#
# Dumps all filenames in table to file, does a dir-list of mp3dirs and compares the lists with diffs. 
# Then the table gets updated.

include ("tunez.inc.php");
include ("tunez_admin.inc.php");

$nrOfSongsBeforeUpdate = nrOfSongs();

$title = "Updating Database";
include ("header.inc.php");

# get file listing from database
$kweerie = "SELECT * from songs ORDER BY filename";
$result = mysql_db_query("tunez", $kweerie);

$fp = fopen("/tmp/filelistFromDb.txt", "w");

while ($row = mysql_fetch_array($result))
{
	$filename = $row["filename"];
	fwrite ($fp, $filename."\n");
}

fclose ($fp);

mysql_free_result($result);

# get file listing from filesystem

system ("touch /tmp/filelistFromFs.txt"); 

for ($i=0 ; $i <= sizeOf($mp3dir) - 1 ; $i++)
{
    $cmd = "find " . $mp3dir[$i] . " |grep -i mp3$ >>/tmp/filelistFromFs.txt";
//	echo $cmd;
    system ($cmd);
}


# sort both files 

system ("sort /tmp/filelistFromFs.txt >/tmp/sortedFsList.txt");
system ("sort /tmp/filelistFromDb.txt >/tmp/sortedDbList.txt");

# get the difference between the two files

system ("diff /tmp/sortedDbList.txt /tmp/sortedFsList.txt >/tmp/differences.txt");

$fp = fopen("/tmp/differences.txt", "r");

    $genrename =array(
        "Blues", "Classic Rock", "Country", "Dance", "Disco", "Funk",
        "Grunge", "Hip-Hop", "Jazz", "Metal", "New Age", "Oldies", "Other",
        "Pop", "R&B", "Rap", "Reggae", "Rock", "Techno",
        "Industrial", "Alternative", "Ska", "Death Metal", "Pranks",
        "Soundtrack", "Euro-Techno", "Ambient", "Trip-Hop", "Vocal",
        "Jazz+Funk", "Fusion", "Trance", "Classical", "Instrumental",
        "Acid", "House", "Game", "Sound Clip", "Gospel", "Noise",
        "AlternRock", "Bass", "Soul", "Punk", "Space", "Meditative",
        "Instrumental Pop", "Instrumental Rock", "Ethnic", "Gothic",
        "Darkwave", "Techno-Industrial", "Electronic", "Pop-Folk",
        "Eurodance", "Dream", "Southern Rock", "Comedy", "Cult", "Gangsta",
        "Top 40", "Christian Rap", "Pop/Funk", "Jungle", "Native American",
        "Cabaret", "New Wave", "Psychadelic", "Rave", "Showtunes", "Trailer",
        "Lo-Fi", "Tribal", "Acid Punk", "Acid Jazz", "Polka", "Retro",
        "Musical", "Rock & Roll", "Hard Rock", "Folk", "Folk/Rock",
        "National Folk", "Swing", "Bebob", "Latin", "Revival", "Celtic",
        "Bluegrass", "Avantgarde", "Gothic Rock", "Progressive Rock",
        "Psychedelic Rock", "Symphonic Rock", "Slow Rock", "Big Band",
        "Chorus", "Easy Listening", "Acoustic", "Humour", "Speech", "Chanson",
        "Opera", "Chamber Music", "Sonata", "Symphony", "Booty Bass",
        "Primus", "Porn Groove", "Satire", "Slow Jam", "Club", "Tango",
        "Samba", "Folklore", "Ballad", "Power Ballad", "Rhythmic Soul",
        "Freestyle", "Duet", "Punk Rock", "Drum Solo", "A capella",
        "Euro-House", "Dance Hall"
    );

while ($line = fgets($fp, 1024)) 
{
    $songtitle = "";
    $year = "";
    $genre = "";
    $gen = "255";
    $album = "";
    $artist = "";
    
  	if (substr($line, 0, 1) == '>') 
	{
		$filename = substr($line, 2, -1);
		echo "Processing $filename <BR>";
		if (is_dir($filename))
		{
			echo "$filename is a directory<br>";
			$doNotAdd = "true";
		}

		# Now we're gonna see if the file has got an ID3 tag, if not we'll make something up

		$filehandle = fopen("$filename","r");
		$size = filesize("$filename");
		$seek = fseek($filehandle, $size-128);
		$tag = fgets($filehandle,128);
   
   		if (eregi("tag", $tag) && ($useTags == true)) 
		{
			echo $tag;
			# Extract the tag data and close the file
			# This is the correct order... 

			$songtitle = trim(substr($tag,3, 30));
			$artist	= trim(substr($tag,33, 30));
			$album = trim(substr($tag,63, 30));
			$year =  substr($tag,93, 4);
			$comment = trim(substr($tag,97,30));

			# Grab last char for the Genre
			# This can get confused for EOF with fgets
			$seek = fseek($filehandle, $size-1);
			$gen = ord(fgetc($filehandle));
			$genre = $genrename[$gen];

			echo "<b>Artist:</b> $artist <br>";
			echo "<b>Songtitle:</b> $songtitle <br>";
			echo "<b>Album:</b> $album <br>";
			echo "<b>Jaar:</b> $year <br>";
			echo "<b>Genre:</b> $genre <br>";

			fclose($filehandle);
		}

		else
		{		
			$filenameSplitted = split("/", $filename);
			$filenameWithoutPath = $filenameSplitted[ sizeOf($filenameSplitted)-1];

			$splittedSong = split(" - ", $filenameWithoutPath);

			if (sizeOf($splittedSong) == 1)
			{
				# This happens when the filename is not properly formatted, try to split song with just -
				$splittedSong = split("-", $filenameWithoutPath);
			}

			$artist = $splittedSong[0];
			$songtitle = substr($splittedSong[sizeOf($splittedSong)-1], 0, -4);
			$gen = '255'; //genre unknown, force 255

			echo "<b>Artist:</b> $artist <br>";
			echo "<b>Songtitle:</b> $songtitle <br>";

		}

		if (!isset($doNotAdd))
		{
			# Now we can safely add it to the database

			$kweerie = "INSERT INTO songs VALUES ('','" . addslashes($artist) . "','" . addslashes($songtitle) . "','" . addslashes($filename) . "','','1','" . addslashes($album) . "','$year', '$gen')";
			echo "\n$kweerie\n";
		
			mysql_db_query("tunez", $kweerie);
			if (mysql_error()) { echo mysql_error();}
		}
	}

  	if (substr($line, 0, 1) == '<') 
	{
		$filename = substr($line, 2, -1);
		echo "Processing $filename <BR>";

		$kweerie = "DELETE FROM songs WHERE filename='" . addslashes($filename) . "'";
		echo "$kweerie\n";
		mysql_db_query("tunez", $kweerie);
		if (mysql_error()) { echo mysql_error();}
		
	}
	echo "<hr>";
}

fclose($fp);
$nrOfSongsAfterUpdate = nrOfSongs();

# delete the temp files
system ("rm /tmp/sortedDbList.txt /tmp/sortedFsList.txt /tmp/differences.txt /tmp/filelistFromFs.txt /tmp/filelistFromDb.txt");

showBox ("Result of update", "Before the update there where $nrOfSongsBeforeUpdate songs in the database.<BR>Now there are $nrOfSongsAfterUpdate songs in the database");

include ("footer.inc.php");
?>
Return current item: Axis Groupware