Filter MySQL Data based on ComboBox Selection

[clc_services]

Hi,

I'm creating a web page which links to a mysql database. I have managed to get all of the data to appear in a table and i have also been able to get the combobox to be populated based on a table in the database. The part which I am having trouble with is joining them together so when you select an option in the combobox, the data is filtered and displayed in the table.

My code is below

<?php header('Refresh: 30'); ?>

<?php

$con = mysql_connect("localhost","test","test");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("equiscor_equiscoreLive", $con);

// Write out our query.
$query = "SELECT * FROM tests";
// Execute it, or return the error message if there's a problem.
$result = mysql_query($query) or die(mysql_error());
$dropdown = "<select name='Test'>";
while($row = mysql_fetch_assoc($result)) {
$dropdown .= "\r\n<option value='{$row['Test']}'>{$row['Test']}</option>";
}
$dropdown .= "\r\n</select>";
echo $dropdown;

$result = mysql_query("SELECT * FROM scores");

echo "<table border='1'>
<tr>
<th>Horse Number</th>
<th>Horse Name</th>
<th>Rider Name</th>
<th>E %</th>
<th>H %</th>
<th>C %</th>
<th>M %</th>
<th>B %</th>
<th>Total %</th>
<th>Errors</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Horse_Number'] . "</td>";
  echo "<td>" . $row['Horse_Name'] . "</td>";
  echo "<td>" . $row['Rider_Name'] . "</td>";
  echo "<td>" . $row['E_Total_Percent'] . "%</td>";
  echo "<td>" . $row['H_Total_Percent'] . "%</td>";
  echo "<td>" . $row['C_Total_Percent'] . "%</td>";
  echo "<td>" . $row['M_Total_Percent'] . "%</td>";
  echo "<td>" . $row['B_Total_Percent'] . "%</td>";
  echo "<td>" . $row['Total_Average'] . "%</td>";
  echo "<td>" . $row['Errors'] . "</td>";

  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

Thanks in advance
Chris

[broj1]

Hope you are still interested in help for this :-).

First wrap your select element in form tags and set method (GET) and action (# -> self). Then add a submit button so the form can be submitted (or make the select element autosubmit).

// add <form> tags and a submit button
$submit = '<input type="submit" name="submit" value="Submit" />';

echo '<form method="get" action="#">' . $dropdown . $submit . '</form>';

Next checkfor the existence of a $_GET value in a $_GET array. If it exists the form was submitted and you can use the selected value of the select element to filter the results (use it with WHERE clause in your query).

// if the form was submited
if(isset($_GET['Test'])) {

    // sanitize test somehow
    $test = $_GET['Test'];
    
    // add the filter condition to your query
    $test_query .= " WHERE test=$test";
}

Your code above modified to filter results:

<?php header('Refresh: 30'); ?>

<?php

// query to show all results
$test_query = "SELECT * FROM scores";

// if the form was submited
if(isset($_GET['Test'])) {

    // sanitize test somehow
    $test = $_GET['Test'];
    
    // add the filter condition to your query
    $test_query .= " WHERE test=$test";
}

$con = mysql_connect("localhost","test","test");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("equiscor_equiscoreLive", $con);

// Write out our query.
$query = "SELECT * FROM tests";
// Execute it, or return the error message if there's a problem.
$result = mysql_query($query) or die(mysql_error());
$dropdown = "<select name='Test'>";
while($row = mysql_fetch_assoc($result)) {
$dropdown .= "\r\n<option value='{$row['Test']}'>{$row['Test']}</option>";
}
$dropdown .= "\r\n</select>";

$submit = '<input type="submit" name="submit" value="Submit" />';

echo '<form method="get" action="#">' . $dropdown . $submit . '</form>';

$result = mysql_query($test_query);

echo "<table border='1'>
<tr>
<th>Horse Number</th>
<th>Horse Name</th>
<th>Rider Name</th>
<th>E %</th>
<th>H %</th>
<th>C %</th>
<th>M %</th>
<th>B %</th>
<th>Total %</th>
<th>Errors</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Horse_Number'] . "</td>";
  echo "<td>" . $row['Horse_Name'] . "</td>";
  echo "<td>" . $row['Rider_Name'] . "</td>";
  echo "<td>" . $row['E_Total_Percent'] . "%</td>";
  echo "<td>" . $row['H_Total_Percent'] . "%</td>";
  echo "<td>" . $row['C_Total_Percent'] . "%</td>";
  echo "<td>" . $row['M_Total_Percent'] . "%</td>";
  echo "<td>" . $row['B_Total_Percent'] . "%</td>";
  echo "<td>" . $row['Total_Average'] . "%</td>";
  echo "<td>" . $row['Errors'] . "</td>";

  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

Note if you intend to modify data in the database use POST not GET. Hope this is what you were after.

The code was not tested against a database.

[vaultdweller123]

1st wrap your dropdown <select> with a form and add a submit button

$dropdown = "<form action='get'><select name='Test'>";
while($row = mysql_fetch_assoc($result)) {
$dropdown .= "\r\n<option value='{$row['Test']}'>{$row['Test']}</option>";
}
$dropdown .= "\r\n</select><input type='submit' name='submit' value='submit' /></form>";

then update your second query

$query2 = "SELECT * FROM tests WHERE id='".$_GET['Test']."'";
$result2 = mysql_query($query2);
while($row2 = mysql_fetch_array($result2))
{
echo "<tr>";
echo "<td>" . $row2['Horse_Number'] . "</td>";
echo "<td>" . $row2['Horse_Name'] . "</td>";
echo "<td>" . $row2['Rider_Name'] . "</td>";
echo "<td>" . $row2['E_Total_Percent'] . "%</td>";
echo "<td>" . $row2['H_Total_Percent'] . "%</td>";
echo "<td>" . $row2['C_Total_Percent'] . "%</td>";
echo "<td>" . $row2['M_Total_Percent'] . "%</td>";
echo "<td>" . $row2['B_Total_Percent'] . "%</td>";
echo "<td>" . $row2['Total_Average'] . "%</td>";
echo "<td>" . $row2['Errors'] . "</td>";
 
echo "</tr>";
}

[clc_services]

Thank you so much everyone. My website (www.equiscore.com.au/live) works perfectly now