Help needed | Mysql listbox to filter results

I'm trying to m ake the listbox vaules filter the (WHERE) option within the sql query.  

<html>
		<h4><i class="fa fa-users"></i> Please select your Sub-contractor</h4>
			<select class="form-control input-lg" name="score_subcontractor_fk">
				<?php include 'get_subcontractor.php'; ?>
			</select>
</html>


<?php
$con=mysqli_connect("10.10.10.1","web212-navicat","blank","blank");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"
SELECT `score_pk` , `score_date` , `score_timelydelivery` , `score_GoodAttitudeDisplayed` , `score_ProgrammePerformance` , `score_FinishedProduct` , `score_TidyWorkingArea` , `score_MaterialsProtected` , `score_RiskAssessments` , `score_GoodHousekeeping` , `score_SigningIn` , `score_PPE` , `score_FuelStorageUse` , `score_WasteManagement` , `score_PollutionPrevention` , `score_contract_fk` , `score_subcontractor_fk` , `score_presenceofsupervisor` , `score_comments` , b.contract_pk, b.contract_name, c.subcontractor_name
FROM `scores` 
INNER JOIN contracts b ON score_contract_fk = contract_pk
INNER JOIN subcontractor c ON score_subcontractor_fk = subcontractor_pk
WHERE score_subcontractor_fk=7");

echo "<div class='table-responsive visible-lg'><table class='table table-striped'>
<tr>
<th>Date</th>
<th>Contract<br/>Name</th>
<th>Subcontractors<br/>Name</th>
<th>timely<br/>delivery</th>
<th>Good<br/>Attitude<br/>Displayed</th>
<th>Programme<br/>Performance</th>
<th>Finished<br/>Product</th>
<th>Tidy<br/>Working<br/>Area</th>
<th>Materials<br/>Protected</th>
<th>Risk<br/>Assessments</th>
<th>Good<br/>Housekeeping</th>
<th>Signing<br/>In</th>
<th>PPE</th>
<th>Fuel<br/>Storage<br/>Use</th>
<th>Waste<br/>Management</th>
<th>Pollution<br/>Prevention</th>
<th>presence<br/>of<br/>supervisor</th>
<th>comments</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
echo "<td>" . $row['score_date'] . "</td>";
echo "<td>" . $row['contract_name'] . "</td>";
echo "<td>" . $row['subcontractor_name'] . "</td>";
echo "<td>" . $row['score_timelydelivery'] . "</td>";
echo "<td>" . $row['score_GoodAttitudeDisplayed'] . "</td>";
echo "<td>" . $row['score_ProgrammePerformance'] . "</td>";
echo "<td>" . $row['score_FinishedProduct'] . "</td>";
echo "<td>" . $row['score_TidyWorkingArea'] . "</td>";
echo "<td>" . $row['score_MaterialsProtected'] . "</td>";
echo "<td>" . $row['score_RiskAssessments'] . "</td>";
echo "<td>" . $row['score_GoodHousekeeping'] . "</td>";
echo "<td>" . $row['score_SigningIn'] . "</td>";
echo "<td>" . $row['score_PPE'] . "</td>";
echo "<td>" . $row['score_FuelStorageUse'] . "</td>";
echo "<td>" . $row['score_WasteManagement'] . "</td>";
echo "<td>" . $row['score_PollutionPrevention'] . "</td>";
echo "<td>" . $row['score_presenceofsupervisor'] . "</td>";
echo "<td>" . $row['score_comments'] . "</td>";
  echo "</tr>";
  }
echo "</table></div>";

mysqli_close($con);
?> 

Open in new window

GeektankAsked:
Who is Participating?
 
KimputerConnect With a Mentor Commented:
New code (click button to submit):

<html>

<script type="text/javascript" src="http://code.jquery.com/jquery-1.11.0.min.js"></script>
	<h4><i class="fa fa-users"></i> Please select your Sub-contractor</h4>
	<select class="form-control input-lg" name="score_subcontractor_fk" id="score_subcontractor_fk">
		<?php include 'get_subcontractor.php'; ?>
	</select>
	
	<button id="btn1">submit</button>
	
	<script type="text/javascript">
		$('#btn1').click(function(){
				var value = $( "#score_subcontractor_fk" ).val();
				var form = $('<form action="index.php" method="post">' +
		'<input type="hidden" name="id" value="' +  value + '" />' +
		'</form>');
		$('body').append(form);
		$(form).submit();
			  });

</script>


</html>


<?php


