Location: PHPKode > projects > PHP Choral Music Library > choralmusiclib-1.5/librarylib.php
<?php

include ("config.php");

// Site Language selector
// 
// The site can become unusable if there are mistakes in the chosen language file
// English is therefore loaded first
// After that, a language file is only loaded if the config file sets a different language
// than english and the file exists. The rest is up to the user.
include ("languages/language-en.php");

if ($choralcfg_sitelanguage !== "en") {
  $sitelanguagefilename = "languages/language-" . $choralcfg_sitelanguage . ".php"; 
  if (file_exists($sitelanguagefilename)) {
    include ($sitelanguagefilename); 
   }
 }
  	



//

function html_begin ($title, $header, $cssfile) {
   header("Content-Type: text/html; charset=utf-8");
   print("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
   print("<html>\n");
   print("<head>\n");
   if ($title) 
      print("<title>$title</title>\n");
   print("<link href=\"$cssfile\" rel=\"stylesheet\" type=\"text/css\">");
   print("<meta http-equiv=\"Content-type\" content=\"text/html;charset=UTF-8\" />");
   print("</head>\n");
   print("<body bgcolor=\"#FFFFFF\">\n");
   if ($header) 
      print("<h2>$header</h2>\n");
}

function library_footer() {
}

function html_end() {
   print("</body>\n</html>\n");

}

function connect_db($host,$user,$passwd,$database) {
   $link = mysql_connect($host,$user,$passwd);
   if ( $link && mysql_select_db($database) ) 
   {
      return($link);
   }
   else {
      return (FALSE);
   }
}

// function to print the search form
function search_form() {
  global $DEBUG,$link,$PHP_SELF;
  
  //temporary fix, since functions do not see the included files??

  include ("config.php");
  include ("languages/language-en.php");

  if ($choralcfg_sitelanguage !== "en") {
  $sitelanguagefilename = "languages/language-" . $choralcfg_sitelanguage . ".php"; 
  if (file_exists($sitelanguagefilename)) {
    include ($sitelanguagefilename); 
   }
  }
  
  
  ////
  
  $search_string = "<form action='$PHP_SELF' method='POST'><table border=0 cellpadding=10>\n";
  $search_string .= "<tr><td>$choral_title</td>".
                    "<td><input type=text name=the_title value='' size=50 maxlength=80></td></tr>\n";
  $search_string .= "<tr><td>$choral_author<br>($choral_composer, $choral_lyricist ,...)</td>".
                    "<td><input type=text name=artist value='' size=50 maxlength=80></td></tr>\n";

  $search_string .= "<tr><td>$choral_language</td>\n";
  $sql = "select language_id, language_name ".
                    "from language order by language_name ASC";
  $result = mysql_query($sql, $link);
  $search_string .= "<td><select name=language_id >\n";
  $search_string .= "<option value=0>$choral_any</option>\n";
  while ($row = mysql_fetch_object($result)) {
      $language_id = $row -> language_id;
      $language[$language_id] = $row -> language_name;
      $search_string .= "<option value=$language_id >$language[$language_id]</option>\n";
  }
  $search_string .= "</select></td></tr>\n";

  $search_string .= "<tr><td>$choral_genre</td>";
  $sql = "select genre_id, genre_name ".
                    "from genre order by genre_name ASC";
  $result = mysql_query($sql, $link);
  $search_string .= "<td><select name=genre_id >\n";
  $search_string .= "<option value=0>$choral_any</option>\n";
  while ($row = mysql_fetch_object($result)) {
      $genre_id = $row -> genre_id;
      $genre[$genre_id] = $row -> genre_name;
      $search_string .= "<option value=$genre_id >$genre[$genre_id]</option>\n";
  }
  $search_string .= "</select></td></tr>\n";

  $search_string .= "<tr><td>$choral_accompaniment</td>";
  $sql = "select accompaniment_id, accompaniment_name ".
                    "from accompaniment order by accompaniment_name ASC";
  $result = mysql_query($sql, $link);
  $search_string .= "<td><select name=accompaniment_id >\n";
  $search_string .= "<option value=0>$choral_any</option>\n";
  while ($row = mysql_fetch_object($result)) {
      $accompaniment_id = $row -> accompaniment_id;
      $accompaniment[$accompaniment_id] = $row -> accompaniment_name;
      $search_string .= "<option value=$accompaniment_id >$accompaniment[$accompaniment_id]</option>\n";
  }
  $search_string .= "</select></td></tr>\n";

  $search_string .=  "<tr><td>$choral_typeofsoloist</td><td>\n".
                     "<select name=soloist>\n".
                     "<option>$choral_any</option>\n".
                     "<option>$choral_soprano</option>\n".
                     "<option>$choral_mezzosoprano</option>\n".
                     "<option>$choral_contralto</option>\n".
                     "<option>$choral_alto</option>\n".
                     "<option>$choral_countertenor</option>\n".
                     "<option>$choral_tenor</option>\n".
                     "<option>$choral_baritone</option>\n".
                     "<option>$choral_bass</option>\n".
                     "</select></td></tr>\n";

  $search_string .= "<tr><td>$choral_publisher</td>\n";
  $sql = "select publisher_id, publisher_name ".
                    "from publisher order by publisher_name ASC";
  $result = mysql_query($sql, $link);
  $search_string .= "<td><select name=publisher_id >\n";
  $search_string .= "<option value=0>$choral_any</option>\n";
  while ($row = mysql_fetch_object($result)) {
      $publisher_id = $row -> publisher_id;
      $publisher[$publisher_id] = $row -> publisher_name;
      $search_string .= "<option value=$publisher_id >$publisher[$publisher_id]</option>\n";
  }
  $search_string .= "</select></td></tr>\n";

  $search_string .= "<tr><td>$choral_collection</td>";
  $sql = "select collection_id, collection_name ".
                    "from collection order by collection_name ASC";
  $result = mysql_query($sql, $link);
  $search_string .= "<td><select name=collection_id >\n";
  $search_string .= "<option value=0>$choral_any</option>\n";
  while ($row = mysql_fetch_object($result)) {
      $collection_id = $row -> collection_id;
      $collection[$collection_id] = $row -> collection_name;
      $search_string .= "<option value=$collection_id >$collection[$collection_id]</option>\n";
  }
  $search_string .= "</select></td></tr>\n";

  $search_string .= "<TR><TD>$choral_voicing</TD>".
                    "<TD><input type=text name=voicing size=50 maxlength=20 value=''></TD></TR>\n";
  $search_string .= "<tr><td colspan=2 align=center><input type=submit value=\"$choral_captionsearch\"> <input type=reset value=\"$choral_captionreset\"></td></tr>\n";
  $search_string .= "</table>\n";
  $search_string .= "<input type=hidden name=searching value='1'>\n";
  $search_string .= "</form>\n";
  return $search_string;
}



// function to return a string array of formatted info about an array of work ids
function format_works($work_ids) {
  global $DEBUG, $link, $PHP_SELF;

  //temporary fix, since functions do not see the included files??

  include ("config.php");
  include ("languages/language-en.php");

  if ($choralcfg_sitelanguage !== "en") {
  $sitelanguagefilename = "languages/language-" . $choralcfg_sitelanguage . ".php"; 
  if (file_exists($sitelanguagefilename)) {
    include ($sitelanguagefilename); 
   }
  }
  
  
  ////

  $search_path = "$PHP_SELF";

  if ($DEBUG) print "search path = $search_path";

  $soloists = array($choral_soprano,$choral_mezzosoprano,$choral_alto,$choral_contralto,$choral_countertenor,$choral_tenor,$choral_baritone,$choral_bass);

  // loop through the work ids and get all of the info with queries
  while(list($key,$value) = each($work_ids)) {
    $this_work_string = "";
    $work_id = $value;
    $sql = "select * from work where work_id=$work_id";
    $result = mysql_query($sql, $link);
    $row = mysql_fetch_object($result);
    $catalog_number = $row -> catalog_number;
    $the_title = $row -> title;
    $duration = $row -> duration;
    $publisher_id = $row -> publisher_id;
    $available = $row -> available;
    $voicing = $row -> voicing;
    $other = $row -> other;
    $this_work_string .=  "<P><table border=0>\n";
    //$this_work_string .=  "<TR><TD><B>WMC Catalog Number:</B></TD><TD>$catalog_number</TD></TR>\n";
    $this_work_string .=  "<TR><TD><B>$choral_title</B></TD>".
                          "<TD><a href='$search_path?work_id=$work_id&searching=1'>$the_title</a></TD></TR>\n";
    if ($duration > 0) {
        $this_work_string .=  "<TR><TD><B>$choral_duration</B></TD><TD>$duration</TD></TR>\n";
    } else {
        $this_work_string .=  "<TR><TD><B>$choral_duration</B></TD><TD>n/a</TD></TR>\n";
    }
    $this_work_string .=  "<TR><TD><B>$choral_author_s:</B></TD><TD></TD></TR>\n";
    $sql = "select person_work.person_id, concat(last_name, ', ', first_name) as name, type from person_work, person where ".
           "person_work.work_id='$work_id' and person_work.person_id = person.person_id";
    $result = mysql_query($sql, $link);
    while ($row = mysql_fetch_object($result)) {
        $name = $row -> name;
        $person_id = $row -> person_id;
        $type = $row -> type;
        $this_work_string .=  "<TR><TD>&nbsp $type:</TD><TD>".
	                      "<a href='$search_path?person_id=$person_id&searching=1'>$name</a></TD></TR>\n";
    }
    $this_work_string .=  "<TR><TD><B>$choral_language</B></TD><TD></TD></TR>\n";
    $sql = "select language_work.language_id, language_name from language_work, language where ".
           "language_work.work_id='$work_id' and language_work.language_id = language.language_id";
    $result = mysql_query($sql, $link);
    while ($row = mysql_fetch_object($result)) {
        $name = $row -> language_name;
        $language_id = $row -> language_id;
        $this_work_string .=  "<TR><TD></TD><TD><a href='$search_path?language_id=$language_id&searching=1'>$name</a></TD></TR>\n";
    }
    $this_work_string .=  "<TR><TD><B>$choral_accompaniment</B></TD><TD></TD></TR>\n";
    $sql = "select accompaniment_work.accompaniment_id, accompaniment_name from accompaniment_work, accompaniment where ".
           "accompaniment_work.work_id='$work_id' and accompaniment_work.accompaniment_id = accompaniment.accompaniment_id";
    $result = mysql_query($sql, $link);
    while ($row = mysql_fetch_object($result)) {
        $name = $row -> accompaniment_name;
        $accompaniment_id = $row -> accompaniment_id;
        $this_work_string .=  "<TR><TD></TD>".
	                      "<TD><a href='$search_path?accompaniment_id=$accompaniment_id&searching=1'>$name</a></TD></TR>\n";
    }
    $this_work_string .=  "<TR><TD><B>$choral_genre</B></TD><TD></TD></TR>\n";
    $sql = "select genre_work.genre_id, genre_name from genre_work, genre where ".
           "genre_work.work_id='$work_id' and genre_work.genre_id = genre.genre_id";
    $result = mysql_query($sql, $link);
    while ($row = mysql_fetch_object($result)) {
        $name = $row -> genre_name;
        $genre_id = $row -> genre_id;
        $this_work_string .=  "<TR><TD></TD>".
	                      "<TD><a href='$search_path?genre_id=$genre_id&searching=1'>$name</a></TD></TR>\n";
    }
    $this_work_string .=  "<TR><TD><B>$choral_typeofsoloist</B></TD><TD></TD></TR>\n";
    $sql = "select * from soloist_work where work_id='$work_id'";
    $result = mysql_query($sql, $link);
    reset($soloists);
    if(mysql_numrows($result) >=1) {
        unset($solo_type);
        while ($row = mysql_fetch_object($result)) {
	    $solo_id = $row -> soloist_work_id;
            $solo_type[$solo_id] = $row -> type;
            $solo_alt[$solo_id] = $row -> alternate_to;
        }
        while(list($key,$value) = each($soloists)) {
            reset($solo_type);
            while(list($type_key, $type_value) = each($solo_type)) {
                if ($value == $type_value) {
		  if ($solo_alt[$type_key] > 0) {
		    $alt_key = $solo_alt[$type_key];
		    $alt_string = "<i>($choral_alternatesoloistto {$solo_type[$alt_key]})</i>";
		  } else {
		    $alt_string = "";
		  }
                    $this_work_string .=  "<TR><TD></TD>".
		                          "<TD><a href='$search_path?soloist=$value&searching=1'>$type_value</a> $alt_string</TD></TR>\n";
                }
            }
        }
    } else {
                    $this_work_string .=  "<TR><TD></TD><TD>$choral_none</TD></TR>\n";
    }
    $publisher_query = "select publisher_name from publisher where ".
                      "publisher_id='$publisher_id' ";
    $result = mysql_query($publisher_query, $link);
    $row = mysql_fetch_object($result);
    $publisher = $row -> publisher_name;
    $this_work_string .=  "<TR><TD><B>$choral_publisher</B></TD><TD></TD></TR>\n".
                          "<TR><TD></TD><TD><a href='$search_path?publisher_id=$publisher_id&searching=1'>$publisher</a></TD></TR>\n";
    $this_work_string .=  "<TR><TD><B>$choral_collection</B></TD><TD></TD></TR>\n";
    $sql = "select collection_work.collection_id, collection_name, num_copies, catalog_num from collection_work, collection where ".
           "collection_work.work_id='$work_id' and collection_work.collection_id = collection.collection_id";
    $result = mysql_query($sql, $link);
    while ($row = mysql_fetch_object($result)) {
        $name = $row -> collection_name;
        $collection_id = $row -> collection_id;
        $num_copies = $row -> num_copies;
        $catalog_num = $row -> catalog_num;
	if (trim($catalog_num) != "") {
	  $catalog_num_string = trim($catalog_num);
	} else {
	  $catalog_num_string = "n/a";
	}
	
        $this_work_string .=  "<TR><TD>&nbsp $choral_collectionname<br>&nbsp $choral_numberofcopies<br>&nbsp $choral_catalognumber</TD>".
	                      "<TD><a href='$search_path?collection_id=$collection_id&searching=1'>$name</a><br> ".
			      "$num_copies <br> $catalog_num_string</TD></TR>\n";
    }
    $sql = "select sum(num_copies) as sum_copies from collection_work where ".
           "collection_work.work_id='$work_id'";
    $result = mysql_query($sql, $link);
    while ($row = mysql_fetch_object($result)) {
        $total_num_copies = $row -> sum_copies;
    }
    $this_work_string .=  "<TR><TD><B>$choral_totalnumberofcopies</B></TD><TD>$total_num_copies</TD></TR>\n";
    $this_work_string .=  "<TR><TD><B>$choral_numberofcopiesavailable</B></TD><TD>$available</TD></TR>\n";
    $this_work_string .=  "<TR><TD><B>$choral_voicing</B></TD>".
                          "<TD><a href='$search_path?voicing=$voicing'>$voicing</a></TD></TR>\n";
    $this_work_string .=  "<TR><TD><B>$choral_otherinfo</B></TD>";
    $this_work_string .=  "<TD>$other</TD></TR>\n";
    $this_work_string .=  "</table></P>\n";
    $work_strings[] = $this_work_string;
  }
  return $work_strings;
}

// return an SQL query based upon the search form
function process_query_form($http_array_input) {
  global $DEBUG,$link, $PHP_SELF;
  
  //temporary fix, since functions do not see the included files??

  include ("config.php");
  include ("languages/language-en.php");

  if ($choralcfg_sitelanguage !== "en") {
  $sitelanguagefilename = "languages/language-" . $choralcfg_sitelanguage . ".php"; 
  if (file_exists($sitelanguagefilename)) {
    include ($sitelanguagefilename); 
   }
  }
  
  
  ////
  
  // the variable $http_array_input contains the HTTP_POST_VARS
  while(list($key,$value)=each($http_array_input)) {
    $$key = $value;
    if ($DEBUG) print "http_array_input[$key] = $value<br>\n";
  }

  $valid_query = false;
  $from = "from ";

  // dummy initial where query
  $where = "where work.work_id > 0";

  // collection
  if ($collection_id>0) {
    $collection_sql = "and collection_work.collection_id = '$collection_id' and ".
                      "collection_work.work_id = work.work_id";
    $from .= "collection_work, ";
    $valid_query = true;
  }

  if ($publisher_id>0) {
    $publisher_sql = "and work.publisher_id = '$publisher_id'";
    $valid_query = true;
  }

  if ($language_id>0) {
    $language_sql = "and language_work.language_id = '$language_id' and ".
                    "language_work.work_id = work.work_id";
    $from .= "language_work, ";
    $valid_query = true;
  }

  if ($genre_id>0) {
    $genre_sql = "and genre_work.genre_id = '$genre_id' and ".
                 "genre_work.work_id = work.work_id";
    $from .= "genre_work, ";
    $valid_query = true;
  }

  if (trim($voicing) != "") {
    $voicing = trim(addslashes($voicing));
    $voicing_sql = "and work.voicing like '$voicing'";
    $valid_query = true;
  }

  if ((trim($soloist) != "")&&(trim($soloist) != $choral_any)) {
    $soloist = trim(addslashes($soloist));
    $soloist_sql = "and soloist_work.type like '$soloist' and ".
                   "soloist_work.work_id = work.work_id";
    $from .= "soloist_work, ";
    $valid_query = true;
  }

  if ($accompaniment_id > 0) {
    $accompaniment_sql = "and accompaniment_work.accompaniment_id = '$accompaniment_id' and ".
                         "accompaniment_work.work_id = work.work_id";
    $from .= "accompaniment_work, ";
    $valid_query = true;
  }

  if ($person_id > 0) {
    $person_sql = "and person_work.person_id = $person_id and ".
                  "person_work.work_id = work.work_id";
    $from .= "person_work, ";
    $valid_query = true;
  }

  if ($work_id > 0) {
    $work_sql = "and work.work_id = $work_id";
    $valid_query = true;
  }

  if (trim($artist) != "") {
    // get rid of some offenders that should never be in a name by changing em into spaces
    $artist = strtr($artist,"1234567890;%@\"",
                            "              ");
    $artist = trim(addslashes($artist));

    $artist_sql = "and (";

    // pull tokens out of the string here and get rid of spaces and commas
    $tok = strtok($artist," ,");
    while ($tok) {
      if (strlen($tok)>2) {
        $artist_sql .= "person.last_name like '%$tok%' or person.first_name like '%$tok%' or ";
      } 
      $tok = strtok(" ,");
    }
    unset($tok);
    if (strlen($artist_sql) > 10) {
      $artist_sql = substr($artist_sql,0,strlen($artist_sql)-3).") and ".
                    "person_work.person_id = person.person_id and ".
		    "person_work.work_id = work.work_id";
      if(strpos($form,"person_work") > 0) {
        $form .= "person, ";
      } else {
        $from .= "person, person_work, ";
      }
      $valid_query = true;
    } else {
      $artist_sql = "";
    }
  }
  if($DEBUG) print "artist_sql = $artist_sql <br>\n";

  if (trim($the_title) != "") {

    $the_title = trim(addslashes($the_title));
    $the_title = strtr($the_title,";%@\"","    ");

    $the_title_sql = "and (";

    $tok = strtok($the_title, " ,");
    while ($tok) {
      if (strlen($tok) > 2) {
        $the_title_sql .= "work.title like '%$tok%' or ";
      }
      $tok = strtok(" ,");
    }
    unset($tok);
    if (strlen($the_title_sql) > 10) {
      $the_title_sql = substr($the_title_sql,0,strlen($the_title_sql)-3).") ";
      $valid_query = true;
    } else {
      $the_title_sql = "";
    }
     
  }

  if($DEBUG) print "the_title_sql = $the_title_sql <br>\n";

  $from .= "work ";

  $where .= " $work_sql $collection_sql $publisher_sql $language_sql $genre_sql $voicing_sql $soloist_sql $accompaniment_sql ".
            "$artist_sql $person_sql $the_title_sql";

  $full_sql = "select work.work_id $from $where group by work.work_id order by work.title asc";
  if($DEBUG) print "full_sql = $full_sql <br>\n";

  if ($valid_query) {
     return $full_sql;
  } else {
     return "";
  }

}


?>
Return current item: PHP Choral Music Library