[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help needed | Mysql listbox to filter results

Posted on 2014-04-30
16
Medium Priority
?
243 Views
Last Modified: 2014-05-06
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

0
Comment
Question by:Geektank
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 36

Expert Comment

by:Kimputer
ID: 40031727
Any problems ? If so, can you show the rendered source file ?
0
 

Author Comment

by:Geektank
ID: 40031792
do you mean 'get_subcontractor.php'
0
 

Author Comment

by:Geektank
ID: 40032156
It needs to work like the following

http://labs.mysql.com/
0
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

 
LVL 36

Expert Comment

by:Kimputer
ID: 40032321
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
 

Author Comment

by:Geektank
ID: 40032361
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
 
LVL 36

Expert Comment

by:Kimputer
ID: 40032439
So you want a dropdown list with what ? 1 through 7 ? 1 through 100 ?
0
 

Author Comment

by:Geektank
ID: 40032447
the dropdown list calls data from another table the number refers to it's foreign key / Primary Key
0
 
LVL 36

Expert Comment

by:Kimputer
ID: 40032455
Then I need that query first. Or if it's POSTed, which string/variable holds it.
0
 

Author Comment

by:Geektank
ID: 40032461
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
 

Author Comment

by:Geektank
ID: 40032517
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
 
LVL 36

Expert Comment

by:Kimputer
ID: 40033439
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
 

Author Comment

by:Geektank
ID: 40044022
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
 
LVL 36

Expert Comment

by:Kimputer
ID: 40044047
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
 

Author Comment

by:Geektank
ID: 40044067
Yeah if you wouldn't mind the submit button would be better for uses.
0
 
LVL 36

Accepted Solution

by:
Kimputer earned 2000 total points
ID: 40044092
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
 

Author Closing Comment

by:Geektank
ID: 40044129
Works perfectly , and can be addapted to suite.  Thank you.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question