Location: PHPKode > projects > Music and Discography Database > music_db/index.php
<?
session_start();
require("common_funcs.php");
include('config.php.inc');
connectSQL();
if (!$ID) die("SQL connect error!");


# The Database Size
query("SELECT count(album_id) FROM album");
$pp = mysql_fetch_row($res);
$size = $pp[0];
## printing HTML header 
include('header');

function array_push_associative(&$arr) {
   $args = func_get_args();
   foreach ($args as $arg) {
       if (is_array($arg)) {
           foreach ($arg as $key => $value) {
               $arr[$key] = $value;
               $ret++;
           }
       }else{
           $arr[$arg] = "";
       }
   }
   return $ret;
}
function line($P,$PV) {
	foreach($P as $key => $value) {
		if ($PV == $key) {
			$PV = $value;
			query("SELECT performer,title FROM album WHERE album_id=$value");
			$r = next_row();
			print "<DL STYLE='font-size:11px'> => {$r['performer']} - {$r['title']}";
			return array($PV,$value);
		}			
	}
}

if (isset($deselect)) {
	unset($select);
	unset($graf);
}

##===================================================== Commands =============================================================>

## DELETE ++++++++++++++++++++++++++++++++
if (isset($delete) && $id !='') 
{
    if ($REMOTE_ADDR == '127.0.0.1'|$REMOTE_ADDR == '192.168.1.21' |$REMOTE_ADDR == '192.168.1.131'  |$REMOTE_ADDR == $TRUSTED_ADDR)
      sqlcmd("DELETE FROM album WHERE album_id=$id");
    else print "Impossible!";
}

## MODIFY ++++++++++++++++++++++++++++++++
if (isset($modify) && $id !='' && $inlogin) 
{
    $select = $id;
    #$genres = preg_split('/,/',$genre);

    if ($time == '') $time = '00:00:00';
    $cmd ="UPDATE album SET 
      performer = '$performer',
      title = '$title',
      time = '$time',
      genre = '$genre',
	  publisher = '$publisher',
      year = $year";
    if ($stars != 0) $cmd .= ",stars = (stars+$stars),vote = (vote+1)";
    $cmd .= " WHERE album_id = $id";
    sqlcmd($cmd);

    query("SELECT album_id FROM shelf WHERE album_id=$id AND user_id=$inlogin");
	if ($nur) {
		if ($disk>=0) {
			$cmd = "UPDATE shelf SET 
				storage = '$disk',
				quality = '$quality',          
				format = '$type',          
				comment = '$log' WHERE album_id=$id AND user_id=$inlogin";
			sqlcmd($cmd);
		}
		else {	#if the catalog number less than zero we drop it from the shelf
			$cmd = "DELETE FROM shelf WHERE album_id=$id AND user_id=$inlogin";
			sqlcmd($cmd);
		}
	}
    if ($_FILES["cover"]["tmp_name"] or trim($coveru) != '') {
        if (trim($coveru) != '') 
        {
			$remote_handle = fopen($coveru, "rb");
	        $local_handle = fopen('/tmp/music_add.image', 'wb');
	        if ($remote_handle===false || $local_handle===false) {
				// error reading or opening file
				return "Error 1.";
			}
			while (!feof($remote_handle)) 
            {
				if (fwrite($local_handle, fread($remote_handle, 1024)) === FALSE) {
					// 'Download error: Cannot write to file ('.$file_target.')';
					return "Error 2.";
				}
			}
			fclose($remote_handle);
			fclose($local_handle);
			$mime = mime_content_type('/tmp/music_add.image');
			$size = filesize('/tmp/music_add.image');
			$ffile = fopen('/tmp/music_add.image','rb');
		} 
        else 
        {
            $ffile = fopen($_FILES["cover"]["tmp_name"], "rb");
			$mime = mime_content_type($_FILES["cover"]["tmp_name"]);
			$size = $_FILES['cover']['size'];
		}
		$contents = fread($ffile, $size);
		fclose($ffile);
        #$contents = pg_escape_bytea($contents);
        $contents = AddSlashes($contents);
        if($contents and $mime) {
          sqlcmd("DELETE FROM cover WHERE album_id=$id");
          sqlcmd("INSERT INTO cover (album_id,image,mime,pg) VALUES ($id,'$contents','$mime',0)");
        }
    }
} 
elseif (isset($modify) and !isset($inlogin)) print "Impossible!";


