certainty.php

<?php
require_once('db.php');

function getValues($struc) {
    $query = "SELECT DISTINCT option_name FROM Options WHERE struc = ? ORDER BY option_name";
    try {
        $stmt = $GLOBALS["link"]->prepare($query);
        $stmt->bindParam(1,$struc, PDO::PARAM_STR);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $stmt->execute();
        $result = $stmt->fetchAll();
    }
    catch(PDOException $e) {
        $error_message = $e->getMessage();
        die($error_message);
    }

    return $result;
}
?>

<html>
<head>
	<script>
		$(function() {
		$( ".datepicker" ).datepicker({ dateFormat: 'yy-mm-dd' });
		});
	</script>

	<title>Filter Results</title>

</head>
<body>


<form action="certainty.php">
	<div class='pure-control-group'>
    <label for="species">Species</label>
    <select name='species'>
	    <option value='any'>Any</option>
<?php
    $strucArray = array("mammal","bird","noanimal","notinlist");
    foreach($strucArray as $struc) {
        echo "<optgroup label='" . ucwords($struc) . "'>";
        foreach(getValues($struc) as $specie) {
            echo "<option value='" . strip_tags($specie['option_name']) . "'>" . strip_tags($specie['option_name']) . "</option>";
        }
        echo "</optgroup>";
    }
?>
    </select>
	</div>
	<div class='pure-control-group'>
	    <label for='sex'>Sex</label>
	    <select name='sex'>
	    <option value='any'>Any</option>
	    <?php
	    foreach(getValues('gender') as $gender) {
	        echo "<option value='" . strip_tags($gender['option_name']) . "'>" . strip_tags($gender['option_name']) . "</option>";
	    }
	    ?>
	    </select>
	</div>
	<div class='pure-control-group'>
	    <label for='age'>Age</label>
	    <select name='age'>
	    <option value='any'>Any</option>
	    <?php
	    foreach(getValues('age') as $age) {
	        echo "<option value='" . strip_tags($age['option_name']) . "'>" . strip_tags($age['option_name']) . "</option>";
	    }
	    ?>
	    </select>
	</div>
	<div class='pure-control-group'>
	    <label for='quality'>Quality</label>
	    <select name='quality'>
	    <option value='any'>Any</option>
	    <?php
	    foreach(getValues('quality') as $quality) {
	        echo "<option value='" . strip_tags($quality['option_name']) . "'>" . strip_tags($quality['option_name']) . "</option>";
	    }
	    ?>
	    </select>
	</div>
	<div class='pure-control-group'>
	    <label for='camera'>Camera</label>
	    <select name='camera'>
	    <option value='any'>Any</option>
	    <?php
	    foreach(getValues('camera') as $camera) {
	        echo "<option value='" . strip_tags($camera['option_name']) . "'>" . strip_tags($camera['option_name']) . "</option>";
	    }
	    ?>
	    </select>
	</div>
	<div class='pure-control-group'>
	    <label for='water'>Water</label>
	    <select name='water'>
	    <option value='any'>Any</option>
	    <?php
	    foreach(getValues('water') as $water) {
	        echo "<option value='" . strip_tags($water['option_name']) . "'>" . strip_tags($water['option_name']) . "</option>";
	    }
	    ?>
	    </select>
	</div>
	<div class='pure-control-group'>
	    <label for='landuse'>Land Use</label>
	    <select name='landuse'>
	    <option value='any'>Any</option>
	    <?php
	    foreach(getValues('landuse') as $landuse) {
	        echo "<option value='" . strip_tags($landuse['option_name']) . "'>" . strip_tags($landuse['option_name']) . "</option>";
	    }
	    ?>
	    </select>
	</div>
	<div class='pure-control-group'>
	    <label for='habitat'>Habitat</label>
	    <select name='habitat'>
	    <option value='any'>Any</option>
	    <?php
	    foreach(getValues('habitat') as $habitat) {
	        echo "<option value='" . strip_tags($habitat['option_name']) . "'>" . strip_tags($habitat['option_name']) . "</option>";
	    }
	    ?>
	    </select>
	</div>
	<!--
	<div class='pure-control-group'>
	    <label for='habitatx'>HabitatX</label>
	    <select name='habitatx'>
	    <option value='any'>Any</option>
	    <?php
	    foreach(getValues('habitatx') as $habitatx) {
	        echo "<option value='" . strip_tags($habitatx['option_name']) . "'>" . strip_tags($habitatx['option_name']) . "</option>";
	    }
	    ?>
	    </select>
	</div>
	 -->
	<div class='pure-control-group'>
	    <label for='purpose'>Purpose</label>
	    <select name='purpose'>
	    <option value='any'>Any</option>
	    <?php
	    foreach(getValues('purpose') as $purpose) {
	        echo "<option value='" . strip_tags($purpose['option_name']) . "'>" . strip_tags($purpose['option_name']) . "</option>";
	    }
	    ?>
	    </select>
	</div>
	<div class='pure-control-group'>
	    <label for='startdate'>Start Date</label>
	    <input type='text' name='startdate' class='datepicker'>
	</div>
	<div class='pure-control-group'>
	    <label for='enddate'>End Date</label>
	    <input type='text' name='enddate' class='datepicker'>
	</div>

	<div>
		<label for='minVotes'>Minimum Votes</label>
		<input type="text" name="minVotes">
	</div>
	<div>
		<label for="threshold">Evenness Threshold</label>
		<input type="text" name="threshold"><br/><br/>
	</div>
	<input type="submit" value="Submit">

</form>

<?php

$servername = "myeusql.dur.ac.uk";
$username = "sxfm87";
$password = "or54ange";
$dbname = "Psxfm87_biodiversity";

$minVotes = $_REQUEST["minVotes"];
$threshold = $_REQUEST["threshold"];

if ($minVotes != '' and $threshold != '') {
	if ($minVotes >= 0) {
		if ($threshold >= 0 and $threshold <= 1) {

			$conn = new mysqli($servername, $username, $password, $dbname);
			$sql = "
			SELECT *
			FROM Photo
			INNER JOIN (SELECT photo_id, COUNT(*) AS votes FROM Animal GROUP BY photo_id) AS VoteCount
			ON VoteCount.photo_id = Photo.photo_id
			INNER JOIN (SELECT option_id, option_name FROM Options) AS AnimalNames
			ON AnimalNames.option_id = Photo.species
			WHERE evenness <= $threshold AND votes >= $minVotes
			";
			$result = $conn->query($sql);

			echo "Showing photos with at least ", $minVotes, " votes and evenness at most ", $threshold, " (", $result->num_rows , " results):<br/><br/>";

			echo '<table style="width:75%"> <tr> <td>photo_id</td> <td>path</td> <td>species</td> <td>evenness</td> <td>votes</td> </tr>';

			while ($row = $result->fetch_assoc()) {
				echo "<tr> <td>", $row["photo_id"], "</td> <td>", $row["dirname"], "/", $row["filename"], "</td> <td>", $row["option_name"], "</td> <td>", $row["evenness"], "</td> <td>", $row["votes"], "</td> </tr>" ;
			}

			echo '</table>';

		} else {
			echo "Threshold must be between 0 and 1.";
		}
	} else {
		echo "Minimum votes must be non-negative.";
	}
} else {
	echo "Please enter a value into both fields.";
}

?>

</body>

</html>