Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <!--PHP BLOCK-->
- <?php
- $query2 = "SELECT * FROM LOCATIONS";
- $all_locations = filterTable($query2);
- $query3 = "SELECT * FROM PERFORMERS";
- $all_bands = filterTable($query3);
- // Default Error message
- $errorMessage = "There are no show times for the band / venue 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.Showtime AS \"Show Time\"
- FROM SHOWS INNER JOIN PERFORMERS ON SHOWS.BandId = PERFORMERS.BandId
- INNER JOIN LOCATIONS ON SHOWS.LocationId=LOCATIONS.LocationId
- WHERE SHOWS.$searchColumn = $searchData;";
- $queryResults = filterTable($query1);
- }
- else
- {
- $query1 = "SELECT * FROM UPCOMING_EVENTS WHERE Location = -1;";
- $queryResults = filterTable($query1);
- }
- }
- // we are not searching, get everything from the database
- else {
- $query1 = "SELECT * 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"; // where ?? is your id
- $dbpass = "laiximuk"; // replace with your 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>
- table,tr,th,td
- {
- border: 1px solid black;
- }
- </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'><br>
- <!--Sumbit Button-->
- <input type="submit" name="submitVal" value="Filter"><br><br>
- <div>
- <!--Band Table-->
- <table style="float: left">
- <tr>
- <th>BandId</th>
- <th>Band</th>
- <th>Genre</th>
- </tr>
- <!-- Populate table from mysql database -->
- <?php while($row = mysqli_fetch_array($all_bands)):?>
- <tr>
- <td><?php echo $row['BandId'];?></td>
- <td><?php echo $row['BandName'];?></td>
- <td><?php echo $row['Genre'];?></td>
- </tr>
- <?php endwhile;?>
- </table>
- <!--Locations Table-->
- <table style="float: left">
- <tr>
- <th>Location Id</th>
- <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['LocationId'];?></td>
- <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>
- <!--Upcoming Events Resuls-->
- <?php if(mysqli_num_rows($queryResults) > 0):?>
- Below are the Show Times for your band and venue!
- <table>
- <tr>
- <th>Venue</th>
- <th>Location</th>
- <th>Band</th>
- <th>Album</th>
- <th>Show Time</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['Show Time'];?></td>
- </tr>
- <?php endwhile;?>
- </table>
- <?php else :?>
- <?php echo $errorMessage ?>
- <?php endif;?>
- </table>
- </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-->
Add Comment
Please, Sign In to add comment