<!--display records from the TRACK table
need a form with a dropdown list from the GENRE table.. genreid is val and genrename is drop text..
if user submits the form with a selected genre...
limit the TRACKS displayed to those who have a genre id seleced from the form..
only display 25 tracks at a time..
use paging to allow the user to scroll forward and back..
for each track, display track NAME and UNIT PRICE
-->
<?php
require_once '../mainfunctions.php';
printHead("3", "Chapter 3 Test 4", "Track Display by Genre");
?>
<div><!--div ends on line 179-->
<?php
$conn = createConn();
// offset value is how many results we skip before printing....
// check to see if there is an offset in the querystring. If not set offset to 0.
// NOTE $_GET['offset'] would be coming from PREV or NEXT buttons..depending on the button it was sent from, the value will either be contents of $prev or $next..
if (isset($_GET['offset'])){
$offset=$_GET['offset'];}
else{
$offset=0;}
//set to 0 if we have selected a new genre aka prev or next was not clicked, dropdown was submitted instead
// calculate the offset for the previous link. If < 0, set to 0... result is that you cannot go back from first page of any genre...
// this subtraction decrements the $prev contents
$prev = $offset - 25;
if ($prev<0) {
$prev=0;
}
// first run on a new genre will always result in $prev=0
// calculate the offset for the NEXT link
$next = $offset + 25;
// see how many records are in the table
$query = "select count(*) as quantityvar from Track";//cnt contains number of records
$result=mysqli_query($conn,$query);
$row = mysqli_fetch_assoc($result);
$quantity=$row['quantityvar']; //$quantity now contains total number of records in Track table
// if the next offset is greater than the number of records,
// set it to 0 to loop back to the beginning of the table
if ($next > $quantity) {
$next = 0;
}
// check for user input
// initialize the genre to 0 in case no genre was selected.
$gen = 0;
// first check to see if the form DROPDOWN was submitted.
//If so, get the genre from the form DROPDOWN SELECTION
//data will be sent via POST in this case
if (isset($_POST['genre'])){
$gen= $_POST['genre'];}
elseif
// if the form was not submitted, check the querystring to see if the genre was passed as part of the paging link
//paging link is PREVIOUS or NEXT.. should stay on the same genre in the dropdown
//data will be sent via GET in this case
(isset($_GET['genre'])) {
$gen=$_GET['genre'];}
?>
<form method="post" action="3.8test4.php">
<p><label for="genre">Genre:</label>
<!--FORM LINE 1/////////////////DROP DOWN SELECTION/////////////////////////////----->
<select name="genre" id="genre">
<?php
// create a select option list from the Genre table
$query = "select GenreId, Name from Genre";
$result = mysqli_query($conn,$query);
// check for errors
if (!$result) {
die(mysqli_error($conn));
}
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo "<option value='".$row['GenreId']."'";
// if a genre has been selected, show that genre as the preselected option
// NOTE that on first run the first item in genre list will show as preselected by default
if ($gen==$row['GenreId']) {echo " selected";}
echo ">".$row['Name']."</option>";
}
}
?>
</select>
<input type="submit" value="Select Genre">
</p>
</form>
<table>
<tr><th>Name</th><th>UnitPrice</th></tr>
<?php
// create the query. Use the offset as the starting row and limit the rows to 20.
$query = "Select Name, UnitPrice from Track";
// if a genre is found from the form or querystring, add the where clause for the selected rep
if ($gen>0) {
$query=$query." where GenreId =$gen";
}
// add the limit clause (must be after the where clause)
$query = $query." Limit $offset, 25"; //skip amt of $offset and only produce 25 records max
//if you are not coming from paging link, but have clicked 'select genre' instead...
//your offset is going to be 0 so you'll start from beginning of results
// run the query
$result = mysqli_query($conn,$query);
// check for errors
if (!$result) {
die(mysqli_error($conn));
}
// check for results
if (mysqli_num_rows($result)> 0) {
// loop through results and display
while ($row = mysqli_fetch_assoc($result)) {
echo "<tr><td>".$row['Name']."</td>";
echo "<td>".$row['UnitPrice']."</td></tr>";
}
}
//NOTE IF OFFSET EXCEEDS AMOUNT OF RECORDS FOR ANY GIVEN GENRE, THE TABLE OUTPUT WILL BE BLANK
// NEED CLOSING BCKET HERE SO THAT THE PRVS AND NEXT BUTTONS ARE NOT A CONDITION OF QUERY OUTPUT
//BECAUSE THE QUERY WILL PRODUCE NOTHING AFTER 2 OR 3 PAGES IF WE ARE SKIPPING AMOUNT OF OFFSET 25 PER PAGE BUT THE GENRE HAS LESS RESULTSS THAN THAT.. THIS WILL ALLOW US TO USE THE BACK BUTTON AFTER RESULTS DONT PRINT ANYMORE
// add the Previous and Next links in the last row of the table.
// include the offset and the genre in the querystring
// send data via GET... will show in url
;?>
<tr id="links">
<td><a href='3.8test4.php?offset=<?php echo $prev?>&genre=<?php echo $gen?>'>PREVIOUS</a></td>
<td></td><td> </td><td> </td><td> </td><td> </td>
<td><a href='3.8test4.php?offset=<?php echo $next?>&genre=<?php echo $gen?>'>NEXT</a></td>
</tr>
</table>
</div><!--end div from line 22-->
<?php
printFootWithCodeExample('3.8test4code.php');
?>