<%@ page language = "java"%>
<%@ page import = "java.util.*"%>
<%@ page import = "java.sql.*"%>

<%@ taglib uri="http://displaytag.sf.net" prefix="display" %>


<%
    int i=0;
    int count=0;
		Connection conn = null;

           try
           {
               String userName = "root";
               String password = "";
               String url = "jdbc:mysql://localhost/band";
               Class.forName ("com.mysql.jdbc.Driver").newInstance();
               conn = DriverManager.getConnection (url, userName, password);
               System.out.println ("Database connection established");
           
			String like = "";
		
            if (request.getParameter("like").equals("")) {
            System.out.println("empty.."); 
            return;
            
        } else {
            System.out.println(request.getParameter("like"));
           like=request.getParameter("like");
        
           }
           
           String table="";
           table = request.getParameter("search");
           
           
              Statement s = conn.createStatement ();
			String query= "Select * from "+table+" where name like '%"+like+"%' order by name limit 5";
			System.out.println(query);
			s.executeQuery (query);
			ResultSet rs = s.getResultSet ();
              
              
  ArrayList ar = new ArrayList();
  if (table.equals("bands")) {
		while (rs.next ())
		{
			int idVal = rs.getInt ("id");
			String tagname = rs.getString ("name");
			System.out.println(idVal+" "+tagname);
			ar.add(new ajax.Tag(idVal,tagname));									
		}
		rs.close ();
		s.close ();
           String foo="";
           for (i=0; i<ar.size();i++) {
           String the_name=(((ajax.Tag)(ar.get(i))).getTagName());
           foo=(the_name+"<br>");

           out.println("<div class=\"suggest_link\" onmouseover=\"javascript:suggestOver(this);\" onmouseout=\"javascript:suggestOut(this);\" onclick=\"javascript:getBand('"+the_name+"');hideDiv('"+table+"'); enableForum(); \" >"+foo+"</div>");
           }
           
           if (ar.size()==5)
           {
                      
           out.println("<div class=\"suggest_link\" onmouseover=\"javascript:suggestOver(this);\" onmouseout=\"javascript:suggestOut(this);\" >......<br></div>");
           }
           
           
           }
          
          
    if (table.equals("tags")) {
		while (rs.next ())
		{		  
			int idVal = rs.getInt ("id");
			String tagname = rs.getString ("name");
			System.out.println(idVal+" "+tagname);
			ar.add(new ajax.Tag(idVal,tagname));									
		}
		rs.close ();
		s.close ();
           String foo="";
           for ( i=0; i<ar.size();i++)           {
           String the_name=(((ajax.Tag)(ar.get(i))).getTagName());
           foo=(the_name+"<br>");


           out.println("<div class=\"suggest_link\" onmouseover=\"javascript:suggestOver(this);\" onmouseout=\"javascript:suggestOut(this);\" onclick=\"javascript:getBandsFromTag('"+the_name+"','main_window');hideDiv('"+table+"');  disableForum(); \"   >"+foo+"</div>");

           }
           
           if (ar.size()==5)
           {
           
           out.println("<div class=\"suggest_link\" onmouseover=\"javascript:suggestOver(this);\" onmouseout=\"javascript:suggestOut(this);\" >......<br></div>");
           }
           
           
           }
           
  if (table.equals("albums")) {
		while (rs.next ())
		{
		
		  count++;
			int idVal = rs.getInt ("id");
			String tagname = rs.getString ("name");
			String date = rs.getDate("date").toString().substring(0,4);
			int band_id = rs.getInt("band_id");
			String band=""; 
			
			Statement s2 = conn.createStatement ();
			String query2= ("select name from bands where id ="+band_id);
			System.out.println(query2);			
			s2.executeQuery (query2);
			ResultSet rs2 = s2.getResultSet();
			
			while(rs2.next()){
			band=rs2.getString("name");
			}
			
			String foo="";
			
			
			
           foo=("<b>"+tagname+"</b><font size=2> ("+band+","+date+")</font><br>");
          System.out.println(foo);
           out.println("<div class=\"suggest_link\" onmouseover=\"javascript:suggestOver(this);\" onmouseout=\"javascript:suggestOut(this);\" onclick=\"javascript:getBand('"+band+"');hideDiv('"+table+"'); enableForum(); \" >"+foo+"</div>");
				}					
			
		
		
		rs.close ();
		s.close ();
		

           
           if (count==5)
           {
          
           out.println("<div class=\"suggest_link\" onmouseover=\"javascript:suggestOver(this);\" onmouseout=\"javascript:suggestOut(this);\" >......<br></div>");
           }
           count=0;
           
           }           
           
   if (table.equals("tracks")) {
		while (rs.next ())
		{
		  
		  count++;
			int idVal = rs.getInt ("id");
			String tagname = rs.getString ("name");
			String albumname="";
			int album_id = rs.getInt("album_id");
			String band=""; 
			
			Statement s2 = conn.createStatement ();
			String query2= ("select name from bands where id in (select band_id from albums where id ="+album_id+")");
			System.out.println(query2);			
			s2.executeQuery (query2);
			ResultSet rs2 = s2.getResultSet();
			
			while(rs2.next()){
			band=rs2.getString("name");
			}
			
			s2 = conn.createStatement ();
			query2= ("select name from albums where id ="+album_id+"");
			System.out.println(query2);			
			s2.executeQuery (query2);
			rs2 = s2.getResultSet();
			
			while(rs2.next()){
			albumname=rs2.getString("name");
			}
			
			
			String foo="";
			
			
			
           foo=("<b>"+tagname+"</b><br><font size=1> ("+band+","+albumname+")</font><br>");
          System.out.println(foo);
           out.println("<div class=\"suggest_link\" onmouseover=\"javascript:suggestOver(this);\" onmouseout=\"javascript:suggestOut(this);\" onclick=\"javascript:getBand('"+band+"');hideDiv('"+table+"'); enableForum(); \" >"+foo+"</div>");
				}					
			
		
		
		rs.close ();
		s.close ();
		
    
           System.out.println(count);
           if (count==5)
           {
          
           out.println("<div class=\"suggest_link\" onmouseover=\"javascript:suggestOver(this);\" onmouseout=\"javascript:suggestOut(this);\" >......<br></div>");
           }
           count=0;
           
           }          
           
           
           
          }
  
  
           
	
           
                      catch (Exception e)
           {
               System.err.println ("database error");
               System.err.println (e);
           }
           
           finally
           {
               if (conn != null)
               {
                   try
                   {
                       conn.close ();
                       System.out.println ("Database connection terminated");
                   }
                   catch (Exception e) { /* ignore close errors */ }
               }
           }
           

%>




    