Nick D Nick D - 16 days ago 5
HTML Question

PHP/MYSQL - Select option value not being sent?

I have the following code which should return all of the teams within a certain region. I have a database of football teams that contains tables for Teams and States. The teams table has a foreign key reference to a states table, and the states table has an attribute for regions (north, south, east, west).

I have the following html/php code on my main page:

<div>
<form method="post" action="regions_filter.php">
<fieldset>
<legend>Filter Teams By Region</legend>
<select name="Region">
<?php
if(!($stmt = $mysqli->prepare("SELECT DISTINCT region FROM states"))){
echo "Prepare failed: " . $stmt->errno . " " . $stmt->error;
}

if(!$stmt->execute()){
echo "Execute failed: " . $mysqli->connect_errno . " " . $mysqli->connect_error;
}
if(!$stmt->bind_result($region)){
echo "Bind failed: " . $mysqli->connect_errno . " " . $mysqli->connect_error;
}
while($stmt->fetch()){
echo '<option value=" ' . $region . ' "> ' . $region . '</option>\n';
}
$stmt->close();
?>
</select>
<input type="submit" value="Run Filter"/>
</fieldset>
</form>
</div>


Below is the regions_filter.php file code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<body>
<div>
<table>
<tr>
<td>Teams By Region</td>
</tr>
<tr>
<td>School Name</td>
<td>State Name</td>
<td>State Capital</td>
<td>State Population</td>
<td>Region</td>
</tr>
<?php
if(!($stmt = $mysqli->prepare("SELECT teams.school_name, states.name, states.capital, states.population, states.region FROM teams
INNER JOIN states ON states.id = teams.state_id
WHERE states.region = ?"))){
echo "Prepare failed: " . $stmt->errno . " " . $stmt->error;
}

if(!($stmt->bind_param("s",$_POST['Region']))){
echo "Bind failed: " . $stmt->errno . " " . $stmt->error;
}

if(!$stmt->execute()){
echo "Execute failed: " . $mysqli->connect_errno . " " . $mysqli- >connect_error;
}
if(!$stmt->bind_result($school, $state, $capital, $population, $region)){
echo "Bind failed: " . $mysqli->connect_errno . " " . $mysqli- >connect_error;
}
while($stmt->fetch()){
echo "<tr>\n<td>" . $school . "\n</td>\n<td>" . $state . "\n</td>\n<td>" . $capital . "\n</td>\n</td>"
. $population . "\n</td>\n<td>" . $region . "\n</td>\n</tr>";
}
$stmt->close();
?>
</table>
</div>

</body>
</html>


When I go to run the filter on my main page, I am taken to the regions_filter.php page with no results. The only thing that shows up is the pre-coded html table at the top of the regions_filter.php page.
I believe the error is somewhere in the code snippet below. I have tried different variations with the option value but can't seem to crack it:

while($stmt->fetch()){
echo '<option value=" ' . $region . ' "> ' . $region . '</option>\n';
}


Any pointers in the right direction would be greatly appreciated.

Answer

There is an error populating the regions select there is an extra white space before and after the region name. It should be like:

while($stmt->fetch()){
   echo '<option value="' . $region . '">' . $region . '</option>\n';
}