Solved

Help needed | Mysql listbox to filter results

Posted on 2014-04-30
16
222 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
  • 9
  • 7
16 Comments
 
LVL 35

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
 
LVL 35

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 35

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 35

Expert Comment

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

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 35

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 35

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 35

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Foolproof security solutions has become one of the key necessities of every e-commerce or Internet banking website. If you too own an online shopping site then its vital for you to equip your web portal with customer security features that can allow…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now