# Result Pages From-To
$TO = 50;      # egyszerre megmutatott sorok száma;
if (!isset($FROM)) $FROM = 0;
if (isset($lastpage)) $FROM = $lastpage;
if (isset($firstpage)) $FROM = 0;
if (isset($nextpage)) $FROM = $nextpage+$TO;
if (isset($prevpage)) $FROM = $prevpage-$TO;
if (isset($exactpage)) $FROM = ($exactpage-1)*$TO;

# Select menus
$genresel = select_menu('album','genre','genre');
$instruments = select_menu("$instruments_table",'name','instruments_id');

##===================================================== SEARCH =============================================================>


if (!isset($SearchString) or $SearchString == '') $SearchString = '*';
if (!isset($SearchGenre)) $SearchGenre = '*';
if (!isset($SearchDisk) or $SearchDisk == '') $SearchDisk = '*';
if (!isset($SearchYear) or $SearchYear == '') $SearchYear = '*';

if ($SearchString != '*') # Zenész azonosítók keresése a keresett sztringhez
{
  $q = "SELECT m.musicans_id FROM musicans m WHERE name REGEXP '^$SearchString|$SearchString$'";
  query($q);
  if ($nur) {
    $r = mysql_fetch_row($res);
    $mid = $r[0];
  }
}

$FILTER = '';   # szürő az SQL lekédezéhez
if (isset($inlogin))
	$FROM_FILTER = " album a LEFT JOIN shelf s ON (s.album_id=a.album_id AND s.user_id=$inlogin) ";
else
	$FROM_FILTER = " album a";
$GROUP = '';
$BRICS = 0;

