API tools faq
paste
Advertisement
Guest User

index001

a guest
Apr 25th, 2017
89
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 6.06 KB | None | 0 0
raw download clone embed print report
  1. <!--PHP BLOCK-->
  2. <?php
  3.  
  4. $query2 = "SELECT * FROM LOCATIONS";
  5. $all_locations = filterTable($query2);
  6.  
  7. $query3 = "SELECT * FROM PERFORMERS";
  8. $all_bands = filterTable($query3);
  9.  
  10. // Default Error message
  11. $errorMessage = "There are no show times for the band / venue you selected!";
  12.  
  13. // If we are searching the database
  14. if(isset($_POST['submitVal']))
  15. {
  16.     // Get the values from the search form
  17.     $searchColumn = $_POST['selectVal'];
  18.     $searchData = $_POST['searchVal'];
  19.  
  20.     // Check if dropdown was selected and text was entered
  21.     $validate = (!$searchData == "");
  22.  
  23.     if ($validate) {
  24.  
  25.         // Table to search from
  26.         if ($searchColumn == "Venue" || $searchColumn ==  "City" || $searchColumn ==  "State") {
  27.             $searchTable = "LOCATIONS";
  28.         }
  29.         else {
  30.             $searchTable = "PERFORMERS";
  31.         }
  32.  
  33.         // Perform a query search based on the two searched values
  34.         $query1 = "SELECT LOCATIONS.Venue AS \"Venue\",
  35.                         CONCAT(LOCATIONS.City, \", \", LOCATIONS.State) AS \"Location\",
  36.                         PERFORMERS.BandName AS \"Band\",
  37.                         PERFORMERS.Genre AS \"Genre\",
  38.                         PERFORMERS.Album_Showing AS \"Album\",
  39.                         SHOWS.Showtime AS \"Show Time\"
  40.                  FROM SHOWS INNER JOIN PERFORMERS ON SHOWS.BandId = PERFORMERS.BandId
  41.                       INNER JOIN LOCATIONS ON SHOWS.LocationId=LOCATIONS.LocationId
  42.                       WHERE $searchTable.$searchColumn = $searchData;";
  43.  
  44.         $queryResults = filterTable($query1);
  45.     }
  46.     else
  47.     {
  48.         $query1 = "SELECT * FROM UPCOMING_EVENTS WHERE Location = -1;";
  49.         $queryResults = filterTable($query1);
  50.     }
  51.  
  52. }
  53. // we are not searching, get everything from the database
  54. else {
  55.     $query1 = "SELECT * FROM UPCOMING_EVENTS WHERE Location = -1;";
  56.     $queryResults = filterTable($query1);
  57. }
  58.  
  59. // function to connect and execute the query
  60. function filterTable($query)
  61. {
  62.     // 1. Create a database connection
  63.     $dbhost = "ecsmysql";
  64.     $dbuser = "cs332u4";  // where ?? is your id
  65.     $dbpass = "laiximuk"; // replace with your password
  66.     $dbname = "cs332u4";
  67.     $dbconnection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
  68.  
  69.     // 2. Check if the connection is ok
  70.     if (mysqli_connect_errno()) {
  71.         die("Database connection failed: " .
  72.             mysqli_connect_error() . " (" > mysqli_connect_errno() . ")" );
  73.     }
  74.  
  75.     // 3. Execute the query
  76.     $filter_Result = mysqli_query($dbconnection, $query);
  77.  
  78.     // Check if there is a query error
  79.     if (!$filter_Result) {
  80.         die("Database query \"$query\" failed.");
  81.     }
  82.  
  83.     // Return the query result
  84.     return $filter_Result;
  85.  
  86.     $dbconnection->close();
  87. }
  88. ?>
  89. <!--END OF PHP BLOCK-->
  90.  
  91.  
  92. <!--HTML BLOCK-->
  93. <!DOCTYPE html>
  94. <html>
  95. <head>
  96.     <title>Show Times Search!</title>
  97.     <style>
  98.         table,tr,th,td
  99.         {
  100.             border: 1px solid black;
  101.         }
  102.     </style>
  103. </head>
  104.  
  105. <body>
  106.  
  107. <!--Search Form-->
  108. <form method="post">
  109.  
  110.     <!--Dropdown List    -->
  111.     <select name="selectVal">
  112.         <option value="BandName">Band Name</option>
  113.         <option value="Genre">Genre</option>
  114.         <option value="Venue">Venue</option>
  115.         <option value="City">City</option>
  116.         <option value="State">State</option>
  117.     </select>
  118.  
  119.     <!--Edit text box-->
  120.     <input type='text' name='searchVal' placeholder='Enter text here...' size='50'><br>
  121.  
  122.     <!--Sumbit Button-->
  123.     <input type="submit" name="submitVal" value="Filter"><br><br>
  124.  
  125.     <div>
  126.         <!--Band Table-->
  127.         <table style="float: left">
  128.             <tr>
  129.                 <th>BandId</th>
  130.                 <th>Band</th>
  131.                 <th>Genre</th>
  132.             </tr>
  133.  
  134.             <!-- Populate table from mysql database -->
  135.             <?php while($row = mysqli_fetch_array($all_bands)):?>
  136.                 <tr>
  137.                     <td><?php echo $row['BandId'];?></td>
  138.                     <td><?php echo $row['BandName'];?></td>
  139.                     <td><?php echo $row['Genre'];?></td>
  140.                 </tr>
  141.             <?php endwhile;?>
  142.         </table>
  143.  
  144.         <!--Locations Table-->
  145.         <table style="float: left">
  146.             <tr>
  147.                 <th>Location Id</th>
  148.                 <th>Venue</th>
  149.                 <th>City</th>
  150.                 <th>State</th>
  151.             </tr>
  152.  
  153.             <!-- Populate table from mysql database -->
  154.             <?php while($row = mysqli_fetch_array($all_locations)):?>
  155.                 <tr>
  156.                     <td><?php echo $row['LocationId'];?></td>
  157.                     <td><?php echo $row['Venue'];?></td>
  158.                     <td><?php echo $row['City'];?></td>
  159.                     <td><?php echo $row['State'];?></td>
  160.                 </tr>
  161.             <?php endwhile;?>
  162.         </table>
  163.     </div>
  164.  
  165.     <br><br><br>
  166.     <br><br><br>
  167.     <br><br><br>
  168.     <br><br><br>
  169.     <br><br><br>
  170.     <br>
  171.  
  172.     <!--Upcoming Events Resuls-->
  173.     <?php if(mysqli_num_rows($queryResults) > 0):?>
  174.         Below are the Show Times for your band and venue!
  175.         <table>
  176.             <tr>
  177.                 <th>Venue</th>
  178.                 <th>Location</th>
  179.                 <th>Band</th>
  180.                 <th>Album</th>
  181.                 <th>Show Time</th>
  182.             </tr>
  183.  
  184.             <?php while($row = mysqli_fetch_array($queryResults)):?>
  185.             <tr>
  186.                 <td><?php echo $row['Venue'];?></td>
  187.                 <td><?php echo $row['Location'];?></td>
  188.                 <td><?php echo $row['Band'];?></td>
  189.                 <td><?php echo $row['Album'];?></td>
  190.                 <td><?php echo $row['Show Time'];?></td>
  191.             </tr>
  192.             <?php endwhile;?>
  193.         </table>
  194.     <?php else :?>
  195.         <?php echo $errorMessage ?>
  196.     <?php endif;?>
  197.     </table>
  198. </form>
  199.  
  200. </body>
  201.  
  202.  
  203. <!--Cleanup-->
  204. <?php
  205. // 4. Release returned result
  206. mysqli_free_result($queryResults);
  207. mysqli_free_result($all_bands);
  208. mysqli_free_result($all_locations);
  209. ?>
  210.  
  211. </html>
  212. <!--END OF HTML BLOCK-->
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!