Solved

Help needed | Mysql listbox to filter results

Posted on 2014-04-30
16
236 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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
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 500 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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

718 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