Location: PHPKode > projects > Axis Groupware > musiclib/inc/db_functions.inc.php
<?php
function times_played($mode,$id) 
{
	global $musiclib;

	$GLOBALS['musiclib_time_played'] = createobject('phpgwapi.db');
	$GLOBALS['musiclib_time_played']->Database = $GLOBALS['phpgw_info']['server']['db_name'];
	$GLOBALS['musiclib_time_played']->Host 	 = $GLOBALS['phpgw_info']['server']['db_host'];
	$GLOBALS['musiclib_time_played']->User 	 = $GLOBALS['phpgw_info']['server']['db_user'];
	$GLOBALS['musiclib_time_played']->Password = $GLOBALS['phpgw_info']['server']['db_pass'];
	$GLOBALS['musiclib_time_played']->connect();

	if ($mode == 'album') 
	{
		$query = "UPDATE ".$musiclib['tables']['albums']
				." SET times_album_played=times_album_played+1 WHERE id=".$id;

		$GLOBALS['musiclib_time_played']->query($query,__LINE__,__FILE__);
	
		$query = "UPDATE ".$musiclib['tables']['songs']
				." SET times_played=times_played+1 WHERE album_id=".$id;

		$GLOBALS['musiclib_time_played']->query($query,__LINE__,__FILE__);
	} 
	else 
	{
		$query = "UPDATE ".$musiclib['tables']['songs']
				." SET times_played=times_played+1 WHERE id=".$id;

		$GLOBALS['musiclib_time_played']->query($query,__LINE__,__FILE__);
	}


}

function construct_albumkey_select($condition) 
{
	global $musiclib;
	
	$query = "SELECT DISTINCT ".$musiclib['tables']['songs'].".id,album_title,artist_name,"
			.$musiclib['tables']['songs'].".album_id,".$musiclib['tables']['songs'].".artist_id"
			." FROM ".$musiclib['tables']['albums']
			." RIGHT JOIN ".$musiclib['tables']['songs']
		 	." ON ".$musiclib['tables']['albums'].".id=".$musiclib['tables']['songs'].".album_id "
		 	." LEFT JOIN ".$musiclib['tables']['artists']
		 	." ON ".$musiclib['tables']['artists'].".id=".$musiclib['tables']['albums'].".artist_id "
		 	." ".$condition; 

#	echo $query;
	return $query;
}
function construct_song_select($condition) 
{
	global $musiclib;

	$query = "SELECT "
			.$musiclib['tables']['songs'].".id,"
			.$musiclib['tables']['songs'].".album_id,"
			.$musiclib['tables']['albums'].".artist_id,"
			."artist_name,album_title,song_title,track,file_extension"
		 	.",path,filename,repository"
			." FROM ".$musiclib['tables']['albums']
            ." RIGHT JOIN ".$musiclib['tables']['songs']
            ." ON ".$musiclib['tables']['albums'].".id=".$musiclib['tables']['songs'].".album_id "
            ." LEFT JOIN ".$musiclib['tables']['artists']
            ." ON ".$musiclib['tables']['artists'].".id=".$musiclib['tables']['albums'].".artist_id "
		 	.$condition;

#	echo $query;
	return $query;
}

function construct_album_select($condition) 
{
	global $musiclib;
	
	$query = "SELECT ".$musiclib['tables']['albums'].".id,artist_id"
			.",artist_name,album_title,year,genre_id,comment,times_album_played"
			." FROM ".$musiclib['tables']['artists']
		 	." RIGHT JOIN ".$musiclib['tables']['albums']
		 	." ON ".$musiclib['tables']['artists'].".id=artist_id "
		 	.$condition; 
	
	return $query;
}
# This gets a list of all user's playlists
function construct_user_playlists_select($condition) 
{
	global $musiclib;
	
	$query = "SELECT * FROM phpgw_accounts "
			."RIGHT JOIN ".$musiclib['tables']['playlists']
			." ON account_id=owner ".$condition
			." GROUP BY account_id";
	return $query;
}
# This gets a list of all user's playlists
function construct_playlists_select($condition) 
{
	global $musiclib;
	
	$query = "SELECT * FROM ".$musiclib['tables']['playlists']." ".$condition;

	return $query;
}
# This is used for getting the contents of a playlist for displaying to screen
# we want the songs that may have been deleted from _songs table
function construct_playlists_content_select($playlist_id) 
{
	global $musiclib;
	
	$query = "SELECT * FROM ".$musiclib['tables']['playlist_contents']
			." WHERE playlist_id=".$playlist_id
		 	." ORDER BY play_order,id";

	return $query;
}
# This function is used for generating a playlist to listen to based on playlist contents
# we do not want the songs that have been deleted from _songs table
function construct_playlist_song_select($playlist_id) 
{
	global $musiclib;

		$query = "SELECT "
			 	.$musiclib['tables']['playlist_contents'].".id"
			 	.",path,filename,repository"
				.",playlist_id,play_order,"
				.$musiclib['tables']['playlist_contents'].".artist_name,"
				.$musiclib['tables']['playlist_contents'].".album_title,"
				.$musiclib['tables']['songs'].".track,song_id,song_title,"
				.$musiclib['tables']['songs'].".file_extension"
				." FROM "
				.$musiclib['tables']['playlist_contents']
				." LEFT JOIN "
				.$musiclib['tables']['songs']
		 		." ON "
		 		.$musiclib['tables']['playlist_contents'].".song_id=".$musiclib['tables']['songs'].".id"
			 	." WHERE ".$musiclib['tables']['playlist_contents'].".playlist_id=".$playlist_id
				." ORDER BY play_order,".$musiclib['tables']['playlist_contents'].".id";

	return $query;
}