# < Search button > press...
if (isset($search) or $order_search==1) 
{
    if ($SearchString != '*') 
        $FILTER .= " (performer LIKE '%$SearchString%' OR title LIKE '%$SearchString%') AND";
    
    # Ilyen keresés mintha nem is lenne...    
    if (isset($mid) and $mid!='') 
    {
        $FILTER = preg_replace('/AND$/','',$FILTER);
        if ($FILTER) $FILTER = " ($FILTER OR n.musicans_id=$mid) AND";
        else $FILTER .= " n.musicans_id=$mid AND";
        //$FROM_FILTER = 'album ';
		#if (isset($inlogin)) $FROM_FILTER .= ' LEFT JOIN shelf s ON (s.album_id=a.album_id)';
		$FROM_FILTER .= ' LEFT JOIN nexus n ON (a.album_id=n.album_id)';
        if ($inlogin)
            $GROUP = 'GROUP BY a.album_id,performer,title,storage,year,quality,comment,stars,time,genre,format,vote,publisher';
        else $GROUP = 'GROUP BY a.album_id,performer,title,year,stars,time,genre,vote,publisher';
        $query_string = get_musicans_by_id($mid);
        $query_label = "Musicans";
    }

    if ($SearchGenre != '*') $FILTER .= " genre='$SearchGenre' AND";
    if ($SearchDisk != '*') $FILTER .= " storage='$SearchDisk' AND";
    
    if ($SearchYearS != '' or $SearchYearE != '') 
    {
        if ($SearchYearS == '') $SearchYearS = 0;
        if ($SearchYearE == '') $SearchYearE = 9999;
        $FILTER .= " year BETWEEN $SearchYearS AND $SearchYearE AND";
    }

    if (isset($SearchInstrument) and count($SearchInstrument) != 0) 
    {
        $query_string = '';
        if ($iconn=='AND') {
          $FROM_FILTER = "(";
          for($i=0;$i<count($SearchInstrument);$i++) {
            $q .= "SELECT DISTINCT album_id FROM nexus WHERE instruments_id=$SearchInstrument[$i]";
            if ($i<count($SearchInstrument)-1) $q .= " UNION ALL ";
            $query_string .= get_instrument_by_id($SearchInstrument[$i]).',';
          }
          $FROM_FILTER = "($q) AS t1 LEFT JOIN album a ON (t1.album_id=a.album_id)";
		  if (isset($inlogin)) $FROM_FILTER .= ' LEFT JOIN shelf s ON (s.album_id=a.album_id) ';
          $GROUP = "GROUP BY t1.album_id HAVING COUNT(*)=$i";
        } else {
          $FROM_FILTER = "nexus n LEFT JOIN album a ON (n.album_id=a.album_id) "; 
		  if (isset($inlogin)) $FROM_FILTER .= ' LEFT JOIN shelf s ON (s.album_id=a.album_id) ';
          $FROM_FILTER .= " WHERE n.instruments_id IN (";
          for($i=0;$i<count($SearchInstrument);$i++) {
            $q .= "$SearchInstrument[$i],";
            $query_string .= get_instrument_by_id($SearchInstrument[$i]).',';
          }
          $q = preg_replace('/,$/','',$q);
          $FROM_FILTER .= "$q) ";
          $GROUP = "GROUP BY a.album_id";
        }
        $query_string = preg_replace('/,$/','',$query_string);
        $query_label = "Instruments";
        /*
          SELECT a.album_id,performer,title,time,publisher,genre,year,stars,vote FROM (
             SELECT DISTINCT album_id FROM nexus
             WHERE instruments_id =3
            UNION ALL
             SELECT DISTINCT album_id FROM nexus
             WHERE instruments_id =10
            UNION ALL
             SELECT DISTINCT album_id FROM nexus
             WHERE instruments_id =86
          ) AS t1 LEFT JOIN album a ON (t1.album_id=a.album_id)
          GROUP BY t1.album_id HAVING COUNT(*)=3
        */
    } 
    $FILTER = preg_replace('/AND$/','',$FILTER);
}
elseif (isset($filter) and $filter !='') # Kattintós kereső esetén:
{
	if ($filter == 'musicans') {
		query("SELECT a.album_id FROM album a JOIN nexus n ON (n.album_id=a.album_id) WHERE n.musicans_id=$value");
		$FILTER = "";
		for ($i=0;$i<$nur;$i++) {
			$r = mysql_fetch_row($res);
			$FILTER .= "a.album_id=$r[0] OR ";
		}
		$FILTER = preg_replace('/OR $/','',$FILTER);
		$orderby_first = 'performer';
		$orderby_second = 'title';
		$direction = 'ASC';
        $query_string = get_musicans_by_id($value);
        $query_label = "Musicans";
	} else 
    {
        if ($filter=='performer') {
            $query_string = get_performer_by_id($value);
            $query_label = "Performer";
        }

		query("SELECT $filter FROM album a LEFT JOIN shelf s ON (s.album_id=a.album_id) WHERE a.album_id=$value");
		$r = mysql_fetch_row($res);
		$v = preg_split("/,/",$r[0]);
		$vk = array();
		foreach($v as $vv) {
		  $vd = preg_split("/ and /",$vv);
		  $vk = array_merge($vk,$vd);
		}
		$v = $vk;
		$vk = array();
		foreach($v as $vv) {
		  $vd = preg_split("/&/",$vv);
		  $vk = array_merge($vk,$vd);
		}
		$v = $vk;
		$vk = array();    
		foreach($v as $vv) {
		  $vd = preg_split("/ with /",$vv);
		  $vk = array_merge($vk,$vd);
		}
		$v = $vk;
		$vk = array();    
		foreach($v as $vv) {
		  $vd = preg_split("/ width /",$vv);
		  $vk = array_merge($vk,$vd);
		}
		$v = $vk;
		$vk = array();    
		foreach($v as $vv) {
		  $vd = preg_split("/-/",$vv);
		  $vk = array_merge($vk,$vd);
		}
		$v = $vk;
		$vh = '';
		foreach($v as $vv) {
		  if ($vv == 'The' | $vv == 'the' | $vv == 'a' | $vv == 'A') continue;
		  $vv = trim($vv);
		  $vh .= "$filter LIKE '%$vv%' OR ";
		}
		$v = preg_replace('/OR $/','',$vh);
		$FILTER = "$v";
		$orderby_first = $filter;
		$orderby_second = '';
		$direction = 'ASC';
	}
}
else # Rendezési sorrend beállítva
{
    if (!isset($orderby_first)) {
      $orderby_first = 'performer';
      $orderby_second = 'title';
      $direction = 'asc';
    }
}
$ORDERBY = $orderby_first;
if ($direction) $ORDERBY .= " $direction"; 
if ($orderby_second) $ORDERBY .= ",$orderby_second";

