Database CGI

      #!/usr/bin/perl
      use strict;
      use DBI;
      use CGI;

      # setup database connection variables
      my $user = "some username goes here";
      my $password = "some password goes here";
      my $host = "the host you want to connect to";
      my $driver = "mysql";
      
      # connect to database
      my $dsn = "DBI:$driver:database=cis430;host=$host";
      my $dbh = DBI->connect($dsn, $user, $password);

      # setup CGI handle
      my $cgi = new CGI;

      # start HTML
      print $cgi->header . $cgi->start_html('Some Tunes');

      # handle any queries that have been sent our way
      my $artist = validate($cgi->param('Artist'));
      my $song = validate($cgi->param('Song'));
      my $album = validate($cgi->param('Album'));
      if ($cgi->param('Query')) {
        my $sql = "select filename, artist, album, track, song from mp3s ";
        $sql .= "where artist like '$artist' "
          if (length $artist > 1);
        $sql .= "where song like '$song' "
          if (length $song > 1);
        $sql .= "where album like '$album' "
          if (length $album > 1);
        $sql .= "order by artist, album, track";
        my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
        if (@$rows) {
          print "<table border=1 cellspacing=0 cellpadding=3><tr>" .
                "<th>Filename</th><th>Artist</th><th>Album</th><th>Track</th><th>Song</th></tr>";
          foreach my $row (@$rows) {
            print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>\n";
          }
          print "</table>\n";
        }
        else {
          print "<p><i>No matches found</i></p>\n";
        }
      }
 
      # display form
      print <<HTML;
      <form method="get" action="database.pl">
      <h3>Search for songs</h3>
      <blockquote>
        You can use a percent (%) for wild cards, and you can
        fill in as many or as few categories as you like.
        Search criteria must be larger than 2 characters,
        otherwise it is ignored.
        </blockquote>
      <h4>Artist</h4>
      <p><input type="text" name="Artist" value="$artist"></p>
      <h4>Song</h4>
      <p><input type="text" name="Song" value="$song"></p>
      <h4>Album</h4>
      <p><input type="text" name="Album" value="$album"></p>
      <p><input type="submit" name="Query" value="Search Tunes"></p>
      HTML

      # end HTML
      print $cgi->end_html();

      # disconnect from database
      $dbh->disconnect();
      exit(0);

      # validate user input to clean out garbage and sneaky people
      sub validate {
        my $string = shift;
        # get rid of all non-letter, non-numerical characters and percents
        $string =~ s/[^A-Za-z0-9%]//g;
        return $string;
      }