<!--
    Brian Platz
    CS85 Section 1643
    Professor Geddes
    May 7, 2012
    Assignment 11: In this lab assignment you shall create tables that store data about audio 
            albums - CDs, cassettes, LPs, DVDs, etc. You shall use a MySQL database called albums 
            that exists on ciswebs. The cs85 user has been granted full privileges on the albums 
            database. Please DO NOT DROP the albums database. If the database does get dropped, 
            you can recreate it with CREATE DATABASE albums.

        Requirements:
            Design your database. Write the schema. Save your schema as albumsSchema.html.
            You shall create tables in the albums database as needed. Name your tables  
                yourLoginName_tableName. E.g., smith_john_artists.
            You shall write the following HTML and PHP documents:
                a createTables.php script that creates the tables
                a form for the user to enter data for a new album
                a PHP script that handles the form
                a form for the user to enter data for a new artist
                a PHP script that handles the form
                a form that allows the user to search the database for a title
                a PHP script that handles the form
    --> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">  
   <head>
      <title>Music Master</title>
      <style type="text/css">
         body {background-color:#B0E0E6}
         h1 {color:black; font-family:Ariel; font-size:14pt; 
                 font-weight:bold; text-align: center}
         h2 {color:red; font-family:Ariel; font-size:14pt; font-weight:bold}
         h3 {color:black; font-family:Ariel; font-size:24pt; 
                 font-weight:normal; text-align:center}
         .center {margin-left: auto;
                margin-right: auto;}
         h4 {color:black; font-family:Ariel; font-size:11pt; font-weight:normal; text-align:center}
         h5 {color:red; font-family:Ariel; font-size:24pt;
                 font-weight:normal; text-align:center}
         h6 {color:green; font-family:Ariel; font-size:40pt}
      </style>    
   </head>
   <body> 
      <?php
        extract
($_REQUEST);                         // create form variables
        
error_reporting(E_ALL & ~E_NOTICE);         // get rid of runtime notices
        
        
include "variables.php" ;       
        include 
"functions.php";    
        include 
"db_connection_info.php";            // include login information
               
        
$sql "SELECT * FROM platz_brian_artists";          
        
connect();                        
        
$result mysql_query($sql$conn);
        
mysql_close();  
        
        if (!
$result) {
            
createTables();       
            
printForm();
            }
        else {
            switch ( 
$_REQUEST['switch'] ) 
            {
            case 
'1':
                
printform();
                break;
            case 
'2':                        // add artist into db
                
$artist $_REQUEST['artist'];                                        // get data
                
                
$sql "INSERT INTO platz_brian_artists VALUES( NULL, '$artist')"// write query
                
                
connect();                                                            // write to db
                
$result mysql_query($sql$conn) or die( "<br>Error storing Artist to db in Case 2: ".mysql_error() );
                
mysql_close();                                                      // close connection
                
                
print "<h5>\"".$artist."\" has been added</h5>";
                
$_REQUEST['artist'] = "";                                            // clear variable for next time through
                
$_REQUEST['switch']='1';                                            // reset switch
                
printform();                                                        // print form
                  
break;
            case 
'3':                        // add album to database
                                                                                    // get data
                
$album $_REQUEST['album'];
                
$artist $_REQUEST['selArt'];
                
$year $_REQUEST['year'];
                
$media $_REQUEST['selMed'];
                                                                                    
// connect, make query, close
                
$sql "INSERT INTO platz_brian_albums VALUES(NULL, '$album', '$artist', '$year', '.jpg', '$media')";  
                
                
connect();                
                
$result mysql_query($sql$conn) or die( "<br>Error storing Album to db in Case 3: ".mysql_error() );
                
mysql_close();  
                                                                                    
// confirm, clean up, print form
                
print "<h5>\"".$album."\" has been added</h5>";
                
$_REQUEST['album'] = "";    
                
$_REQUEST['selArt'] = "";  
                
$_REQUEST['year'] = "";  
                
$_REQUEST['selMed'] = "";
                
$_REQUEST['switch'] ='1';
                
printform();
                  break;
            case 
'4':                        // search database
                
$lookFor $_REQUEST['lookFor'];                                    // get search string
                
                
if ($lookFor != "" && $lookFor != " ") {                            // if legit search, create query...
                    
$lookFor '%'.$lookFor.'%';                                    // make it 'internal' search
                    
$sql "SELECT art.artistName Artist, alb.albumTitle Album
    FROM platz_brian_artists art INNER JOIN platz_brian_albums alb
    ON art.artistID = alb.artist_id
    WHERE     (art.artistName LIKE '%
$lookFor%' OR alb.albumTitle LIKE '%$lookFor%')";
    
                    
connect();                                                        // ...get results
                    
$result mysql_query($sql$conn) or die( "<br>Error querying db in Case 4: ".mysql_error() );
                    
mysql_close();                                                  // close connection
            
                                                                                    // create and print out search results...
                    
$table "<table class='center' border='2px' bordercolor='black' cellpadding='2px' cellspacing='2px' ><tr><th>Artist</th><th>Album</th></tr>";
                    while (
$row mysql_fetch_assoc($result) ) 
                        {
                        foreach (
$row as $index => $value)
                            {
                            switch (
$index)
                                {
                                case 
'Artist':
                                  
$table .= "<tr><td>".$value."</td>";
                                  break;
                                case 
'Album':
                                  
$table .= "<td>".$value."</td>";
                                  break;
                                default:
                                  print 
"Error in 'print search results' switch -- invalid case = ".$index;
                                }  
                            } 
// end foreach
                        
// end while
                    
$table .= "</table>";
                    print 
"<h3>Your Search Found...</h3>";
                    print 
"<br>$table";
                }
                        
                
$_REQUEST['switch'] ='1';                                            // clean up and reprint screen
                
$_REQUEST['lookFor'] = '';
                
printform();
                  break;
            case 
'5':                        // display music collection
                                                                                    // create query, connect, query, close
                
$sql "SELECT art.artistName Artist, alb.albumTitle Album, alb.yearReleased Released
    FROM platz_brian_artists art INNER JOIN platz_brian_albums alb 
    ON art.artistID = alb.artist_id
    ORDER BY Artist "
;          
    
                
connect();                            
                
$result mysql_query($sql$conn) or die( "<br>Error getting Album Information in Case 5: ".mysql_error() );
                
mysql_close();  
            
                                                                                    
// create and print out albums table...
                
$table "<table class='center' border='2px' bordercolor='black' cellpadding='2px' cellspacing='2px' ><tr><th>Artist</th><th>Album</th><th>Year Released</th></tr>";
                while (
$row mysql_fetch_assoc($result) ) 
                    {
                     foreach (
$row as $index => $value)
                         {
                        switch (
$index)
                            {
                            case 
'Artist':
                              
$table .= "<tr><td>".$value."</td>";
                              break;
                            case 
'Album':
                              
$table .= "<td>".$value."</td>";
                              break;
                            case 
'Released':
                              
$table .= "<td>".$value."</td></tr>";
                              break;
                            default:
                              print 
"Error in 'print collection' switch -- invalid case = ".$index;
                            }  
                         } 
// end foreach
                     
// end while
                 
$table .= "</table>";
                 print 
"<h3>Your Music Collection</h3>";
                print 
"<br>$table";

                                                                                    
// clean up and reprint screen    
                
$_REQUEST['switch']='1';
                
printform();
                  break;
            case 
'6':                        // revert to baseline db
                
createTables();
                
$_REQUEST['switch'] ='1';
                print 
"<h5>The database has been restored to its default.</h5>";
                
printform();
                break;
            default:
                
createTables();
                
$_REQUEST['switch'] ='1';
                
printform();
            }
}

?>
      
<a href='../index.html#unit11' >Main Page</a>  
   </body>
</html>