if( $_SERVER['REQUEST_METHOD'] == 'POST') {

	if(isset($_POST['id'])){
			$id = $_POST['id'];
			$con=mysqli_connect("10.10.10.1","web212-navicat","blank","blank");
			// Check connection
			if (mysqli_connect_errno())
			  {
			  echo "Failed to connect to MySQL: " . mysqli_connect_error();
			  }

			$result = mysqli_query($con,"
			SELECT `score_pk` , `score_date` , `score_timelydelivery` , `score_GoodAttitudeDisplayed` , `score_ProgrammePerformance` , `score_FinishedProduct` , `score_TidyWorkingArea` , `score_MaterialsProtected` , `score_RiskAssessments` , `score_GoodHousekeeping` , `score_SigningIn` , `score_PPE` , `score_FuelStorageUse` , `score_WasteManagement` , `score_PollutionPrevention` , `score_contract_fk` , `score_subcontractor_fk` , `score_presenceofsupervisor` , `score_comments` , b.contract_pk, b.contract_name, c.subcontractor_name
			FROM `scores` 
			INNER JOIN contracts b ON score_contract_fk = contract_pk
			INNER JOIN subcontractor c ON score_subcontractor_fk = subcontractor_pk
			WHERE score_subcontractor_fk=$id");

			echo "<div class='table-responsive visible-lg'><table class='table table-striped'>
			<tr>
			<th>Date</th>
			<th>Contract<br/>Name</th>
			<th>Subcontractors<br/>Name</th>
			<th>timely<br/>delivery</th>
			<th>Good<br/>Attitude<br/>Displayed</th>
			<th>Programme<br/>Performance</th>
			<th>Finished<br/>Product</th>
			<th>Tidy<br/>Working<br/>Area</th>
			<th>Materials<br/>Protected</th>
			<th>Risk<br/>Assessments</th>
			<th>Good<br/>Housekeeping</th>
			<th>Signing<br/>In</th>
			<th>PPE</th>
			<th>Fuel<br/>Storage<br/>Use</th>
			<th>Waste<br/>Management</th>
			<th>Pollution<br/>Prevention</th>
			<th>presence<br/>of<br/>supervisor</th>
			<th>comments</th>
			</tr>";

			while($row = mysqli_fetch_array($result))
			  {
			  echo "<tr>";
			echo "<td>" . $row['score_date'] . "</td>";
			echo "<td>" . $row['contract_name'] . "</td>";
			echo "<td>" . $row['subcontractor_name'] . "</td>";
			echo "<td>" . $row['score_timelydelivery'] . "</td>";
			echo "<td>" . $row['score_GoodAttitudeDisplayed'] . "</td>";
			echo "<td>" . $row['score_ProgrammePerformance'] . "</td>";
			echo "<td>" . $row['score_FinishedProduct'] . "</td>";
			echo "<td>" . $row['score_TidyWorkingArea'] . "</td>";
			echo "<td>" . $row['score_MaterialsProtected'] . "</td>";
			echo "<td>" . $row['score_RiskAssessments'] . "</td>";
			echo "<td>" . $row['score_GoodHousekeeping'] . "</td>";
			echo "<td>" . $row['score_SigningIn'] . "</td>";
			echo "<td>" . $row['score_PPE'] . "</td>";
			echo "<td>" . $row['score_FuelStorageUse'] . "</td>";
			echo "<td>" . $row['score_WasteManagement'] . "</td>";
			echo "<td>" . $row['score_PollutionPrevention'] . "</td>";
			echo "<td>" . $row['score_presenceofsupervisor'] . "</td>";
			echo "<td>" . $row['score_comments'] . "</td>";
			  echo "</tr>";
			  }
			echo "</table></div>";

			mysqli_close($con);
	}
}


?> 

Open in new window

0
 
KimputerCommented:
Any problems ? If so, can you show the rendered source file ?
0
 
GeektankAuthor Commented:
do you mean 'get_subcontractor.php'
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
GeektankAuthor Commented:
It needs to work like the following

http://labs.mysql.com/
0
 
KimputerCommented:
Currently, I cannot see how it works, if it works, and if it doesn't, what needs to be fixed. You should load the page, then save as html and attach here.
0
 
GeektankAuthor Commented:
Hi thanks for getting back to me, well the basic listbox pulls the vaules from the database.

and the code below shows a query run on the database with the WHEN clause, however I don't no how to make the listbox 'become the query vaule'

WHERE score_subcontractor_fk=7");

Open in new window

0
 
KimputerCommented:
So you want a dropdown list with what ? 1 through 7 ? 1 through 100 ?
0
 
GeektankAuthor Commented:
the dropdown list calls data from another table the number refers to it's foreign key / Primary Key
0
 
KimputerCommented:
Then I need that query first. Or if it's POSTed, which string/variable holds it.
0
 
GeektankAuthor Commented:
Sorry the answer to that was yes but i already have this working... It's the ability to link it in with the query's clause.
0
 
GeektankAuthor Commented:
ok the get_subcontractor.php is below.

<?php
$con=mysqli_connect("10.10.10.1","web212-navicat","blank","blank");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM subcontractor");



while($row = mysqli_fetch_array($result))
  {
  echo '<option value="';
  echo $row['subcontractor_pk'];
  echo '">';
  echo $row['subcontractor_name'];
  echo '</option>';

  }
  
mysqli_close($con);

?>

Open in new window

0
 
KimputerCommented:
Then it should be something like this:

<html>

<script type="text/javascript" src="http://code.jquery.com/jquery-1.11.0.min.js"></script>
	<h4><i class="fa fa-users"></i> Please select your Sub-contractor</h4>
	<select class="form-control input-lg" name="score_subcontractor_fk" id="score_subcontractor_fk">
		<?php include 'get_subcontractor.php'; ?>
	</select>
	
	<script type="text/javascript">
		$('#score_subcontractor_fk').click(function(){
				var value = $( "#score_subcontractor_fk" ).val();
				var form = $('<form action="index.php" method="post">' +
		'<input type="hidden" name="id" value="' +  value + '" />' +
		'</form>');
		$('body').append(form);
		$(form).submit();
			  });

</script>


</html>


<?php


if( $_SERVER['REQUEST_METHOD'] == 'POST') {

	if(isset($_POST['id'])){
			$id = $_POST['id'];
			$con=mysqli_connect("10.10.10.1","web212-navicat","blank","blank");
			// Check connection
			if (mysqli_connect_errno())
			  {
			  echo "Failed to connect to MySQL: " . mysqli_connect_error();
			  }

			$result = mysqli_query($con,"
			SELECT `score_pk` , `score_date` , `score_timelydelivery` , `score_GoodAttitudeDisplayed` , `score_ProgrammePerformance` , `score_FinishedProduct` , `score_TidyWorkingArea` , `score_MaterialsProtected` , `score_RiskAssessments` , `score_GoodHousekeeping` , `score_SigningIn` , `score_PPE` , `score_FuelStorageUse` , `score_WasteManagement` , `score_PollutionPrevention` , `score_contract_fk` , `score_subcontractor_fk` , `score_presenceofsupervisor` , `score_comments` , b.contract_pk, b.contract_name, c.subcontractor_name
			FROM `scores` 
			INNER JOIN contracts b ON score_contract_fk = contract_pk
			INNER JOIN subcontractor c ON score_subcontractor_fk = subcontractor_pk
			WHERE score_subcontractor_fk=$id");

			echo "<div class='table-responsive visible-lg'><table class='table table-striped'>
			<tr>
			<th>Date</th>
			<th>Contract<br/>Name</th>
			<th>Subcontractors<br/>Name</th>
			<th>timely<br/>delivery</th>
			<th>Good<br/>Attitude<br/>Displayed</th>
			<th>Programme<br/>Performance</th>
			<th>Finished<br/>Product</th>
			<th>Tidy<br/>Working<br/>Area</th>
			<th>Materials<br/>Protected</th>
			<th>Risk<br/>Assessments</th>
			<th>Good<br/>Housekeeping</th>
			<th>Signing<br/>In</th>
			<th>PPE</th>
			<th>Fuel<br/>Storage<br/>Use</th>
			<th>Waste<br/>Management</th>
			<th>Pollution<br/>Prevention</th>
			<th>presence<br/>of<br/>supervisor</th>
			<th>comments</th>
			</tr>";

			while($row = mysqli_fetch_array($result))
			  {
			  echo "<tr>";
			echo "<td>" . $row['score_date'] . "</td>";
			echo "<td>" . $row['contract_name'] . "</td>";
			echo "<td>" . $row['subcontractor_name'] . "</td>";
			echo "<td>" . $row['score_timelydelivery'] . "</td>";
			echo "<td>" . $row['score_GoodAttitudeDisplayed'] . "</td>";
			echo "<td>" . $row['score_ProgrammePerformance'] . "</td>";
			echo "<td>" . $row['score_FinishedProduct'] . "</td>";
			echo "<td>" . $row['score_TidyWorkingArea'] . "</td>";
			echo "<td>" . $row['score_MaterialsProtected'] . "</td>";
			echo "<td>" . $row['score_RiskAssessments'] . "</td>";
			echo "<td>" . $row['score_GoodHousekeeping'] . "</td>";
			echo "<td>" . $row['score_SigningIn'] . "</td>";
			echo "<td>" . $row['score_PPE'] . "</td>";
			echo "<td>" . $row['score_FuelStorageUse'] . "</td>";
			echo "<td>" . $row['score_WasteManagement'] . "</td>";
			echo "<td>" . $row['score_PollutionPrevention'] . "</td>";
			echo "<td>" . $row['score_presenceofsupervisor'] . "</td>";
			echo "<td>" . $row['score_comments'] . "</td>";
			  echo "</tr>";
			  }
			echo "</table></div>";

			mysqli_close($con);
	}
}


?> 

Open in new window


Please note the mentioning of index.php. That's because I assume that's what this code is for. If not, put in the filename where you will use this code.
0
 
GeektankAuthor Commented:
Yeah this is more like what i'm after, howerever the 'on click' function only pulls the first record within the list... Should this have a submit button and if so how should i begin to code it in.

Thanks in advance your on the right lines. :)
0
 
KimputerCommented:
Can you try this, click on the arrow next to the dropdown list. Without letting go of the mousebutton, continue to select an item more down the list, only now will you release the button. This is how you select another item, other than the first record.

If you still want the submit button, it's also okay of course, I will code it after you reply.
0
 
GeektankAuthor Commented:
Yeah if you wouldn't mind the submit button would be better for uses.
0
 
GeektankAuthor Commented:
Works perfectly , and can be addapted to suite.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.