How to use SELECT drop-down selected value in the WHERE clause of a mysql SELECT statement

Question

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)
fields:
skill_id (int,pk)
skill (varchar)
Example row - 5011, Roofing

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

table3: tc_skill_assessments (3108 rows)
fields:
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:

PHP

<?php
// 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 
    tc_skill_assessments.id, 
    tc_skill_categories.skill_id, 
    tc_skill_categories.skill, 
    tc_volunteers.vol_id, 
    tc_volunteers.full_name, 
    tc_skill_assessments.ranking 
	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
    
	ORDER BY 
    tc_skill_categories.skill, 
    tc_skill_assessments.ranking DESC';
	
    $q = $dbo->query($sql2);
    $q->setFetchMode(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("Could not connect to the database $dbname :" . $e->getMessage());
}
?>


<!DOCTYPE html>
<html>
<head>
	<title>Skilled Help Finder</title>
	<link href="node_modules/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet">
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
</head>
<body>
<div id="container">
<h1>  Skilled Help Finder</h1><br>

<form name="form1″ method="post" > 
	<?php
	// 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
	?>
</form>

<table class="table table-bordered table-condensed">
	<thead>
	<tr>
		<th>rowID</th>
		<th>skillID</th>
		<th>Skill</th>
		<th>volID</th>
		<th>Volunteer</th>
		<th>Expertise</th>
	</tr>
	</thead>
	<tbody>
	<?php while ($row = $q->fetch()): ?>
		<tr>
		<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>
		</tr>
	<?php endwhile; ?>
	</tbody>
</table>
</div><!-- /container -->
<script>
  $(document).ready(function() {
	  $("select#skillselector").change(function() {
		  let selectedItem = $(this).children("option:selected").val();
		  alert("You have selected skill id " + selectedItem);
		});
	});
</script>
</body>
</html>

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.

Posted 9-Dec-22 2:53am

Sam Tanner

Updated 11-Dec-22 4:45am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CodeGuru84 · Answer 1 · 2022-12-11T04:45:00

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 -->
</select>

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
  executeQuery(query);
});

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.