if (isset($select)) # Egy album kiválasztása
{
    $FILTER = "a.album_id=$select";
}


if ($FILTER) $FILTER = 'WHERE '.$FILTER;

# A select eredményt egyben kéne eltenni, hogy lehessen másik selectet csinálni.
if (isset($inlogin)) $columns = "a.album_id,performer,title,storage,quality,time,publisher,genre,format,year,comment,stars,vote";
else $columns = "a.album_id,performer,title,time,publisher,genre,year,stars,vote";
$search_query = "SELECT $columns FROM $FROM_FILTER $FILTER $GROUP ORDER BY $ORDERBY LIMIT $TO OFFSET $FROM";
$length_query = "SELECT $columns FROM $FROM_FILTER $FILTER $GROUP ORDER BY $ORDERBY";
query($length_query);
$length = mysql_num_rows($res);
query($search_query);
#print $search_query.'<P>';

#==============================================================================================

if (isset($select)) {
    ## Detailed selection output 
    #
    include('./album_sheet.php');
}
else {

### Browser selection output =============================================== >>
# Search box area:

include('SearchBox.php');

# results area:
print "<FORM METHOD=post NAME=result>
<INPUT TYPE=hidden NAME=RFROM VALUE=$FROM>";

include('pagecounter');

# column's titles:
print "
<TABLE WIDTH=100% CELLSPACING=1><TR>
<TD STYLE='width:30px'></TD>
<TD STYLE='background:#7d817c;font-weight:bold' OnMouseOver='changeBG(\"in\",\"t1\")'  OnMouseOut='changeBG(\"out\",\"t1\")' ID=t1 OnClick='MOrder(\"performer\",\"$orderby_first\",\"$orderby_second\",\"$direction\",\"$SearchGenre\",\"$SearchString\",\"$SearchDisk\")'>Artist</TD>
<TD STYLE='background:#7d817c;font-weight:bold' OnMouseOver='changeBG(\"in\",\"t2\")'  OnMouseOut='changeBG(\"out\",\"t2\")' ID=t2 OnClick='MOrder(\"title\",\"$orderby_first\",\"$orderby_second\",\"$direction\",\"$SearchGenre\",\"$SearchString\",\"$SearchDisk\")'>Title</TD>";
if (isset($inlogin)) print "
<TD STYLE='background:#7d817c;font-weight:bold' OnMouseOver='changeBG(\"in\",\"t3\")'  OnMouseOut='changeBG(\"out\",\"t3\")' ID=t3 OnClick='MOrder(\"quality\",\"$orderby_first\",\"$orderby_second\",\"$direction\",\"$SearchGenre\",\"$SearchString\",\"$SearchGenre\")'>Genre</TD>
<TD STYLE='background:#7d817c;font-weight:bold' OnMouseOver='changeBG(\"in\",\"t4\")'  OnMouseOut='changeBG(\"out\",\"t4\")' ID=t4 OnClick='MOrder(\"disk\",\"$orderby_first\",\"$orderby_second\",\"$direction\",\"$SearchGenre\",\"$SearchString\",\"$SearchDisk\")'><ABBR TITLE='Catalog Number'>Cat.No.</ABBR></TD>
<TD STYLE='background:#7d817c;font-weight:bold' OnMouseOver='changeBG(\"in\",\"t5\")'  OnMouseOut='changeBG(\"out\",\"t5\")' ID=t5 OnClick='MOrder(\"log\",\"$orderby_first\",\"$orderby_second\",\"$direction\",\"$SearchGenre\",\"$SearchString\",\"$SearchDisk\")'>Log</TD>";
else print "<TD STYLE='background:#7d817c;font-weight:bold' OnMouseOver='changeBG(\"in\",\"t3\")'  OnMouseOut='changeBG(\"out\",\"t3\")' ID=t3 OnClick='MOrder(\"quality\",\"$orderby_first\",\"$orderby_second\",\"$direction\",\"$SearchGenre\",\"$SearchString\",\"$SearchGenre\")'>Genre</TD>";
print "</TR>";

# results:
for ($i=0;$i<$nur;$i++) {
    //$j = $FROM+$i+1; 
    $r = next_row();
//    print_r($r);
    $log = preg_replace('/^(OK)!$/',"<SPAN STYLE='color:green'>$1</SPAN>!",$r['comment']);
    $log = preg_replace('/^(.+)(!+)$/',"<SPAN STYLE='color:red'>$1</SPAN>$2",$log);
    $log = preg_replace('/^(.+)\?$/',"<SPAN STYLE='color:orange'>$1</SPAN>?",$log);
    $stars = '';
	if ($r['vote']>0) {
		$stars = "<SPAN STYLE='color:orange'>";
		$rating = round($r['stars']/$r['vote']);
		for($k=0;$k<$rating;$k++) $stars .= '*';
		$stars .= '</SPAN>';
	}
    $log = "$log$stars";
    if ($r['year'] !=0) $year = "(<a href='?filter=year&value=".$r['album_id']."'>".$r['year']."</a>)";
    else $year = '';
    print "<TR onmouseover=\"bgColor='#BEC4BC'\" onmouseout=\"bgColor='#FFFFFF'\" bgcolor=\"#FFFFFF\">
              <TD Id=".$r['id']."". '_0' ." ALIGN=left STYLE='padding-left:6px'>
                        <BUTTON TYPE=submit NAME=select VALUE='".$r['album_id']."' STYLE='border:none;background:transparent;padding:0;margin:0;cursor:pointer;cursor:hand' OnClick='this.form.submit()'><IMG SRC='icons/Button-Info-16x16.png'></BUTTON></TD>
              <TD><a href='?filter=performer&value=".$r['album_id']."'>".$r['performer']."</a></TD>
              <TD>".$r['title']." $year "; 
              if (isset($r[format])) print "[{$r[format]}]"; print "</TD>";
              if (isset($inlogin)) print "<TD>".$r['genre']."</TD>
              <TD><a href='?filter=storage&value=".$r['album_id']."'>".$r['storage']."</a></TD>
              <TD>$log</TD>";
			  else print "<TD>{$r['genre']}</TD>";
			  print "</TR>";
}
print "</TABLE>";

include('pagecounter');

print "</FORM>";

} # End of Browser selection output


closeSQL();
?>
<DIV STYLE='background-color:#FFA800;height:2px;border-bottom:1px solid gray'></DIV>
</CENTER>
</BODY></HTML>
PHP Helpdesk script - 100% PHP source code. Try it now!
Return current item: Music and Discography Database