Click here to Skip to main content
15,746,107 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to have an HTML SELECT drop-down filtering the contents of an html table by changing the WHERE clause of the table's mySQL SELECT query.

I have 3 tables in a mySql database:
table1: tc_skill_categories (28 rows)
skill_id (int,pk)
skill (varchar)
Example row - 5011, Roofing

table2: tc_volunteers (111 rows)
vol_id (int,pk)
full_name (varchar)
Example row - 1001, Jane Doe

table3: tc_skill_assessments (3108 rows)
id  (int,pk)
skill_id (int)
vol_id (int)
ranking (int)  <-- the level of this volunteer's expertise in this skill, ranked 0-4
Example row - 929, 5011, 1001, 3

On my html page, I have a select drop-down populated with skill categories from table1 via PDO and PHP, using a foreach loop. On page load, it shows the first option which is "Auto Maintenance" (with hidden skill_id 5001). I have proven the select box works using jQuery alert.

Next I want to have an html table on the same page, displaying volunteer names and their ranking for that skill. To do this, I need to include the selected value of my select-box (name and id = "skillselector") as a variable in the WHERE clause of my SELECT statement.

So the question is how do I connect the html select box to control the result table? I think I need to use html FORM but can't seem to decipher how it would be done.

What I have tried:

My code so far:
// prepare to gather data
    $host = 'localhost';
    $dbname = 'pdo';
    $username = 'root';
    $password = '';
try {
$dbo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
} catch (PDOException $e) {
    die("Could not connect to the database $dbname :" . $e->getMessage());

// gather data for result table
try {
    $sql2 = 'SELECT, 
	FROM tc_skill_categories 
	JOIN tc_skill_assessments ON 
    tc_skill_categories.skill_id = tc_skill_assessments.skill_id 
	INNER JOIN tc_volunteers ON 
    tc_skill_assessments.vol_id = tc_volunteers.vol_id 
	WHERE tc_skill_categories.skill_id = 5001
    tc_skill_assessments.ranking DESC';
    $q = $dbo->query($sql2);
} catch (PDOException $e) {
    die("Could not connect to the database $dbname :" . $e->getMessage());

<!DOCTYPE html>
	<title>Skilled Help Finder</title>
	<link href="node_modules/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet">
	<script src=""></script>
<div id="container">
<h1>  Skilled Help Finder</h1><br>

<form name="form1″ method="post" > 
	// gather data for drop_box
	$sql="SELECT skill,skill_id FROM tc_skill_categories";
	// Set up list drop-box html
	echo "<select name='skillselector' id='skillselector' skill=skill value=skill_id>Skill</option>";
	// loop through skill categories table
	foreach ($dbo->query($sql) as $row){
	// write out each option line
	echo "<option value=$row[skill_id]>$row[skill]</option>"; 
	 echo "</select>";// Close list box tag

<table class="table table-bordered table-condensed">
	<?php while ($row = $q->fetch()): ?>
		<td><?php echo htmlspecialchars($row['id']) ?></td>
		<td><?php echo htmlspecialchars($row['skill_id']); ?></td>
		<td><?php echo htmlspecialchars($row['skill']); ?></td>
		<td><?php echo htmlspecialchars($row['vol_id']); ?></td>
		<td><?php echo htmlspecialchars($row['full_name']); ?></td>
		<td><?php echo htmlspecialchars($row['ranking']); ?></td>
	<?php endwhile; ?>
</div><!-- /container -->
  $(document).ready(function() {
	  $("select#skillselector").change(function() {
		  let selectedItem = $(this).children("option:selected").val();
		  alert("You have selected skill id " + selectedItem);

My page almost works... the drop-down and the table are both populated and I get a JS alert with the new skill_id number when changing the drop-down, but I don't know how to finish this.
Updated 11-Dec-22 4:45am

1 solution

To create a drop-down filter for an HTML table based on a SELECT query in MySQL, you can use JavaScript to dynamically update the query's WHERE clause and retrieve the filtered results from the database. Here is an example of how you might do this:

In your HTML file, create a SELECT element that lists the skill categories from the tc_skill_categories table:

<select id="skill-filter">
  <option value="">All Skills</option>
  <option value="Roofing">Roofing</option>
  <!-- Add options for each skill category in tc_skill_categories -->

Use JavaScript to listen for changes to the SELECT element and update the WHERE clause of the SELECT query for the HTML table:

const skillFilter = document.getElementById("skill-filter");

skillFilter.addEventListener("change", function() {
  const selectedSkill = this.value;

  // Update the WHERE clause of the SELECT query to filter by the selected skill
  let query = "SELECT * FROM tc_skill_assessments WHERE 1=1";
  if (selectedSkill) {
    query += ` AND skill_id IN (SELECT skill_id FROM tc_skill_categories WHERE skill = ${selectedSkill})`;

  // Execute the updated query and update the HTML table with the results

Write a function to execute the SELECT query and update the HTML table with the results. This function will likely involve using an API or library to send the query to the MySQL database and retrieve the results, as well as updating the HTML table with the returned data.

Note that this is just one way to implement a drop-down filter for an HTML table using a MySQL SELECT query. There are many other approaches you could take, and the specific details will depend on your specific needs and environment.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900