<!--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", "Lab 3.8", "Track Display by Genre - Limit query results with pagination");
?>
<div><!--div ends on line 179-->
<div id="notifyEnd">
</div>
<?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
$trackCountQuery = "select count(*) as quantityvar from Track";//cnt contains number of records
$trackCountResult=mysqli_query($conn,$trackCountQuery);
$trackCountArray = mysqli_fetch_assoc($trackCountResult);
$trackCount=$trackCountArray['quantityvar']; //$trackQuantity 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 > $trackCount) {
$next = 0;
}
// check for user input
// initialize the genre to 0 in case no genre was selected.
$genreSelection = 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['genreSubmit'])) {
$genreSelection = $_POST['genreDropdown'];
}
elseif (isset($_GET['genrePaging'])) {
$genreSelection = $_GET['genrePaging'];
}
// 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
?>
<form method="post" action="3.8.php">
<p><label for="genre">Genre:</label>
<!--FORM LINE 1/////////////////DROP DOWN SELECTION/////////////////////////////----->
<select name="genreDropdown" id="genreDropdown">
<?php
// create a select option list from the genre table
$genreQuery = "select GenreId, Name from Genre";
$genreQueryResult = mysqli_query($conn,$genreQuery);
// check for errors
if (!$genreQueryResult) {
die(mysqli_error($conn));
}
if (mysqli_num_rows($genreQueryResult) > 0) {
while ($genreArray = mysqli_fetch_assoc($genreQueryResult)) {
echo "<option value='" . $genreArray['GenreId'] . "'";
if ($genreSelection==$genreArray['GenreId']) {
echo " selected";
// 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
}
echo ">" . $genreArray['Name'] . "</option>";
}
}
?>
</select>
<input type="submit" name="genreSubmit" value="Select Genre">
</p>
</form>
<table>
<tr><th>Name</th><th>UnitPrice</th></tr>
<?php
$outputCounter = 0;
// create the query. Use the offset as the starting row and limit the rows to 20.
$trackTableQuery = "Select Name, UnitPrice from Track";
// if a genre is found from the form or querystring, add the where clause for the selected rep
if ($genreSelection > 0) {
$trackTableQuery = $trackTableQuery ." where GenreId = $genreSelection";
}
// add the limit clause (must be after the where clause)
$trackTableQuery = $trackTableQuery." 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
$trackTableResult = mysqli_query($conn,$trackTableQuery);
// check for errors
if (!$trackTableResult) {
die(mysqli_error($conn));
}
// check for results
if (mysqli_num_rows($trackTableResult)> 0) {
// loop through results and display
while ($trackTableArray = mysqli_fetch_assoc($trackTableResult)) {
echo "<tr><td>".$trackTableArray['Name']."</td>";
echo "<td>".$trackTableArray['UnitPrice']."</td></tr>";
$outputCounter++;
}
//test data
echo "genreSelection = " . $genreSelection . "<br>";
echo "outputcounter = " . $outputCounter . "<br>";
echo "offset = " . $offset . "<br>";
echo "previous = " . $prev . "<br>";
echo "next = " . $next . "<br>";
}
//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.8.php?offset=<?php echo $prev;?>&genrePaging=<?php echo $genreSelection;?>'>PREVIOUS</a></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td id="toggleLink"><a href='3.8.php?offset=<?php echo $next;?>&genrePaging=<?php echo $genreSelection;?>'>NEXT</a></td>
</tr>
</table>
</div><!--end div from line 22-->
<?php
if($outputCounter < 25){
?>
<script type="text/javascript">
function myFunction2(){
document.getElementById("notifyEnd").innerHTML = "No more results!!!!!!!";
document.getElementById("toggleLink").innerHTML = "END";
}
myFunction2();
</script>
<?php
}
?>
<?php
printFootWithCodeExample('3.8code.php');
?>