function construct_random_playlist_song_select($condition) 
{
	global $musiclib;

		$query = "SELECT "
			 	.$musiclib['tables']['playlist_contents'].".id"
			 	.",path,filename,repository"
				.",playlist_id,play_order,"
				.$musiclib['tables']['playlist_contents'].".artist_name,"
				.$musiclib['tables']['playlist_contents'].".album_title,"
				.$musiclib['tables']['songs'].".track,song_id,song_title,"
				.$musiclib['tables']['songs'].".file_extension"
				." FROM "
				.$musiclib['tables']['playlist_contents']
				." RIGHT JOIN "
				.$musiclib['tables']['playlists']
		 		." ON "
		 		.$musiclib['tables']['playlist_contents'].".playlist_id=".$musiclib['tables']['playlists'].".id "
				." LEFT JOIN "
				.$musiclib['tables']['songs']
		 		." ON "
		 		.$musiclib['tables']['playlist_contents'].".song_id=".$musiclib['tables']['songs'].".id "
				.$condition;
				
	return $query;
}

function add_to_playlist($mode,$playlist_id,$array_of_songs) 
{
	global $musiclib;
	
	if ($playlist_id == 'new') 
	{

		if ($mode == 'album') 
		{ 
			$playordercount = true; 
		}
		
		$query = "INSERT INTO ".$musiclib['tables']['playlists']
				." (owner,playlist_title)"
				." VALUES (".$GLOBALS['phpgw_info']['user']['account_id'].",'New Playlist')";

		$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);

		$playlist_id = $GLOBALS['phpgw']->db->get_last_insert_id($musiclib['tables']['playlists'],'id');
	}

	$count=0;
	foreach($array_of_songs as $song_id)
	{
		$count++;
		
		$query = construct_song_select("WHERE ".$musiclib['tables']['songs'].".id=".$song_id);
		
		$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
		$GLOBALS['phpgw']->db->next_record();
		$row = $GLOBALS['phpgw']->db->Record;
	
		if ($playordercount) 
		{
			$playorder = $count;
		} 
		else 
		{
			$playorder = 0;
		}
	
		if ($row['repository'] == 2) 
		{
			$artist 	 = addslashes($row['path']);
			$album 		 = addslashes($row['filename']);
			$track 		 = '';
		} 
		else 
		{
			$artist 	 = addslashes($row['artist_name']);
			$album 		 = addslashes($row['album_title']);
			$track 		 = $row['track'];
		}
		
		$query = "INSERT INTO ".$musiclib['tables']['playlist_contents']
				." (playlist_id,play_order,artist_name,album_title,track,song_id) 
				VALUES ($playlist_id,$playorder,'$artist','$album','$track','$song_id')";
			
		$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
	}

	return $playlist_id;
}
function update_playlist($playlist_id,$id_array,$play_order) 
{
	global $musiclib;

#	print_r($id_array);
#	print_r($play_order);

	for($n=0;$n < count($id_array);$n++) 
	{
		if (is_numeric($play_order[$n]) and $play_order[$n] >= 0) 
		{
			$query = "UPDATE ".$musiclib['tables']['playlist_contents']
				." SET play_order=".$play_order[$n] 
				." WHERE id=".$id_array[$n];
			$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
		}
	}
}

function change_playlist_title($playlist_id,$playlist_title) 
{
	global $musiclib;

	$query = "UPDATE ".$musiclib['tables']['playlists']
			." SET playlist_title='".$playlist_title."'" 
			." WHERE id=".$playlist_id;
	$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
}

function modify_play_order($ids,$order) 
{
	global $musiclib;
#	echo $ids."<br>";
#	echo $order."<br>";

	$id = split(":",$ids);
	$play_order = split(":",$order);
	
	$query = "UPDATE ".$musiclib['tables']['playlist_contents']
			." SET play_order=".$play_order[1]
			." WHERE id=".$id[0];
	$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);

	$query = "UPDATE ".$musiclib['tables']['playlist_contents']
			." SET play_order=".$play_order[0]
			." WHERE id=".$id[1];
	$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
}

function remove_from_playlist($playlist_id,$array_of_songs) 
{
	global $musiclib;
	
	foreach($array_of_songs as $song_id)
	{
		
		$query = "DELETE FROM ".$musiclib['tables']['playlist_contents']
				." WHERE id=".$song_id;
		
		$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
	}
}

function delete_playlist($playlist_id) 
{
	global $musiclib;

	$query = "DELETE FROM ".$musiclib['tables']['playlist_contents']
			." WHERE playlist_id=".$playlist_id;
		
	$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
	
	$query = "DELETE FROM ".$musiclib['tables']['playlists']
			." WHERE id=".$playlist_id;
		
	$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
}

?>
Return current item: Axis Groupware