Untitled

<!--PHP BLOCK-->
<?php

$query2 = "SELECT Venue, City, State FROM LOCATIONS";
$all_locations = filterTable($query2);

$query3 = "SELECT BandName, Genre FROM PERFORMERS";
$all_bands = filterTable($query3);

// Default Error message
$errorMessage = "There are no show times for the criteria you selected!";

// If we are searching the database
if(isset($_POST['submitVal']))
{
    // Get the values from the search form
    $searchColumn = $_POST['selectVal'];
    $searchData = $_POST['searchVal'];

    // Check if dropdown was selected and text was entered
    $validate = (!$searchData == "");

    if ($validate) {

        // Table to search from
        if ($searchColumn == "Venue" || $searchColumn ==  "City" || $searchColumn ==  "State") {
            $searchTable = "LOCATIONS";
        }
        else {
            $searchTable = "PERFORMERS";
        }

        // Perform a query search based on the two searched values
        $query1 = "SELECT LOCATIONS.Venue AS \"Venue\",
                         CONCAT(LOCATIONS.City, \", \", LOCATIONS.State) AS \"Location\",
                         PERFORMERS.BandName AS \"Band\",
                         PERFORMERS.Genre AS \"Genre\",
                         PERFORMERS.Album_Showing AS \"Album\",
                         SHOWS.Show_Time AS \"Show_Time\",
                         SHOWS.Show_Day AS \"Show_Day\"
                  FROM SHOWS INNER JOIN PERFORMERS ON SHOWS.BandId = PERFORMERS.BandId
                       INNER JOIN LOCATIONS ON SHOWS.LocationId=LOCATIONS.LocationId
                       WHERE $searchTable.$searchColumn = \"$searchData\";";

        $queryResults = filterTable($query1);
    }
    else
    {
        $query1 = "SELECT Venue, Location, Band, Album, Genre, Show_Time, Show_Day FROM UPCOMING_EVENTS WHERE Location = -1;";
        $queryResults = filterTable($query1);
    }

}
// we are not searching, get everything from the database
else {
    $query1 = "SELECT Venue, Location, Band, Album, Genre, Show_Time, Show_Day FROM UPCOMING_EVENTS WHERE Location = -1;";
    $queryResults = filterTable($query1);
}

// function to connect and execute the query
function filterTable($query)
{
    // 1. Create a database connection
    $dbhost = "ecsmysql";
    $dbuser = "cs332u4";  // Charles Bucher's ID
    $dbpass = "laiximuk"; // Charles Bucher's password
    $dbname = "cs332u4";
    $dbconnection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

    // 2. Check if the connection is ok
    if (mysqli_connect_errno()) {
        die("Database connection failed: " .
            mysqli_connect_error() . " (" > mysqli_connect_errno() . ")" );
    }

    // 3. Execute the query
    $filter_Result = mysqli_query($dbconnection, $query);

    // Check if there is a query error
    if (!$filter_Result) {
        die("Database query \"$query\" failed.");
    }

    // Return the query result
    return $filter_Result;

    $dbconnection->close();
}
?>
<!--END OF PHP BLOCK-->


<!--HTML BLOCK-->
<!DOCTYPE html>
<html>
<head>
    <title>Show Times Search!</title>
    <style type="text/css">
                /* Begin Styled HTML Table */
                .tftable {font-size:12px;color:#333333;border-width:1px;border-color:#729ea5;border-collapse:collapse;float:left;}
                .tftable th {font-size:12px;background-color:#acc8cc;border-width:1px;padding:8px;border-style:solid;border-color:#729ea5;text-align:left;}
                .tftable tr {background-color:#ffffff;}
                .tftable td {font-size:12px;border-width:1px;padding:8px; border-style:solid;border-color:#729ea5;white-space: nowrap;}
                .absorbing-column {  width: 30%;  }
        </style>
</head>

<body>

<!--Search Form-->
<form method="post">

    <!--Dropdown List    -->
    <select name="selectVal">
        <option value="BandName">Band Name</option>
        <option value="Genre">Genre</option>
        <option value="Venue">Venue</option>
        <option value="City">City</option>
        <option value="State">State</option>
    </select>

    <!--Edit text box-->
    <input type='text' name='searchVal' placeholder='Enter text here...' size='50'>

    <!--Sumbit Button-->
    <input type="submit" name="submitVal" value="Filter"><br><br>

    <div>
        <!--Band Table-->
        <table class="tftable" border="1" table-layout="fixed">
            <tr>
                <th class="absorbing-column">Band</th>
                <th>Genre</th>
            </tr>

            <!-- Populate table from mysql database -->
            <?php while($row = mysqli_fetch_array($all_bands)):?>
                <tr>
                    <td><?php echo $row['BandName'];?></td>
                    <td><?php echo $row['Genre'];?></td>
                </tr>
            <?php endwhile;?>
        </table>

        <!--Locations Table-->
        <table class="tftable" border="1" table-layout="fixed">
            <tr>
                <th>Venue</th>
                <th>City</th>
                <th>State</th>
            </tr>

            <!-- Populate table from mysql database -->
            <?php while($row = mysqli_fetch_array($all_locations)):?>
                <tr>
                    <td><?php echo $row['Venue'];?></td>
                    <td><?php echo $row['City'];?></td>
                    <td><?php echo $row['State'];?></td>
                </tr>
            <?php endwhile;?>
        </table>
    </div>

    <br><br><br>
    <br><br><br>
    <br><br><br>
    <br><br><br>
    <br><br><br>
    <br><br><br>
    <br><br><br>

    <!--Upcoming Events Resuls-->
    <?php if(mysqli_num_rows($queryResults) > 0):?>
        Below are the Show Times for your selected criteria!

        <div>
            <table class="tftable" border="1" table-layout="auto">
                <tr>
                    <th>Venue</th>
                    <th class="absorbing-column">Location</th>
                    <th class="absorbing-column">Band</th>
                    <th>Album</th>
                    <th>Genre</th>
                    <th>Show Time</th>
                    <th>Show Day</th>
                </tr>

                <?php while($row = mysqli_fetch_array($queryResults)):?>
                    <tr>
                        <td><?php echo $row['Venue'];?></td>
                        <td><?php echo $row['Location'];?></td>
                        <td><?php echo $row['Band'];?></td>
                        <td><?php echo $row['Album'];?></td>
                        <td><?php echo $row['Genre'];?></td>
                        <td><?php echo $row['Show_Time'];?></td>
                        <td><?php echo $row['Show_Day'];?><td>
                    </tr>
                <?php endwhile;?>
            </table>
        </div>

    <?php else :?>
        <?php echo $errorMessage ?>
    <?php endif;?>

</form>

</body>


<!--Cleanup-->
<?php
// 4. Release returned result
mysqli_free_result($queryResults);
mysqli_free_result($all_bands);
mysqli_free_result($all_locations);
?>

</html>
<!--END OF HTML BLOCK-->