<?php

# formats a sql INSERT or UPDATE syntax from a 
# array where $array[column]=value
function sql_syntax($table, $array, $update=null)
{
	if (isset($update))
	{
		$string= "UPDATE $table SET ";
		foreach (array_keys ($array) as $a)
			$string="$string $a = '".$array[$a].'\', ';
		$string=substr($string,0,strlen($string)-2);
		$string="$string WHERE ID = $update ";
		return $string;
	}
	else
	{
		$string = "INSERT INTO $table (";
		foreach (array_keys ($array) as $a)
			$string = $string.$a.', ';
		$string=substr($string,0,strlen($string)-2);
		$string = $string.') VALUES (';
		foreach ($array as $a)
			$string = $string."'".$a.'\', ';
		$string=substr($string,0,strlen($string)-2);
		$string = $string.')';
		return $string;
	}
}
	

function clean_database()
{
	echo '<hr color=#000000 size=10>';
	echo '[<b>Cleaning database</b>]<br><br>';
	flush();
	cleanitem('track_index','artistID','track_artist');
	cleanitem('album_index','artistID','album_artist');
	cleanitem('album_index','ID','genre_index', 'albumID');
	cleanitem('genre_index','genreID','genre_genre');
	cleanitem('album_index','ID','album_image', 'albumID');
	cleanitem('album_index','ID','album_docs', 'albumID',true);
	
	cleanitem('album_index','ID','track_index', 'albumID',true);
	
	#there is no point in having emplty albums
	cleanitem('track_index','albumID','album_index');

	echo "checking playlists...<br>";
	check_playlists();

	global $config;
}


# Deletes from table2 if a value in table2.col2 is not found in table2.col2
function cleanitem($table1, $col1, $table2, $col2='ID', $not=false)
{
	$count=0;
	print "CLEANING :: $table1.$col1   : $table2 ..";
	flush();

	set_time_limit(TIMEOUT);
	$query=db_query("SELECT DISTINCT $col1 FROM $table1");
	$table1a= array();
	while ( list ($table1a[])=mysql_fetch_row($query));

	set_time_limit(TIMEOUT);
	$query=db_query("SELECT DISTINCT $col2 from $table2");
	$table2a= array();
	while ( list ($table2a[])=mysql_fetch_row($query));

	set_time_limit(TIMEOUT);
	foreach ($table2a as $mitch)
		if (!in_array($mitch,$table1a))
		{
			#echo "$mitch";
			db_query("DELETE FROM $table2 WHERE $col2 = $mitch"); 
			$count++;
		}

	print ".finished. Erased ".$count." rows <br>";
	flush();

}

function isdate($date)
{
	if ( ereg("^([0-9]{4})-([0-9]{1,2})-([0-9]{1,2})$", $date, $regs) && checkdate($regs[2], $regs[3], $regs[1]) )
		return TRUE;
	else
		return FALSE;
}



# --------------------------------------------------------------------------------------------------------------
# --------------------------------------------------------------------------------------------------------------
# check is $val exists in $col of $table. 
# If true, return the ID field of the row $val was found on.
# If false, create a new row in $col and then return the ID if that row.
function getID($table, $col, $val, $update=false)
{
	$eval=mysql_escape_string($val);
	$query=db_query("SELECT ID, $col FROM $table WHERE $col = '$eval'");
	list ($result, $nval)=mysql_fetch_row($query);
	if (mysql_num_rows($query)>0)
	{
		if ($update && $val!=$nval)
			db_query("UPDATE $table SET $col = '$eval' WHERE ID = '$result'");
		return $result;
	}
	else
	{
		db_query("INSERT INTO $table ($col) VALUES('$eval')");
		$query=db_query("select LAST_INSERT_ID()");
		list ($result)=mysql_fetch_row($query);
		return $result;
	}
	
}


?>